/// <summary>
/// 每条数据插入到数据库中
/// </summary>
/// <param name="carNum">车号</param>
/// <param name="liaoKou">料口</param>
/// <param name="jingDun">净吨</param>
/// <param name="riQi">手动输入的日期</param>
private bool InsertToSql(string carNum,string liaoKou,string dunshu,string riQi)
{
//根据车号查询车主姓名、超吨
string sql_sel = "select chezhu,beizhu from lz_chezhu where chehao='"+carNum+"'";
DBClass db = new DBClass();
DataTable dt_chezhu = db.DsGetinfo(sql_sel).Tables[0];
string chezhu = "新车主";
string xiandun = "999";
if (dt_chezhu.Rows.Count>0)
{
chezhu = dt_chezhu.Rows[0]["chezhu"].ToString();
xiandun = dt_chezhu.Rows[0]["beizhu"].ToString();
}
decimal xiandun_inp = 0;
decimal dunshu_inp = 0;
decimal chaodun=0;
//净吨和超吨必须是小数
if (!decimal.TryParse(dunshu,out dunshu_inp)||!decimal.TryParse(xiandun,out xiandun_inp))
{
MessageBox.Show("所读取的数据类型有误！","错误");
return false;
}
decimal jingdun = dunshu_inp;
if (dunshu_inp > xiandun_inp)
{
jingdun = xiandun_inp;
chaodun = dunshu_inp - xiandun_inp;
}
//插入数据库
string sql_ins = "insert into lz_data values('"+chezhu+"','"+carNum+"','"+riQi+"','"+liaoKou+"','"+dunshu_inp.ToString()+"','"+chaodun+"','"+jingdun+"','0')";
int i = db.Executeinfo(sql_ins);
if (i>0)
{
return true;
}
else
{
return false;
}
}
/// <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);
}
}