/// <summary>
/// 导入主方法
/// </summary>
private void importToSql()
{
GetTextValue(cboFileName);
string FilePath = controlTextValue;
if (File.Exists(FilePath))//存在此文件
{
SetSumLabelValue("正在确认导入的车辆总数···");
//HtmlAgilityPack自带加载html为htmlDocument
HtmlAgilityPack.HtmlDocument hd = new HtmlAgilityPack.HtmlDocument();
hd.Load(FilePath, UTF8Encoding.UTF8);
HtmlNode rootNode = hd.DocumentNode;
HtmlNodeCollection categoryNodeList = rootNode.SelectNodes("//font[@*]");//根据xpath获取节点树
if (categoryNodeList == null)
{
return;
}
//foreach读取效率比for、while高
//for (int i = categoryNodeList.Count-1; i >=0; i--)
//{
// if (categoryNodeList[i].InnerText.Contains("车数"))
// {
// CountTemp = Int32.Parse(categoryNodeList[i + 1].InnerText.Trim());
// break;
// }
//}
foreach (HtmlNode item in categoryNodeList)
{
if (item.InnerText.Contains("车数"))
{
CountTemp = Int32.Parse(categoryNodeList[categoryNodeList.IndexOf(item) + 1].InnerText.Trim());
break;
}
}
SetSumLabelValue("需要导入的总车数为： "+CountTemp);
SetControl(btnStop, true);
//从数据库中查询最大id作为统计标记
string sqlId = "select max(id) as 'id' from lz_data";
DBClass db = new DBClass();
DataTable dt = db.DsGetinfo(sqlId).Tables[0];
idTemp = dt.Rows[0]["id"].ToString();
//从数据库中获取需要查对的料口数据
List<string> liaokouData = getSqlData("select liaokou from lz_liaokou", "liaokou");
//条数标记
int flag = 0;
//遍历HtmlNodeCollection中的每一个标签内的内容，获取需要插入的数据
foreach (HtmlNode item in categoryNodeList)
{
if (item.InnerText.Trim() != "")
{
//判断车号：三位整数
string strTemp = item.InnerText.Trim();
int intTemp = 0;
if (strTemp.Length == 3 && Int32.TryParse(strTemp, out intTemp))
{
//判断料口：去前两位与数据库中比较，如果是三堆，则在车号前加‘*’
if (categoryNodeList.IndexOf(item) + 2>=categoryNodeList.Count)
{
break;
}
string liaokouTemp1 = categoryNodeList[categoryNodeList.IndexOf(item) + 2].InnerText.Trim();
int pageDown=categoryNodeList.IndexOf(item) - 17;
if (pageDown>=categoryNodeList.Count)
{
break;
}
if (liaokouTemp1.Length == 4)//infoCheck(liaokouData,liaokouTemp1))//如果存在，则是料口
{
//如果料口和车号的索引固定（未翻页），可确定其他数据的索引
liaokouTemp1 = liaokouTemp1.Substring(0, 2);
if (liaokouTemp1 == "三堆")
{
strTemp = "*" + strTemp;
}
//判断称重:长度为6位的小数
string chengzhongTemp = categoryNodeList[categoryNodeList.IndexOf(item) + 5].InnerText.Trim();
decimal czTemp = 0;
if (chengzhongTemp.Length == 6 && decimal.TryParse(chengzhongTemp, out czTemp))
{
//判断日期：长度13位或者14位
string riqiTemp = categoryNodeList[categoryNodeList.IndexOf(item) + 10].InnerText.Trim();
string riqiIns = getDate(riqiTemp);
bool b = InsertToSql(strTemp, liaokouTemp1, chengzhongTemp, riqiIns);
if (b == true)
{
flag++;
//在richtextbox显示导入的数据
SetRichTextBoxValue(flag + " - " + strTemp + " - " + liaokouTemp1 + " - " + chengzhongTemp + " - " + riqiIns + "\n");
SetLabelValue(flag);
SetProcessBarValue(flag,CountTemp);
}
else
{
this.Text = "存在未导入数据，请审核！";
}
}
}
else if (categoryNodeList[categoryNodeList.IndexOf(item) - 17].InnerText.Trim().Length == 4)//翻页时
{
liaokouTemp1 = categoryNodeList[categoryNodeList.IndexOf(item) - 17].InnerText.Trim();
//如果料口和车号的索引固定（未翻页），可确定其他数据的索引
liaokouTemp1 = liaokouTemp1.Substring(0, 2);
if (liaokouTemp1 == "三堆")
{
strTemp = "*" + strTemp;
}
//判断称重:长度为6位的小数
string chengzhongTemp = categoryNodeList[categoryNodeList.IndexOf(item) - 14].InnerText.Trim();
decimal czTemp = 0;
if (chengzhongTemp.Length == 6 && decimal.TryParse(chengzhongTemp, out czTemp))
{
//判断日期：长度13位或者14位
string riqiTemp = categoryNodeList[categoryNodeList.IndexOf(item) + 4].InnerText.Trim();
string riqiIns = getDate(riqiTemp);
bool b = InsertToSql(strTemp, liaokouTemp1, chengzhongTemp, riqiIns);
if (b == true)
{
flag++;
//int valueTemp = flag / CountTemp * 100;
//在richtextbox显示导入的数据
SetRichTextBoxValue(flag + " - " + strTemp + " - " + liaokouTemp1 + " - " + chengzhongTemp + " - " + riqiIns + "\n");
SetLabelValue(flag);
SetProcessBarValue(flag,CountTemp);
}
else
{
this.Text = "存在未导入数据，请审核后导入！";
}
}
}
}
}
}
//遍历完毕，统计数据库中本次录入的车数和吨数（根据ID）
string sqlCount = "select count(id) as 'cheshu',sum(dunshu) as 'dunshu' from lz_data where id>'" + idTemp + "'";
SqlDataReader dr = db.SqlGetinfo(sqlCount);
if (dr.Read())
{
SetSumLabelValue("导入合计：" + dr["cheshu"].ToString() + " 车 " + dr["dunshu"].ToString() + " 吨");
}
//如果操作有误，可放弃保存本次导入的数据
DialogResult dr2 = MessageBox.Show("导入完成 "+dr["cheshu"].ToString()+" 车 "+dr["dunshu"].ToString()+"吨，\n请确定后保存？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (dr2 == DialogResult.No)
{
string sqlDel = "delete from lz_data where id>'" + idTemp + "'";
int ires = db.Executeinfo(sqlDel);
if (ires > 0)
{
SetSumLabelValue( "已删除" + ires.ToString() + "条，未做任何更改");
SetProcessBarValue(0, CountTemp);
SetLabelValue(0);
SetRichTextBoxValue("已删除" + ires.ToString() + "条，未做任何更改");
}
}
SetControl(btnImport, true);
SetControl(btnExit, true);
SetControl(btnStop, false);
}
}
/// <summary>
/// 根据sql语句获取数据库中数据，并返回泛型数据数组
/// </summary>
/// <param name="cmd">查询语句</param>
/// <returns>泛型数组</returns>
private List<string> getSqlData(string cmd,string column)
{
List<string> sqlData = new List<string>();
DBClass db = new DBClass();
SqlDataReader sDR = db.SqlGetinfo(cmd);
while (sDR.Read())
{
sqlData.Add(sDR[column].ToString());
}
sDR.Close();
return sqlData;
}