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
}
