static public Hashtable SystemStartParaDownLoad(out Exception e) { Hashtable htout = new Hashtable(); bool noconnectflag = true; //未连接 bool centerNoPara = true; //中心参数不全 bool nodownFlag = true; //下载失败 DataSet dstmp = new DataSet(); try { e = null; conCenter.ConnectionString = CenterConString; string sql; conCenter.Open(); sql = "select * from tbCommCode"; daTmp = new SqlDataAdapter(sql, conCenter); daTmp.Fill(dstmp, "CenCommCode"); sql = "select * from tbDept"; daTmp = new SqlDataAdapter(sql, conCenter); daTmp.Fill(dstmp, "CenDept"); sql = "select * from tbFunc"; daTmp = new SqlDataAdapter(sql, conCenter); daTmp.Fill(dstmp, "CenFunc"); sql = "select * from tbOper"; daTmp = new SqlDataAdapter(sql, conCenter); daTmp.Fill(dstmp, "CenOper"); sql = "select * from tbOperFunc"; daTmp = new SqlDataAdapter(sql, conCenter); daTmp.Fill(dstmp, "CenOperFunc"); sql = "select * from tbRegister"; daTmp = new SqlDataAdapter(sql, conCenter); daTmp.Fill(dstmp, "CenRegister"); conCenter.Close(); noconnectflag = false; if (noconnectflag == false) { if (dstmp.Tables["CenCommCode"].Rows.Count >= 24 && dstmp.Tables["CenDept"].Rows.Count >= 1 && dstmp.Tables["CenOper"].Rows.Count > 0) { centerNoPara = false; } else { centerNoPara = true; htout.Add("IsNoConnect", noconnectflag); htout.Add("IsNoPara", centerNoPara); htout.Add("IsNoDown", nodownFlag); return(htout); } } } catch (Exception err) { if (conCenter.State == ConnectionState.Open) { conCenter.Close(); } e = err; htout.Clear(); htout.Add("IsNoConnect", noconnectflag); htout.Add("IsNoPara", centerNoPara); htout.Add("IsNoDown", nodownFlag); return(htout); } con.ConnectionString = ConString; SqlDataReader drr; SqlCommand cmd; con.Open(); using (SqlTransaction trans = con.BeginTransaction(IsolationLevel.ReadCommitted)) { try { string strsql = ""; int i = 0; #region 更新tbCommCode strsql = "truncate table tbCommCode"; cmd = new SqlCommand(strsql, con, trans); cmd.ExecuteNonQuery(); strsql = ""; for (i = 0; i < dstmp.Tables["CenCommCode"].Rows.Count; i++) { strsql += "insert into tbCommCode values('" + dstmp.Tables["CenCommCode"].Rows[i]["cnvcCommName"].ToString() + "','" + dstmp.Tables["CenCommCode"].Rows[i]["cnvcCommCode"].ToString() + "','" + dstmp.Tables["CenCommCode"].Rows[i]["cnvcCommSign"].ToString() + "','" + dstmp.Tables["CenCommCode"].Rows[i]["cnvcComments"].ToString() + "');"; } if (strsql != "") { cmd = new SqlCommand(strsql, con, trans); cmd.ExecuteNonQuery(); } #endregion #region 更新tbDept strsql = ""; strsql = "delete from tbDept where cnvcLocalFlag<>'LOCAL' or cnvcLocalFlag is null"; cmd = new SqlCommand(strsql, con, trans); cmd.ExecuteNonQuery(); CMSMStruct.DeptStruct depts = new CMSMData.CMSMStruct.DeptStruct(); strsql = "select * from tbDept where cnvcLocalFlag='LOCAL'"; cmd = new SqlCommand(strsql, con, trans); drr = cmd.ExecuteReader(); if (drr.HasRows) { drr.Read(); depts.strDeptID = drr[0].ToString(); depts.strDeptName = drr[1].ToString(); depts.strParentDeptID = drr[2].ToString(); depts.strLocalFlag = drr[3].ToString(); LocalDeptID = depts.strDeptID; LocalDeptName = depts.strDeptName; drr.Close(); strsql = ""; for (i = 0; i < dstmp.Tables["CenDept"].Rows.Count; i++) { if (dstmp.Tables["CenDept"].Rows[i]["cnvcDeptID"].ToString() != depts.strDeptID) { strsql += "insert into tbDept values('" + dstmp.Tables["CenDept"].Rows[i]["cnvcDeptID"].ToString() + "','" + dstmp.Tables["CenDept"].Rows[i]["cnvcDeptName"].ToString() + "','" + dstmp.Tables["CenDept"].Rows[i]["cnvcParentDeptID"].ToString() + "',null);"; } } if (strsql != "") { cmd = new SqlCommand(strsql, con, trans); cmd.ExecuteNonQuery(); } strsql = ""; if (depts.strDeptID != "NULL") { for (i = 0; i < dstmp.Tables["CenDept"].Rows.Count; i++) { if (dstmp.Tables["CenDept"].Rows[i]["cnvcDeptID"].ToString() == depts.strDeptID) { if (dstmp.Tables["CenDept"].Rows[i]["cnvcDeptName"].ToString() != depts.strDeptName) { strsql += "update tbDept set cnvcDeptName='" + dstmp.Tables["CenDept"].Rows[i]["cnvcDeptName"].ToString() + "' where cnvcLocalFlag='LOCAL' and cnvcDeptID='" + depts.strDeptID + "';"; strsql += "update tbRegister set cnvcDeptName='" + dstmp.Tables["CenDept"].Rows[i]["cnvcDeptName"].ToString() + "' where cnvcDeptName='" + depts.strDeptName + "';"; cmd = new SqlCommand(strsql, con, trans); cmd.ExecuteNonQuery(); strsql = ""; break; } } } } } else { drr.Close(); strsql = ""; for (i = 0; i < dstmp.Tables["CenDept"].Rows.Count; i++) { strsql += "insert into tbDept values('" + dstmp.Tables["CenDept"].Rows[i]["cnvcDeptID"].ToString() + "','" + dstmp.Tables["CenDept"].Rows[i]["cnvcDeptName"].ToString() + "','" + dstmp.Tables["CenDept"].Rows[i]["cnvcParentDeptID"].ToString() + "',null);"; } if (strsql != "") { cmd = new SqlCommand(strsql, con, trans); cmd.ExecuteNonQuery(); } } #endregion #region 更新tbFunc strsql = ""; strsql = "truncate table tbFunc"; cmd = new SqlCommand(strsql, con, trans); cmd.ExecuteNonQuery(); strsql = ""; for (i = 0; i < dstmp.Tables["CenFunc"].Rows.Count; i++) { strsql += "insert into tbFunc values('" + dstmp.Tables["CenFunc"].Rows[i]["cnvcFuncName"].ToString() + "','" + dstmp.Tables["CenFunc"].Rows[i]["cnvcFuncAddress"].ToString() + "','" + dstmp.Tables["CenFunc"].Rows[i]["cnvcFuncType"].ToString() + "');"; } if (strsql != "") { cmd = new SqlCommand(strsql, con, trans); cmd.ExecuteNonQuery(); } #endregion #region 更新tbOper strsql = ""; strsql = "truncate table tbOper"; cmd = new SqlCommand(strsql, con, trans); cmd.ExecuteNonQuery(); strsql = ""; for (i = 0; i < dstmp.Tables["CenOper"].Rows.Count; i++) { strsql += "insert into tbOper values(" + dstmp.Tables["CenOper"].Rows[i]["cnnOperID"].ToString() + ",'" + dstmp.Tables["CenOper"].Rows[i]["cnvcOperName"].ToString() + "','" + dstmp.Tables["CenOper"].Rows[i]["cnvcPwd"].ToString() + "','" + dstmp.Tables["CenOper"].Rows[i]["cnvcDeptID"].ToString() + "');"; } if (strsql != "") { cmd = new SqlCommand(strsql, con, trans); cmd.ExecuteNonQuery(); } #endregion #region 更新tbOperFunc strsql = ""; strsql = "truncate table tbOperFunc"; cmd = new SqlCommand(strsql, con, trans); cmd.ExecuteNonQuery(); strsql = ""; for (i = 0; i < dstmp.Tables["CenOperFunc"].Rows.Count; i++) { strsql += "insert into tbOperFunc values(" + dstmp.Tables["CenOperFunc"].Rows[i]["cnnOperID"].ToString() + ",'" + dstmp.Tables["CenOperFunc"].Rows[i]["cnvcFuncName"].ToString() + "','" + dstmp.Tables["CenOperFunc"].Rows[i]["cnvcFuncAddress"].ToString() + "');"; } if (strsql != "") { cmd = new SqlCommand(strsql, con, trans); cmd.ExecuteNonQuery(); } #endregion #region 更新tbRegister strsql = ""; strsql = "truncate table tbRegister"; cmd = new SqlCommand(strsql, con, trans); cmd.ExecuteNonQuery(); strsql = ""; for (i = 0; i < dstmp.Tables["CenRegister"].Rows.Count; i++) { strsql += "insert into tbRegister values('" + dstmp.Tables["CenRegister"].Rows[i]["cnvcHddSerialNo"].ToString() + "','" + dstmp.Tables["CenRegister"].Rows[i]["cnvcRegister"].ToString() + "','" + dstmp.Tables["CenRegister"].Rows[i]["cnvcDeptName"].ToString() + "','" + dstmp.Tables["CenRegister"].Rows[i]["cnvcOperName"].ToString() + "','" + dstmp.Tables["CenRegister"].Rows[i]["cndOperDate"].ToString() + "');"; } if (strsql != "") { cmd = new SqlCommand(strsql, con, trans); cmd.ExecuteNonQuery(); } #endregion trans.Commit(); nodownFlag = false; htout.Clear(); htout.Add("IsNoConnect", noconnectflag); htout.Add("IsNoPara", centerNoPara); htout.Add("IsNoDown", nodownFlag); return(htout); } catch (Exception err) { trans.Rollback(); e = err; htout.Clear(); htout.Add("IsNoConnect", noconnectflag); htout.Add("IsNoPara", centerNoPara); htout.Add("IsNoDown", nodownFlag); return(htout); } finally { con.Close(); } } }
private void simpleButton1_Click(object sender, System.EventArgs e) { this.pictureBox1.Visible = true; this.sbtnClose.Enabled = false; #region 手动下载更新 DataSet dstmp = new DataSet(); int i = 0; SqlDataAdapter daTmp; SqlConnection conCenter = new SqlConnection(); SqlConnection con = new SqlConnection(); SqlCommand cmd; SqlDataReader drr; conCenter.ConnectionString = SysInitial.CenterConString; con.ConnectionString = SysInitial.ConString; try { conCenter.Open(); } catch (Exception conerr) { clog.WriteLine(conerr); this.listBox1.Items.Add("--------------------------------"); this.listBox1.Items.Add("连接中心数据库失败,请检查网络!"); this.sbtnClose.Enabled = true; return; } con.Open(); using (SqlTransaction transCenter = conCenter.BeginTransaction(IsolationLevel.ReadCommitted)) { using (SqlTransaction transLocal = con.BeginTransaction(IsolationLevel.ReadCommitted)) { try { e = null; string sql, sql2; this.listBox1.Items.Add("--------------------------------"); this.listBox1.Items.Add("开始加载中心数据库资料..."); this.listBox1.SelectedIndex = this.listBox1.Items.Count - 1; this.Refresh(); sql = "select * from tbCommCode"; cmd = new SqlCommand(sql, conCenter, transCenter); daTmp = new SqlDataAdapter(cmd); daTmp.Fill(dstmp, "CenCommCode"); sql = "select * from tbDept"; cmd = new SqlCommand(sql, conCenter, transCenter); daTmp = new SqlDataAdapter(cmd); daTmp.Fill(dstmp, "CenDept"); sql = "select * from tbFunc"; cmd = new SqlCommand(sql, conCenter, transCenter); daTmp = new SqlDataAdapter(cmd); daTmp.Fill(dstmp, "CenFunc"); sql = "select * from tbOper"; cmd = new SqlCommand(sql, conCenter, transCenter); daTmp = new SqlDataAdapter(cmd); daTmp.Fill(dstmp, "CenOper"); sql = "select * from tbOperFunc"; cmd = new SqlCommand(sql, conCenter, transCenter); daTmp = new SqlDataAdapter(cmd); daTmp.Fill(dstmp, "CenOperFunc"); sql = "select * from tbRegister"; cmd = new SqlCommand(sql, conCenter, transCenter); daTmp = new SqlDataAdapter(cmd); daTmp.Fill(dstmp, "CenRegister"); DateTime dt1MonthBefore = DateTime.Today.AddMonths(-1); sql = "select * from tbOilPrice where cndPriceDate>='" + dt1MonthBefore.ToShortDateString() + "'"; cmd = new SqlCommand(sql, conCenter, transCenter); daTmp = new SqlDataAdapter(cmd); daTmp.Fill(dstmp, "CenOilPrice"); sql = "select * from tbMember"; cmd = new SqlCommand(sql, conCenter, transCenter); daTmp = new SqlDataAdapter(cmd); daTmp.Fill(dstmp, "CenMember"); sql = "select * from tbSpecialOilDept"; cmd = new SqlCommand(sql, conCenter, transCenter); daTmp = new SqlDataAdapter(cmd); daTmp.Fill(dstmp, "CenSpecOilDept"); sql = "select * from tbConsItem"; cmd = new SqlCommand(sql, con, transLocal); daTmp = new SqlDataAdapter(cmd); daTmp.Fill(dstmp, "LocalConsItem"); sql = "select * from tbMebCompanyPrepay"; cmd = new SqlCommand(sql, conCenter, transCenter); daTmp = new SqlDataAdapter(cmd); daTmp.Fill(dstmp, "CenMebCompanyPrepay"); this.listBox1.Items.Add("加载中心数据库资料成功!"); this.listBox1.Items.Add("--------------------------------"); this.listBox1.SelectedIndex = this.listBox1.Items.Count - 1; this.Refresh(); this.listBox1.Items.Add("更新本地数据:系统基本参数信息..."); this.listBox1.SelectedIndex = this.listBox1.Items.Count - 1; this.Refresh(); #region 更新tbCommCode sql = "truncate table tbCommCode"; cmd = new SqlCommand(sql, con, transLocal); cmd.ExecuteNonQuery(); sql = ""; for (i = 0; i < dstmp.Tables["CenCommCode"].Rows.Count; i++) { sql += "insert into tbCommCode values('" + dstmp.Tables["CenCommCode"].Rows[i]["cnvcCommName"].ToString() + "','" + dstmp.Tables["CenCommCode"].Rows[i]["cnvcCommCode"].ToString() + "','" + dstmp.Tables["CenCommCode"].Rows[i]["cnvcCommSign"].ToString() + "','" + dstmp.Tables["CenCommCode"].Rows[i]["cnvcComments"].ToString() + "');"; } if (sql != "") { cmd = new SqlCommand(sql, con, transLocal); cmd.ExecuteNonQuery(); } #endregion this.listBox1.Items.Add("更新本地数据:系统基本参数信息更新成功..."); this.listBox1.Items.Add("--------------------------------"); this.listBox1.SelectedIndex = this.listBox1.Items.Count - 1; this.Refresh(); this.listBox1.Items.Add("更新本地数据:部门信息..."); this.listBox1.SelectedIndex = this.listBox1.Items.Count - 1; this.Refresh(); #region 更新tbDept sql = ""; sql = "delete from tbDept where cnvcLocalFlag<>'LOCAL' or cnvcLocalFlag is null"; cmd = new SqlCommand(sql, con, transLocal); cmd.ExecuteNonQuery(); CMSMStruct.DeptStruct depts = new CMSMData.CMSMStruct.DeptStruct(); sql = "select * from tbDept where cnvcLocalFlag='LOCAL'"; cmd = new SqlCommand(sql, con, transLocal); drr = cmd.ExecuteReader(); if (drr.HasRows) { drr.Read(); depts.strDeptID = drr[0].ToString(); depts.strDeptName = drr[1].ToString(); depts.strParentDeptID = drr[2].ToString(); depts.strLocalFlag = drr[3].ToString(); drr.Close(); sql = ""; for (i = 0; i < dstmp.Tables["CenDept"].Rows.Count; i++) { if (dstmp.Tables["CenDept"].Rows[i]["cnvcDeptID"].ToString() != depts.strDeptID) { sql += "insert into tbDept values('" + dstmp.Tables["CenDept"].Rows[i]["cnvcDeptID"].ToString() + "','" + dstmp.Tables["CenDept"].Rows[i]["cnvcDeptName"].ToString() + "','" + dstmp.Tables["CenDept"].Rows[i]["cnvcParentDeptID"].ToString() + "',null);"; } } if (sql != "") { cmd = new SqlCommand(sql, con, transLocal); cmd.ExecuteNonQuery(); } sql = ""; if (depts.strDeptID != "NULL") { for (i = 0; i < dstmp.Tables["CenDept"].Rows.Count; i++) { if (dstmp.Tables["CenDept"].Rows[i]["cnvcDeptID"].ToString() == depts.strDeptID) { if (dstmp.Tables["CenDept"].Rows[i]["cnvcDeptName"].ToString() != depts.strDeptName) { sql += "update tbDept set cnvcDeptName='" + dstmp.Tables["CenDept"].Rows[i]["cnvcDeptName"].ToString() + "' where cnvcLocalFlag='LOCAL' and cnvcDeptID='" + depts.strDeptID + "';"; sql += "update tbRegister set cnvcDeptName='" + dstmp.Tables["CenDept"].Rows[i]["cnvcDeptName"].ToString() + "' where cnvcDeptName='" + depts.strDeptName + "';"; cmd = new SqlCommand(sql, con, transLocal); cmd.ExecuteNonQuery(); sql = ""; break; } } } } } else { drr.Close(); sql = ""; for (i = 0; i < dstmp.Tables["CenDept"].Rows.Count; i++) { sql += "insert into tbDept values('" + dstmp.Tables["CenDept"].Rows[i]["cnvcDeptID"].ToString() + "','" + dstmp.Tables["CenDept"].Rows[i]["cnvcDeptName"].ToString() + "','" + dstmp.Tables["CenDept"].Rows[i]["cnvcParentDeptID"].ToString() + "',null);"; } if (sql != "") { cmd = new SqlCommand(sql, con, transLocal); cmd.ExecuteNonQuery(); } } #endregion this.listBox1.Items.Add("更新本地数据:部门信息更新成功..."); this.listBox1.Items.Add("--------------------------------"); this.listBox1.SelectedIndex = this.listBox1.Items.Count - 1; this.Refresh(); this.listBox1.Items.Add("更新本地数据:功能列表信息..."); this.listBox1.SelectedIndex = this.listBox1.Items.Count - 1; this.Refresh(); #region 更新tbFunc sql = ""; sql = "truncate table tbFunc"; cmd = new SqlCommand(sql, con, transLocal); cmd.ExecuteNonQuery(); sql = ""; for (i = 0; i < dstmp.Tables["CenFunc"].Rows.Count; i++) { sql += "insert into tbFunc values('" + dstmp.Tables["CenFunc"].Rows[i]["cnvcFuncName"].ToString() + "','" + dstmp.Tables["CenFunc"].Rows[i]["cnvcFuncAddress"].ToString() + "','" + dstmp.Tables["CenFunc"].Rows[i]["cnvcFuncType"].ToString() + "');"; } if (sql != "") { cmd = new SqlCommand(sql, con, transLocal); cmd.ExecuteNonQuery(); } #endregion this.listBox1.Items.Add("更新本地数据:功能列表信息更新成功..."); this.listBox1.Items.Add("--------------------------------"); this.listBox1.SelectedIndex = this.listBox1.Items.Count - 1; this.Refresh(); this.listBox1.Items.Add("更新本地数据:操作员信息..."); this.listBox1.SelectedIndex = this.listBox1.Items.Count - 1; this.Refresh(); #region 更新tbOper sql = ""; sql = "truncate table tbOper"; cmd = new SqlCommand(sql, con, transLocal); cmd.ExecuteNonQuery(); sql = ""; for (i = 0; i < dstmp.Tables["CenOper"].Rows.Count; i++) { sql += "insert into tbOper values(" + dstmp.Tables["CenOper"].Rows[i]["cnnOperID"].ToString() + ",'" + dstmp.Tables["CenOper"].Rows[i]["cnvcOperName"].ToString() + "','" + dstmp.Tables["CenOper"].Rows[i]["cnvcPwd"].ToString() + "','" + dstmp.Tables["CenOper"].Rows[i]["cnvcDeptID"].ToString() + "');"; } if (sql != "") { cmd = new SqlCommand(sql, con, transLocal); cmd.ExecuteNonQuery(); } #endregion this.listBox1.Items.Add("更新本地数据:操作员信息更新成功..."); this.listBox1.Items.Add("--------------------------------"); this.listBox1.SelectedIndex = this.listBox1.Items.Count - 1; this.Refresh(); this.listBox1.Items.Add("更新本地数据:操作员权限信息..."); this.listBox1.SelectedIndex = this.listBox1.Items.Count - 1; this.Refresh(); #region 更新tbOperFunc sql = ""; sql = "truncate table tbOperFunc"; cmd = new SqlCommand(sql, con, transLocal); cmd.ExecuteNonQuery(); sql = ""; for (i = 0; i < dstmp.Tables["CenOperFunc"].Rows.Count; i++) { sql += "insert into tbOperFunc values(" + dstmp.Tables["CenOperFunc"].Rows[i]["cnnOperID"].ToString() + ",'" + dstmp.Tables["CenOperFunc"].Rows[i]["cnvcFuncName"].ToString() + "','" + dstmp.Tables["CenOperFunc"].Rows[i]["cnvcFuncAddress"].ToString() + "');"; } if (sql != "") { cmd = new SqlCommand(sql, con, transLocal); cmd.ExecuteNonQuery(); } #endregion this.listBox1.Items.Add("更新本地数据:操作员权限信息更新成功..."); this.listBox1.Items.Add("--------------------------------"); this.listBox1.SelectedIndex = this.listBox1.Items.Count - 1; this.Refresh(); #region 更新tbRegister sql = ""; sql = "truncate table tbRegister"; cmd = new SqlCommand(sql, con, transLocal); cmd.ExecuteNonQuery(); sql = ""; for (i = 0; i < dstmp.Tables["CenRegister"].Rows.Count; i++) { sql += "insert into tbRegister values('" + dstmp.Tables["CenRegister"].Rows[i]["cnvcHddSerialNo"].ToString() + "','" + dstmp.Tables["CenRegister"].Rows[i]["cnvcRegister"].ToString() + "','" + dstmp.Tables["CenRegister"].Rows[i]["cnvcDeptName"].ToString() + "','" + dstmp.Tables["CenRegister"].Rows[i]["cnvcOperName"].ToString() + "','" + dstmp.Tables["CenRegister"].Rows[i]["cndOperDate"].ToString() + "');"; } if (sql != "") { cmd = new SqlCommand(sql, con, transLocal); cmd.ExecuteNonQuery(); } #endregion this.listBox1.Items.Add("更新本地数据:油价信息..."); this.listBox1.SelectedIndex = this.listBox1.Items.Count - 1; this.Refresh(); #region 更新本地tbOilPrice sql2 = ""; for (i = 0; i < dstmp.Tables["CenOilPrice"].Rows.Count; i++) { sql = "select count(*) from tbOilPrice where cnnSerialNo='" + dstmp.Tables["CenOilPrice"].Rows[i]["cnnSerialNo"].ToString() + "'"; cmd = new SqlCommand(sql, con, transLocal); drr = cmd.ExecuteReader(); drr.Read(); int PriceCount = int.Parse(drr[0].ToString()); drr.Close(); if (PriceCount == 0) { sql2 += "insert into tbOilPrice values('" + dstmp.Tables["CenOilPrice"].Rows[i]["cnnSerialNo"].ToString() + "','" + dstmp.Tables["CenOilPrice"].Rows[i]["cndPriceDate"].ToString() + "','" + dstmp.Tables["CenOilPrice"].Rows[i]["cnvcGoodsName"].ToString() + "','" + dstmp.Tables["CenOilPrice"].Rows[i]["cnvcGoodsType"].ToString() + "','" + dstmp.Tables["CenOilPrice"].Rows[i]["cnvcUnit"].ToString() + "'," + dstmp.Tables["CenOilPrice"].Rows[i]["cnnOilPrice"].ToString() + ",'" + dstmp.Tables["CenOilPrice"].Rows[i]["cnvcDeptName"].ToString() + "','" + dstmp.Tables["CenOilPrice"].Rows[i]["cnvcDeptID"].ToString() + "');"; } } if (sql2 != "") { cmd = new SqlCommand(sql2, con, transLocal); cmd.ExecuteNonQuery(); } #endregion this.listBox1.Items.Add("更新本地数据:油价信息更新成功..."); this.listBox1.Items.Add("--------------------------------"); this.listBox1.SelectedIndex = this.listBox1.Items.Count - 1; this.Refresh(); this.listBox1.Items.Add("更新本地数据:会员信息..."); this.listBox1.SelectedIndex = this.listBox1.Items.Count - 1; this.Refresh(); #region 更新本地tbMember sql2 = ""; int MebCount = 0; for (i = 0; i < dstmp.Tables["CenMember"].Rows.Count; i++) { MebCount = 0; sql = "select count(*) from tbMember where cnvcCardID='" + dstmp.Tables["CenMember"].Rows[i]["cnvcCardID"].ToString() + "'"; cmd = new SqlCommand(sql, con, transLocal); drr = cmd.ExecuteReader(); drr.Read(); MebCount = int.Parse(drr[0].ToString()); drr.Close(); if (MebCount == 0) { sql2 += "insert into tbMember values('" + dstmp.Tables["CenMember"].Rows[i]["cnvcCardID"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cnvcCompanyID"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cnvcCompanyName"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cnvcLicenseTag"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cnvcGoodsName"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cnvcGoodsType"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cnvcDeptID"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cnvcDeptName"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cnvcState"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cnvcComments"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cndCreateDate"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cnvcOperName"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cndOperDate"].ToString() + "');"; } else { MebCount = 0; sql = "select count(*) from tbMember where cnvcCardID='" + dstmp.Tables["CenMember"].Rows[i]["cnvcCardID"].ToString() + "' and cndOperDate<'" + dstmp.Tables["CenMember"].Rows[i]["cndOperDate"].ToString() + "'"; cmd = new SqlCommand(sql, con, transLocal); drr = cmd.ExecuteReader(); drr.Read(); MebCount = int.Parse(drr[0].ToString()); drr.Close(); if (MebCount > 0) { sql2 += "delete from tbMember where cnvcCardID='" + dstmp.Tables["CenMember"].Rows[i]["cnvcCardID"].ToString() + "';"; sql2 += "insert into tbMember values('" + dstmp.Tables["CenMember"].Rows[i]["cnvcCardID"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cnvcCompanyID"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cnvcCompanyName"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cnvcLicenseTag"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cnvcGoodsName"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cnvcGoodsType"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cnvcDeptID"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cnvcDeptName"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cnvcState"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cnvcComments"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cndCreateDate"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cnvcOperName"].ToString() + "','" + dstmp.Tables["CenMember"].Rows[i]["cndOperDate"].ToString() + "');"; } } } if (sql2 != "") { cmd = new SqlCommand(sql2, con, transLocal); cmd.ExecuteNonQuery(); } #endregion this.listBox1.Items.Add("更新本地数据:会员信息更新成功..."); this.listBox1.Items.Add("--------------------------------"); this.listBox1.SelectedIndex = this.listBox1.Items.Count - 1; this.Refresh(); this.listBox1.Items.Add("更新本地数据:专供油单位及合同信息..."); this.Refresh(); #region 更新本地tbSpecialOilDept sql2 = ""; for (i = 0; i < dstmp.Tables["CenSpecOilDept"].Rows.Count; i++) { sql = "select count(*) from tbSpecialOilDept where cnvcContractNo='" + dstmp.Tables["CenSpecOilDept"].Rows[i]["cnvcContractNo"].ToString() + "'"; cmd = new SqlCommand(sql, con, transLocal); drr = cmd.ExecuteReader(); drr.Read(); int SpecDeptCount = int.Parse(drr[0].ToString()); drr.Close(); if (SpecDeptCount == 0) { sql2 += "insert into tbSpecialOilDept values('" + dstmp.Tables["CenSpecOilDept"].Rows[i]["cnvcContractNo"].ToString() + "','" + dstmp.Tables["CenSpecOilDept"].Rows[i]["cnvcDeliveryCompany"].ToString() + "')"; } } if (sql2 != "") { cmd = new SqlCommand(sql2, con, transLocal); cmd.ExecuteNonQuery(); } #endregion this.listBox1.Items.Add("更新本地数据:专供油单位及合同信息更新成功..."); this.listBox1.Items.Add("--------------------------------"); this.Refresh(); this.listBox1.Items.Add("更新中心和本地数据:会员单位及余额信息..."); this.listBox1.SelectedIndex = this.listBox1.Items.Count - 1; this.Refresh(); #region 更新本地tbMebCompanyPrepay sql2 = ""; for (i = 0; i < dstmp.Tables["CenMebCompanyPrepay"].Rows.Count; i++) { sql = "select count(*) from tbMebCompanyPrepay where cnvcCompanyID='" + dstmp.Tables["CenMebCompanyPrepay"].Rows[i]["cnvcCompanyID"].ToString() + "'"; cmd = new SqlCommand(sql, con, transLocal); drr = cmd.ExecuteReader(); drr.Read(); int MebCompCount = int.Parse(drr[0].ToString()); drr.Close(); if (MebCompCount == 0) { sql2 += "insert into tbMebCompanyPrepay values('" + dstmp.Tables["CenMebCompanyPrepay"].Rows[i]["cnvcCompanyID"].ToString() + "','" + dstmp.Tables["CenMebCompanyPrepay"].Rows[i]["cnvcCompanyName"].ToString() + "','" + dstmp.Tables["CenMebCompanyPrepay"].Rows[i]["cnvcAcctID"].ToString() + "'," + dstmp.Tables["CenMebCompanyPrepay"].Rows[i]["cnnPrepayFee"].ToString() + ",'" + dstmp.Tables["CenMebCompanyPrepay"].Rows[i]["cnvcDeptName"].ToString() + "','" + dstmp.Tables["CenMebCompanyPrepay"].Rows[i]["cnvcDeptID"].ToString() + "');"; } } if (sql2 != "") { cmd = new SqlCommand(sql2, con, transLocal); cmd.ExecuteNonQuery(); } #endregion #region tbConsItem,并进行余额计算,更新中心和本地 //上传tbConsItem sql2 = ""; for (i = 0; i < dstmp.Tables["LocalConsItem"].Rows.Count; i++) { sql = "select count(*) from tbConsItem where cnnSerial='" + dstmp.Tables["LocalConsItem"].Rows[i]["cnnSerial"].ToString() + "'"; cmd = new SqlCommand(sql, conCenter, transCenter); drr = cmd.ExecuteReader(); drr.Read(); int ConsCount = int.Parse(drr[0].ToString()); drr.Close(); if (ConsCount == 0) { sql2 += "insert into tbConsItem values('" + dstmp.Tables["LocalConsItem"].Rows[i]["cnnSerial"].ToString() + "','" + dstmp.Tables["LocalConsItem"].Rows[i]["cnvcCardID"].ToString() + "','" + dstmp.Tables["LocalConsItem"].Rows[i]["cnvcLicenseTags"].ToString() + "','" + dstmp.Tables["LocalConsItem"].Rows[i]["cnvcGoodsName"].ToString() + "','" + dstmp.Tables["LocalConsItem"].Rows[i]["cnvcGoodsType"].ToString() + "','" + dstmp.Tables["LocalConsItem"].Rows[i]["cnvcUnit"].ToString() + "'," + dstmp.Tables["LocalConsItem"].Rows[i]["cnnDensity"].ToString() + "," + dstmp.Tables["LocalConsItem"].Rows[i]["cnnKGCount"].ToString() + "," + dstmp.Tables["LocalConsItem"].Rows[i]["cnnCount"].ToString() + "," + dstmp.Tables["LocalConsItem"].Rows[i]["cnnPrice"].ToString() + "," + dstmp.Tables["LocalConsItem"].Rows[i]["cnnFee"].ToString() + ",'" + dstmp.Tables["LocalConsItem"].Rows[i]["cnvcComments"].ToString() + "','" + dstmp.Tables["LocalConsItem"].Rows[i]["cnvcConsType"].ToString() + "','" + dstmp.Tables["LocalConsItem"].Rows[i]["cndConsDate"].ToString() + "','" + dstmp.Tables["LocalConsItem"].Rows[i]["cnvcCompanyID"].ToString() + "','" + dstmp.Tables["LocalConsItem"].Rows[i]["cnvcCompanyName"].ToString() + "','" + dstmp.Tables["LocalConsItem"].Rows[i]["cnvcAcctID"].ToString() + "','" + dstmp.Tables["LocalConsItem"].Rows[i]["cnvcDeptID"].ToString() + "','" + dstmp.Tables["LocalConsItem"].Rows[i]["cnvcDeptName"].ToString() + "','" + dstmp.Tables["LocalConsItem"].Rows[i]["cnvcOperName"].ToString() + "','" + dstmp.Tables["LocalConsItem"].Rows[i]["cndOperDate"].ToString() + "');"; } } if (sql2 != "") { cmd = new SqlCommand(sql2, conCenter, transCenter); cmd.ExecuteNonQuery(); } sql = "insert into tbConsItemHis select * from tbConsItem"; cmd = new SqlCommand(sql, con, transLocal); cmd.ExecuteNonQuery(); sql = "truncate table tbConsItem"; cmd = new SqlCommand(sql, con, transLocal); cmd.ExecuteNonQuery(); //计算中心本地部门的单位余额 sql = "select cnvcCompanyID,isnull(sum(cnnFillFee),0) as TolFill from tbFillFee where cnvcDeptID='" + SysInitial.LocalDeptID + "' group by cnvcCompanyID order by cnvcCompanyID"; cmd = new SqlCommand(sql, conCenter, transCenter); daTmp = new SqlDataAdapter(cmd); daTmp.Fill(dstmp, "CenCompFill"); sql = "select cnvcCompanyID,isnull(sum(cnnFee),0) as TolCons from tbConsItem where cnvcDeptID='" + SysInitial.LocalDeptID + "' group by cnvcCompanyID union select cnvcCompanyID,0 as TolCons from tbMebCompanyPrepay where cnvcCompanyID not in(select distinct cnvcCompanyID from tbConsItem where cnvcDeptID='" + SysInitial.LocalDeptID + "') order by cnvcCompanyID"; cmd = new SqlCommand(sql, conCenter, transCenter); daTmp = new SqlDataAdapter(cmd); daTmp.Fill(dstmp, "CenCompCons"); double TolFill = 0; double TolCons = 0; double TolCompPrepay = 0; for (i = 0; i < dstmp.Tables["CenCompFill"].Rows.Count; i++) { for (int j = 0; j < dstmp.Tables["CenCompCons"].Rows.Count; j++) { if (dstmp.Tables["CenCompFill"].Rows[i]["cnvcCompanyID"].ToString() == dstmp.Tables["CenCompCons"].Rows[j]["cnvcCompanyID"].ToString()) { TolFill = double.Parse(dstmp.Tables["CenCompFill"].Rows[i]["TolFill"].ToString()); TolCons = double.Parse(dstmp.Tables["CenCompCons"].Rows[j]["TolCons"].ToString()); TolCompPrepay = TolFill - TolCons; //修正余额为负更新余额 // if(TolCompPrepay>=0) // { //更新中心单位余额 sql = "update tbMebCompanyPrepay set cnnPrepayFee=" + TolCompPrepay.ToString() + " where cnvcCompanyID='" + dstmp.Tables["CenCompFill"].Rows[i]["cnvcCompanyID"].ToString() + "'"; cmd = new SqlCommand(sql, conCenter, transCenter); cmd.ExecuteNonQuery(); //更新本地单位余额 cmd = new SqlCommand(sql, con, transLocal); cmd.ExecuteNonQuery(); // } } } } #endregion this.listBox1.Items.Add("更新中心和本地数据:会员单位及余额信息更新成功..."); this.listBox1.Items.Add("--------------------------------"); this.listBox1.SelectedIndex = this.listBox1.Items.Count - 1; this.Refresh(); transLocal.Commit(); transCenter.Commit(); this.listBox1.Items.Add(""); this.listBox1.Items.Add("所有数据更新完成!"); this.listBox1.SelectedIndex = this.listBox1.Items.Count - 1; this.Refresh(); this.pictureBox1.Visible = false; this.sbtnClose.Enabled = true; Exception err = null; SysInitial.CreatDS(out err); if (err != null) { MessageBox.Show("系统参数刷新失败,将自动关闭,稍后请重新登录系统!", "系统提示", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); clog.WriteLine(err); Application.Exit(); } } catch (Exception err) { transLocal.Rollback(); transCenter.Rollback(); if (con.State == ConnectionState.Open) { con.Close(); } if (conCenter.State == ConnectionState.Open) { conCenter.Close(); } clog.WriteLine(err); this.listBox1.Items.Add("----------------------------------------------------------------------"); this.listBox1.Items.Add("更新过程中发生意外错误,更新未完全!"); this.listBox1.Items.Add("----------------------------------------------------------------------"); MessageBox.Show("更新过程中发生意外错误,更新未完全,请重新选择手工更新或重新启动系统!", "系统提示", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); this.sbtnClose.Enabled = true; return; } } } if (con.State == ConnectionState.Open) { con.Close(); } if (conCenter.State == ConnectionState.Open) { conCenter.Close(); } #endregion }