private void btnShuiX_Click(object sender, EventArgs e)
{
string FileDir = txtInDir.Text; //待处理车辆文件所在目录
string[] arrFiles = Directory.GetFiles(FileDir, "*.txt"); //目录内的所有车辆文件
//将文本文件处理成csv文件
//循环处理目录里面的每个文件
//foreach( string doFileName in arrFiles )
//{
// //打开excel文件,放到数据表中
// //DataTable dt = ExcelHelper.ReadExelToTable( doFileName, 0 );
// FileStream fs = new FileStream( doFileName, System.IO.FileMode.Open, System.IO.FileAccess.Read );
// string FileName = Utility.GetFileName( doFileName );
// StreamReader sr = new StreamReader( fs, Encoding.Default );
// string strLine = "";
// StringBuilder sb = new StringBuilder();
// int Kong = 1;
// //循环处理每条记录
// while( ( strLine = sr.ReadLine() ) != null )
// {
// int iLen = strLine.Length;
// if( iLen > 0 )
// {
// for( int i = 0; i < iLen; i++ )
// {
// string s = strLine.Substring( i, 1 );
// if( s == " " )
// {
// if( Kong == 1 )
// sb.Append( "," );
// Kong++;
// }
// else
// {
// sb.Append( s );
// Kong = 1;
// }
// }
// sb.Append( Environment.NewLine );
// }
// }
// StreamWriter sw = new StreamWriter( @"f:\临时文件\输入目录\" + FileName + ".csv", true, System.Text.Encoding.Default );
// sw.Write( sb.ToString() );
// sw.Close();
//}
//MessageBox.Show( "文本文件处理完毕" );
//处理csv文件
arrFiles = Directory.GetFiles(FileDir, "*.csv"); //目录内的所有车辆文件
int totalstand = 1;
int Slno = 1; //站序
int Slno_X = 1;
int Slno_S = 1;
string IsMain = "0";
string SName = string.Empty; //站名
string Slon = string.Empty; //经度
string Slat = string.Empty; //纬度
string SQL = string.Empty;
string LineName = string.Empty;
StringBuilder sbStop = new StringBuilder();
foreach (string doFileName in arrFiles)
{
DataTable dt = ExcelHelper.ReadCsvToTable(doFileName);
//LineName = Utility.GetFileName( doFileName ) + "路";
Slno = 1;
Slno_S = 1;
Slno_X = 1;
//处理每条记录
foreach (DataRow dr in dt.Rows)
{
string slguid = Utility.CreateGuidLeft(36, totalstand.ToString() + "STANDDATA", '0', true);
Slno = Convert.ToInt16(dr[0].ToString());
Slon = dr[2].ToString(); //经度
Slat = dr[3].ToString(); //纬度
SName = dr[1].ToString(); //站名
LineName = dr[4].ToString(); //线路名
IsMain = dr[5].ToString(); //上下行
//if( tempName[ 0 ] == "上行" )
//{
// IsMain = "0";
// Slno = Slno_S;
// Slno_S ++;
//}
//else
//{
// IsMain = "1";
// Slno = Slno_X;
// Slno_X++;
//}
SQL = "INSERT INTO linestand(slno,sname,slon,slat,lname,slguid,ismain) VALUES({0},'{1}',{2},{3},'{4}','{5}','{6}')" + Environment.NewLine;
SQL = string.Format(SQL, Slno, SName, Convert.ToDecimal(Slon), Convert.ToDecimal(Slat), LineName, slguid, IsMain);
Slno++;
sbStop.Append(SQL);
totalstand++;
}
}
StreamWriter swStop = new StreamWriter(@"f:\临时文件\linstand.sql", true, System.Text.Encoding.Default);
swStop.Write(sbStop.ToString());
swStop.Close();
MessageBox.Show("CSV文件处理完毕");
//生成新的线路信息
SQL = "select lname from linestand group by lname";
DbHelper dbHelper = new DbHelper();
DataTable dtLine = dbHelper.Fill(SQL);
InportData.InportLineInfo(dtLine, @"f:\临时文件\lineinfo.sql");
}
private void btnInGd_Click(object sender, EventArgs e)
{
string Lname = textBox5.Text;
Int32 totalstand = 8000;
//同步line_standinfo表中的sguid
string UpdateSguid = "UPDATE linestand SET sguid=(SELECT sguid FROM dbo.line_standinfo WHERE SNAME=linestand.sname AND SLON=linestand.slon AND SLAT=linestand.slat AND slon>0)";
UpdateSguid += " WHERE linestand.slon > 0 AND linestand.sguid IS NULL;" + Environment.NewLine;
//从高德数据中拿线路站点
string sql = " SELECT a.sequence AS slno,a.short_name AS sname,a.longitude AS slon, a.latitude AS slat, ";
sql += "b.short_name AS lname,b.direction - 1 AS ismain FROM dbo.dw_stop a ";
sql += "LEFT JOIN dbo.dw_route b ON b.id = a.route_id ";
sql += "WHERE b.parent_id > 0 AND b.short_name LIKE '" + Lname + "%' ";
sql += "ORDER BY b.short_name";
string SQL = string.Empty;
DbHelper dbHelper = new DbHelper();
dbHelper.Add("DELETE FROM linestand;");
DataTable dt = dbHelper.Fill(sql);
StringBuilder sbStop = new StringBuilder();
foreach (DataRow dr in dt.Rows)
{
string slguid = Utility.CreateGuidLeft(36, totalstand.ToString() + "STANDDATA", '0', true);
string Slno = dr[0].ToString(); //站序
string SName = dr[1].ToString(); //站名
string Slon = dr[2].ToString(); //经度
string Slat = dr[3].ToString(); //纬度
string linename = dr[4].ToString(); //线路名称
string ismain = dr[5].ToString(); //上下行
SQL = "INSERT INTO linestand(slno,sname,slon,slat,lname,slguid,ismain) VALUES({0},'{1}',{2},{3},'{4}','{5}','{6}')";
SQL = string.Format(SQL, Slno, SName, Convert.ToDecimal(Slon), Convert.ToDecimal(Slat), linename, slguid, ismain);
//sw.WriteLine( SQL );
sbStop.Append(SQL);
totalstand++;
}
//插入高德数据
dbHelper.Add(sbStop.ToString());
//同步sguid
dbHelper.Add(UpdateSguid);
//插入line_standinfo表中不存在的站点
sql = "SELECT sname,slon,slat FROM linestand WHERE sguid IS null GROUP BY sname,slon,slat;";
DataTable dtStand = dbHelper.Fill(sql);
InportData.InportStandInfo(dtStand, @"f:\临时文件\standinfo.sql");
//再次同步sguid并同步liguid
string UpdateLiguid = "UPDATE linestand SET liguid=(SELECT liguid FROM line_lineinfo WHERE ismain=linestand.ismain AND LBGUID=(";
UpdateLiguid += " SELECT LBGUID FROM line_linebasicinfo WHERE lname = linestand.lname AND ISDELETED = '0')); ";
dbHelper.Add(UpdateSguid + UpdateLiguid);
//准备插入line_standdata
string InsertData = "select slguid,sguid,liguid,slno from dbo.linestand WHERE liguid IS NOT NULL ORDER by lname,ismain,slno;";
DataTable dtStandData = dbHelper.Fill(InsertData);
InportData.InportStandData(dtStandData, @"f:\临时文件\standdata.sql");
MessageBox.Show("数据已导入");
}