PERL 生成数据

use  GetExcelData;
use ReadWriteFile;
use Cwd;#获取本地路径模块
#获得二维数组的一维全部元素,第三个变量是得到元素的数组引用。
sub GetArrayValue
{
my($InputPoint,$iGetindex,$GetPoint) = @_;
my $count = 0;
#print "the array is @$InputPointn";
foreach my $index (@$InputPoint)
{
# print "nEach Array value is @$indexn";
if($iGetindex == $count)
{
push @$GetPoint,@$index;
return 1;
}
$count++;
}
0;
}
print "n正在导入工参数据n.......";
#new一个对象的引用
my $ExcelPoint = GetExcelData->new();
#获取本地路径下所有EXCEL数据
$ExcelPoint->GetAllExcelDate();
#获得导入的所有的Excel的名字
my @ExcelName;
@ExcelName = $ExcelPoint->GetExcelName();
#print "nExcel Name is @ExcelNamen";
print "n数据导入完毕，按任意键继续......n";
my $input = <STDIN>;
my $ExcelName = "ex.xlsx";
my $SheetName = "Project Parameters";
my @ProjectContent;
my $iRowNum = $ExcelPoint->GetExcelSheetRowNum($ExcelName,$SheetName);
foreach my $Iindex(3..$iRowNum)
{
my @SheetRowDate = $ExcelPoint->GetExcelSheetRowDate($ExcelName,$SheetName,$Iindex);
my $SubNetID = $SheetRowDate[1];
my $MEID = $SheetRowDate[2];
my $eNodeB = $SheetRowDate[4];
my $MCC = $SheetRowDate[9];
my $MNC = $SheetRowDate[10];
my $Lon = $SheetRowDate[6];
my $Lat = $SheetRowDate[7];
my $CellID = $SheetRowDate[12];
#print "$SubNetID is $SubNetIDn";
#print "$MEID is $MEIDn";
#print "$eNodeB is $eNodeBn";
#print "$MCC is $MCCn";
#print "$MNC is $MNCn";
#print "$Lon is $Lonn";
#print "$Lat is $Latn";
my @ProjectInfo;
push @ProjectInfo,$SubNetID;
push @ProjectInfo,$MEID;
push @ProjectInfo,$eNodeB;
push @ProjectInfo,$MCC;
push @ProjectInfo,$MNC;
push @ProjectInfo,$Lon;
push @ProjectInfo,$Lat;
push @ProjectInfo,$CellID;
my $Connet = join "&",@ProjectInfo;
#存入工参所有相关小区信息
push @ProjectContent,$Connet;
}
my $iProjectSize = @ProjectContent;
print "n工参的大小 $iProjectSizen";
#保存生成的SQL语句
my @CellInsertValue;
#获取本地路径
my $dir = getcwd;
#保存成功执行的SQL
$FilePath = $dir."生成语句生成执行.sql";
#获取对象引用
my $FilePoint = ReadWriteFile->new();
my $SqlNumSize = 0;
print "n语句生成中，请耐心等待......n";
foreach my $Value (@ProjectContent)
{
my $MaxSqlNum = 15;
#每个小区最多生成的Sql语句数
my $SQlNum = &GetRoundNum($MaxSqlNum);
while($SQlNum == 0)
{
$SQlNum = &GetRoundNum($MaxSqlNum);
}
@CellInsertValue = &GetCellInserSql($SQlNum,$Value,@ProjectContent);
my $Arraysize = @CellInsertValue;
$SqlNumSize  = $SqlNumSize  + $Arraysize;
#print "数组大小为 $Arraysize";
#my $inputin = <STDIN>;
$FilePoint->WriteDateToFileAdd($FilePath,@CellInsertValue);
}
print "n共生成$SqlNumSize条测试语句n";
print "n完成任务，按任意键退出....n";
my $InputTest = <STDIN>;
sub GetRoundNum
{
my $MaxValue = shift;
my $GetValue = int(rand($MaxValue));
return $GetValue;
}
sub GetCellInserSql
{
my $SQlNum = shift;
my $Value = shift;
my $ProjectPoint = shift;
my @ProjectContent = @$ProjectPoint;
my @GenerateSql;
foreach my $Index (1..$SQlNum)
{
# print "*" x 20,".n";
# print "n第$Index条数据生成完毕n";
# print "*" x 20,".n";
my @Sql = &GenerateEachSql($Value,@ProjectContent);
#print "n最终生成的SQL值@Sqln";
# PrintOutSql(@Sql);
my $InsertSql = "insert into t054(D004,D005,D020,C001,C009,C010,C011,C012,C013,C015,C016,C017,C018,C019,C020)values";
my $value ="($Sql[0],$Sql[1],'$Sql[2]',$Sql[3],$Sql[4],$Sql[5],$Sql[6],'$Sql[7]','$Sql[8]','$Sql[9]','$Sql[10]',$Sql[23],$Sql[24],$Sql[11],$Sql[19])";
# print "nvalue is $valuen";
my $resultSql = $InsertSql.$value."n";
# print "n生成的SQL是$resultSqln";
#my $test = <STDIN>;
push @GenerateSql,$resultSql;
}
return @GenerateSql;
}
sub PrintOutSql
{
print "n生成的数据为n";
@Sql = @_;
print "nD004 is $Sql[0]n";
print "nD005 is $Sql[1]n";
print "nD020 is $Sql[2]n";
print "nC001 is $Sql[3]n";
print "nC009 is $Sql[4]n";
print "nC010 is $Sql[5]n";
print "nC011 is $Sql[6]n";
print "nC012 is $Sql[7]n";
print "nC013 is $Sql[8]n";
print "nC015 is $Sql[9]n";
print "nC016 is $Sql[10]n";
print "nC019 is $Sql[11]n";
print "nC020 is $Sql[12]n";
print "nC021 is $Sql[13]n";
print "nC022 is $Sql[14]n";
print "nC023 is $Sql[15]n";
print "nC024 is $Sql[16]n";
print "nC025 is $Sql[17]n";
print "nC026 is $Sql[18]n";
print "nC027 is $Sql[19]n";
print "nC029 is $Sql[20]n";
print "nC031 is $Sql[21]n";
print "nC032 is $Sql[22]n";
}
sub GenerateEachSql
{
my $Value = shift;
my $ProjectPoint = shift;
my @ProjectContent = @$ProjectPoint;
my @SheetRowDate = split "&",$Value;
my $SubNetID = $SheetRowDate[0];
my $MEID = $SheetRowDate[1];
my $eNodeB = $SheetRowDate[2];
my $MCC = $SheetRowDate[3];
my $MNC = $SheetRowDate[4];
my $Lon = $SheetRowDate[5];
my $Lat = $SheetRowDate[6];
my $CellID = $SheetRowDate[7];
my $MaxRand = 10000;
my $D004 = $eNodeB;
my $D005 = $CellID;
my $D020 = &GetRoundNum($MaxRand);
my $C001 = &GetRoundNum($MaxRand);
#RSRP与RSRQ
my $MaxRsValue = 43;
my $MaxRqValue = 17;
my $C009 = &GetRoundNum($MaxRsValue);
my $C010 = &GetRoundNum($MaxRqValue);
$C009 = $C009 -140;
$C010 = $C010 -20;
#邻区数据邻区数目
my $NeighSize = @ProjectContent;
my $NeighMaxSize = 10;
my $C011 = &GetRoundNum($NeighMaxSize);
while($C011 == 0)
{
$C011 = &GetRoundNum($NeighMaxSize);
#test
#print"n邻区为0的循环执行了  $C011n";
#my $test = <STDIN>;
}
#控制最大邻区数目
if($C011 > 30)
{
$C011 = $C011 - 4;
}
my @EnodArray;
my @CellArray;
my @RsrpArray;
my @RsrqArray;
#获取邻区数据
&GetNeighInfo($C011,@EnodArray,@CellArray,@RsrpArray,@RsrqArray,@ProjectContent);
#enodb序列
my $C012 = join ",",@EnodArray;
#cell序列
my $C013 = join ",@CellArray;
#邻区RSRP数据
my $C015 = join ",@RsrpArray;
#邻区RSRQ数据
my $C016 = join ",@RsrqArray;
#表示是MDT数据
my $C019 = 3;
my $C020 = 0;
#是服务小区最近一次RSRP和RSRQ值
my $C021 =  &GetRoundNum($MaxRsValue);
my $C022 =  &GetRoundNum($MaxRqValue);
$C021 = $C021 -140;
$C022 = $C022 -20;
my $C023 = $eNodeB;
my $C024 = $CellID;
#重新建立小区的信息
my @RandCell = &GetRondCell(@ProjectContent);
#重建立小区enodbID
my $C025 = $RandCell[2];
#重建立小区CellID
my $C026 = $RandCell[7];
#PHR默认值（这两个门限值，后续要根据算法重新设定）
my $PHMax = 15;
my $C027 = &GetRoundNum($PHMax);
my $C029 = &GetRoundNum($PHMax);
#RSRP经纬度
my $MasValue = 100;
my $tempvalue = &GetRoundNumSuborAdd($MasValue);
my $C017 = $Lon + 0.001 * $tempvalue;
$tempvalue = &GetRoundNumSuborAdd($MasValue);
my $C018 = $Lat + 0.001 * $tempvalue;
#小区经纬度
$tempvalue = &GetRoundNumSuborAdd($MasValue);
my $C031 = $Lat + 0.001 * $tempvalue;
$tempvalue = &GetRoundNumSuborAdd($MasValue);
my $C032 = $Lon + 0.001 * $tempvalue;
my @Sql = ($D004,$D005,$D020,$C001,$C009,$C010,$C011,$C012,$C013,$C015,$C016,$C019,$C020,$C021,$C022,$C023,$C024,$C025,$C026,$C027,$C029,$C031,$C032,$C017,$C018);
return @Sql;
}
sub GetRoundNumSuborAdd
{
my $MaxValue = shift;
my $GetValue = int(rand($MaxValue));
my $Point = int(rand(2));
if($Point == 0)
{
$GetValue = -$GetValue;
}
return $GetValue;
}
#获得邻区数据
sub GetNeighInfo
{
#数组硬指针
my $NeighNum = shift;
my $Value = shift;
my $EnodPoint = shift;
my $CellPoint = shift;
my $RsrpPoint = shift;
my $RsrqPoint = shift;
my $ProjectPoint = shift;
my $CellValue = GetRondCellValue(@$ProjectPoint);
my @NeighArray;
push @NeighArray,$Value ;
my $count = 1;
if($NeighNum != 1)
{
$NeighNum = $NeighNum - 1;
}
while($count <= $NeighNum)
{
#包含邻区数据重新生成
my $TestCount = 1;
while(@NeighArray ~~ $CellValue)
{
$CellValue = GetRondCellValue(@$ProjectPoint);
if($TestCount > 25)
{
#print "n死循环内执行超过25次了n";
#print "n邻区数目是$NeighNumn";
#print "n生成的邻区数据是$countn";
#print "n数据是@NeighArrayn";
#print "n生成的值是$CellValuen";
#my $test = <STDIN>;
}
$TestCount++;
}
push @NeighArray,$CellValue;
$count++;
}
shift @NeighArray;
foreach my $Value (@NeighArray)
{
my @SplitResult = split "&",$Value;
my $eNodeB = $SplitResult[2];
my $CellID = $SplitResult[7];
push @$EnodPoint,$eNodeB;
push @$CellPoint,$CellID;
#RSRP与RSRQ
my $MaxRsValue = 43;
my $Rsrp = &GetRoundNum($MaxRsValue);
$Rsrp  = $Rsrp  - 140;
push @$RsrpPoint,$Rsrp;
my $MaxRqValue = 17;
my $Rsrq = &GetRoundNum($MaxRqValue);
$Rsrq  = $Rsrq  - 20;
push @$RsrqPoint,$Rsrq;
}
}
#获取一个随机的小区
sub GetRondCell
{
my @ProjectContent = @_;
my $CellSize = @ProjectContent;
my $Index = &GetRoundNum($CellSize);
my $CellValue = @ProjectContent[$Index];
my @CellInfo = split "&",$CellValue;
return @CellInfo;
}
#获取一个随机的小区
sub GetRondCellValue
{
my @ProjectContent = @_;
my $CellSize = @ProjectContent;
my $Index = &GetRoundNum($CellSize);
my $CellValue = @ProjectContent[$Index];
return $CellValue;
}
sub GetFloatRandValue
{
my $max = shift;
#生成整数部分
my $IntValue = &GetRoundNum($max);
my $MaxFloat = 100;
#生成小数部分
my $FloatValue = &GetRoundNum($MaxFloat);
#生成假浮点数
my $FloatValue = $IntValue + 0.01 * $FloatValue;
return $FloatValue;
}
#print "Row Date is @SheetRowDaten";