private void ReportResult(string name, string idcardType, string cardID, string receipt, string result)
{
string outstring;
string strsql = "";
AccessOperator Accor = new AccessOperator();
//01身份证
MsgPutOut("数据回盘", "正在回盘数据，请耐心等待...");
string recode = TCPinterface.ReportResult(name, idcardType, cardID, receipt, result, out outstring);
if (recode.Equals("0000"))
{
strsql = "UPDATE ProductData SET [Status01]='4' WHERE [SHBZHM28]='" + cardID + "'";
ucFlowChart1.SetStepStatus(4, true);
MsgPutOut("数据回盘", "回盘成功");
}
else
{
strsql = "UPDATE ProductData SET [Status01]='3' WHERE [SHBZHM28]='" + cardID + "'";
ucFlowChart1.SetStepStatus(4, false);
MsgPutOut("数据回盘", "回盘失败:" + outstring);
}
int count = Accor.ExecuteNonQuery(strsql);
if (count <= 0)
{
MsgPutOut("数据回盘", "更新本地数据库失败");
}
}
private int SaveData(string idcard, string carddata, string idcardType, string receipt)
{
//Status01,CGBZ02,SBYY03,KSDM04,YHKH05,KSBM06,ATR07,ShJPCH08,SJPCH09,CSMC10,
//CJJGZKPCH11,CJJGMC12,XZQHDM13,XZQHMC14,DWBH15,DWMC16,YXBJ17,KDLB18,GFBB19,
//CSHJGBM20,FKRQ21,KYXQ22,KH23,SGDBBH24,ZKZL25,RYCCSLDBM26,XM27,SHBZHM28,YHBM29,XB30,
//MZ31,CSRQ32,XMKZ33,CSD34,BHKBZ35,CSKSBM36,SGRSBH37,ZZMM38,CZDZ39,HKXZ40,
//HKSZD41,XL42,GRSF43,ZJLX44,ZJHM45,ZJYXQ46,ZJZZRQ47,LXDH48,GJ49,ZY50,
//HYLB51,JHRXM52,JHRXB53,JHRZJLX54,JHRZJHM55,JHRDZ56,JHRLXDH57,JYHKH58,YL1,YL2
AccessOperator Accor = new AccessOperator();
string sql = "SELECT [SHBZHM28] FROM ProductData WHERE [SHBZHM28]='" + idcard + "'";
DataTable dtread = Accor.ExecuteDataTable(sql);
if (dtread.Rows.Count > 0)
{
return(0);
}
string tableFields = "Status01,CGBZ02,SBYY03,KSDM04,YHKH05,KSBM06,ATR07,ShJPCH08,SJPCH09,CSMC10,CJJGZKPCH11,CJJGMC12,XZQHDM13,XZQHMC14,DWBH15,DWMC16,YXBJ17,KDLB18,GFBB19,CSHJGBM20,FKRQ21,KYXQ22,KH23,SGDBBH24,ZKZL25,RYCCSLDBM26,XM27,SHBZHM28,YHBM29,XB30,MZ31,CSRQ32,XMKZ33,CSD34,BHKBZ35,CSKSBM36,SGRSBH37,ZZMM38,CZDZ39,HKXZ40,HKSZD41,XL42,GRSF43,ZJLX44,ZJHM45,ZJYXQ46,ZJZZRQ47,LXDH48,GJ49,ZY50,HYLB51,JHRXM52,JHRXB53,JHRZJLX54,JHRZJHM55,JHRDZ56,JHRLXDH57,JYHKH58,YL1,YL2,FullDATA59,BY60,BY61,BY62";
string[] tabledata = tableFields.Split(',');
string[] data = carddata.Split('|');
string insertvalue = "'1','','','','','','','" + carddata.Replace("|", "','") + "','" + carddata + "','" + idcardType + "','" + receipt + "',''";
string strsql = "insert into ProductData (" + tableFields + ") values (" + insertvalue + ")";
int count = Accor.ExecuteNonQuery(strsql);
int ret = count > 0 ? 0 : -1; //插入成功或失败
return(ret);
}
private void RefreshTree()
{
AccessOperator Accor = new AccessOperator();
tvCardInit.Nodes.Clear();
string strsql = "SELECT [Status01],[XM27],[SHBZHM28] FROM ProductData WHERE [Status01]='1' OR [Status01]='2' OR [Status01]='3'";
DataTable dtread = Accor.ExecuteDataTable(strsql);
TreeNode Root = tvCardInit.Nodes.Add(string.Format("个人补换卡[数量:{0}]", dtread.Rows.Count));
TreeNode Node = null;
foreach (DataRow dr in dtread.Rows)
{
Node = Root.Nodes.Add(dr["SHBZHM28"].ToString(), dr["SHBZHM28"] + "_" + dr["XM27"]);
if (dr["Status01"].ToString().Equals("2"))
{
Node.ForeColor = System.Drawing.Color.Red;
}
else if (dr["Status01"].ToString().Equals("3"))
{
Node.ForeColor = System.Drawing.Color.Blue;
}
}
tvCardInit.ExpandAll();
tvCardInit.SelectedNode = Root;
}
private void Export(string startTime, string endTime, string fileName, string filePath)
{
LogHelper.WriteLog(typeof(FormMain), "Export");
string strsql = string.Format("SELECT NAME AS 姓名,IDCARD AS 身份证号,CARDID AS 社保卡号,BANKCARDNO AS 银行卡号,DATESAVE AS 制卡日期时间, SWITCH(MARK = 4, '是', TRUE, '否') AS 是否回盘 FROM NEWPRODUCTDATA UNION ALL SELECT NAME AS 姓名,IDCARD AS 身份证号,CARDID AS 社保卡号,BANKCARDNO AS 银行卡号,DATESAVE AS 制卡日期时间, SWITCH(MARK = 4, '是', TRUE, '否') AS 是否回盘 FROM NEWPRODUCTDATAS WHERE MARK IN (3,4) AND DATESAVE >= #{0}# and DATESAVE < #{1}# ORDER BY 制卡日期时间 ASC", startTime, endTime);
LogHelper.WriteLog(typeof(FormMain), "Sql: " + strsql);
AccessOperator Accor = new AccessOperator();
System.Data.DataTable dtread = Accor.ExecuteDataTable(strsql);
exportDataTableToExcel(dtread, fileName, filePath);
}
public void btnSearchPersonInfo_Click(object sender, EventArgs e)
{
//查询制卡数据
if (String.Equals(txtIDNo.Text.Trim(), "身份证号") || String.Equals(tbName.Text.Trim(), "姓名"))
{
MessageBox.Show("【身份证号】【姓名】不能为空！");
return;
}
if (2 != cbChangeType.SelectedIndex && String.Equals(tbBankNo.Text.Trim(), "原银行卡号"))
{
MessageBox.Show("【原银行卡号】不能为空！");
return;
}
int ret;
string recode = "";
string receipt = "";
string carddata = "";
string photodata = "";
AccessOperator Accor = new AccessOperator();
MsgPutOut("查询数据", "正在查询数据，请耐心等待...");
//查询业务单据号
recode = TCPinterface.CheckCardFees(tbName.Text.Trim(), strCertificateType, txtIDNo.Text.Trim(), changeType, tbBankNo.Text.Trim(), out receipt);
if (!recode.Equals("0000"))
{
MsgPutOut("查询数据", "查询业务单据号失败:" + receipt);
return;
}
recode = TCPinterface.SearchPersonInfo(tbName.Text.Trim(), strCertificateType, txtIDNo.Text.Trim(), changeType, tbBankNo.Text.Trim(), receipt, out carddata, out photodata);
if (recode.Equals("0000"))
{
string[] data = carddata.Split('|');
ret = SaveData(data[20], carddata, strCertificateType, receipt);
if (ret < 0)
{
MsgPutOut("查询数据", "保存个人数据失败");
return;
}
SavePhoto(photodata, data[20] + "_" + data[19]);
RefreshTree();
MsgPutOut("查询数据", "查询成功");
}
else
{
MsgPutOut("查询数据", carddata);
}
}
private void SelectDataTable()
{
string strsql = string.Format("SELECT NAME AS 姓名,IDCARD AS 身份证号,CARDID AS 社保卡号,BANKCARDNO AS 银行卡号,DATESAVE AS 制卡日期时间, SWITCH(MARK = 4, '是', TRUE, '否') AS 是否回盘 FROM NEWPRODUCTDATA UNION ALL SELECT NAME AS 姓名,IDCARD AS 身份证号,CARDID AS 社保卡号,BANKCARDNO AS 银行卡号,DATESAVE AS 制卡日期时间, SWITCH(MARK = 4, '是', TRUE, '否') AS 是否回盘 FROM NEWPRODUCTDATAS WHERE MARK IN (3,4) AND DATESAVE >= #{0}# and DATESAVE < #{1}# ORDER BY 制卡日期时间 ASC", dateTP_Start.Value.ToString("yyyy-MM-dd"), Convert.ToDateTime(dateTP_End.Value).AddDays(1).ToString("yyyy-MM-dd"));
LogHelper.WriteLog(typeof(FormMain), "Sql: " + strsql);
AccessOperator Accor = new AccessOperator();
dtread = Accor.ExecuteDataTable(strsql);
dataGridView1.DataSource = dtread;
//FenYe(dtread);
label5.Text = dtread.Rows.Count.ToString() + "张";
}
public void btnReport_Click(object sender, EventArgs e)
{
AccessOperator Accor = new AccessOperator();
TreeNode Node = (TreeNode)tvCardInit.SelectedNode;
string strsql = "SELECT [Status01],[CGBZ02],[SBYY03],[KSDM04],[YHKH05],[KSBM06],[ATR07],[XM27],[SHBZHM28] FROM ProductData WHERE [SHBZHM28]='" + Node.Name + "' AND [Status01]='3'";
DataTable dtread = Accor.ExecuteDataTable(strsql);
string strResult = dtread.Rows[0]["CGBZ02"].ToString() + "|"
+ dtread.Rows[0]["SBYY03"].ToString() + "|"
+ dtread.Rows[0]["KSDM04"].ToString() + "|"
+ dtread.Rows[0]["YHKH05"].ToString() + "|"
+ dtread.Rows[0]["KSBM06"].ToString() + "|"
+ dtread.Rows[0]["ATR07"];
tvCardInit.Enabled = false;
ReportResult(dtread.Rows[0]["XM27"].ToString(), GlobalClass.idcardType, dtread.Rows[0]["SHBZHM28"].ToString(), GlobalClass.receipt, strResult);
RefreshTree();
tvCardInit.Enabled = true;
}
private void tvCardInit_AfterSelect(object sender, TreeViewEventArgs e)
{
AccessOperator Accor = new AccessOperator();
TreeNode Node = (TreeNode)tvCardInit.SelectedNode;
ucFuncButton1.SetButtonEnabled(3, false);
ucFuncButton1.SetButtonEnabled(4, false);
if (Node.Parent == null)
{
pcPrintView.Image = Properties.Resources.社保正面;
}
else
{
string strsql = "SELECT [Status01],[XM27],[SHBZHM28],[XB30],[SGRSBH37],[FullDATA59],[BY60],[BY61] FROM ProductData WHERE [SHBZHM28]='" + Node.Name + "'";
DataTable dtread = Accor.ExecuteDataTable(strsql);
if (dtread.Rows.Count > 0)
{
GlobalClass.status = dtread.Rows[0]["Status01"].ToString();
GlobalClass.name = dtread.Rows[0]["XM27"].ToString();
GlobalClass.idcard = dtread.Rows[0]["SHBZHM28"].ToString();
GlobalClass.sex = dtread.Rows[0]["XB30"].ToString();
GlobalClass.provNo = dtread.Rows[0]["SGRSBH37"].ToString();
GlobalClass.fulldata = dtread.Rows[0]["FullDATA59"].ToString();
GlobalClass.idcardType = dtread.Rows[0]["BY60"].ToString();
GlobalClass.receipt = dtread.Rows[0]["BY61"].ToString();
if (GlobalClass.status.Equals("1") || GlobalClass.status.Equals("2"))
{
ucFuncButton1.SetButtonEnabled(3, true);
}
else if (GlobalClass.status.Equals("3"))
{
ucFuncButton1.SetButtonEnabled(4, true);
}
PrintParameter pp = new PrintParameter();
PrintFormatView pfv = new PrintFormatView();
pfv.DrawFormat(pp);
pcPrintView.Image = pfv.image;
}
}
}
private void ProductCard(string name, string idcardType, string idNo, string receipt)
{
string ICdata = "";
string reportStr = "";
string strsql = "";
ICdata = TCPinterface.WriteICBaseData(name, idcardType, idNo, receipt) + "#" + GlobalClass.fulldata;
AccessOperator Accor = new AccessOperator();
PrintParameter pp = new PrintParameter();
tvCardInit.Enabled = false;
int ret = ProductExecute(pp, ICdata, out reportStr);
if (ret != 0)
{
MsgPutOut("制作卡片", "制卡失败。错误:" + ret);
strsql = "UPDATE ProductData SET [Status01]='2' WHERE [SHBZHM28]='" + idNo + "'";
int count = Accor.ExecuteNonQuery(strsql);
if (count <= 0)
{
MsgPutOut("制作卡片", "更新本地数据库失败");
}
}
else
{
MsgPutOut("制作卡片", "制卡成功！");
string[] strrep = reportStr.Split('|');
strsql = "UPDATE ProductData SET [Status01]='3',[CGBZ02]='1',[SBYY03]='',[KSDM04]='"
+ strrep[0] + "',[YHKH05]='" + strrep[1] + "',[KSBM06]='" + strrep[2] + "',[ATR07]='" + strrep[3] + "' WHERE [SHBZHM28]='" + idNo + "'";
int count = Accor.ExecuteNonQuery(strsql);
if (count <= 0)
{
MsgPutOut("制作卡片", "更新本地数据库失败");
}
ReportResult(name, idcardType, idNo, receipt, reportStr);
}
RefreshTree();
tvCardInit.Enabled = true;
}
