private void button_Qry_Click(object sender, EventArgs e) { MyFunc.WriteToDbLog("查询磅房交易汇总", "", "MSG", MyStart.giUserID); //MyFunc.WriteToDbLog("查询商品销售汇总", "", "MSG", MyStart.giUserID); string szBgn = string.Format("{0:yyyy-MM-dd HH:mm:ss}", dateTimePicker_Bgn.Value); string szEnd = string.Format("{0:yyyy-MM-dd HH:mm:ss}", dateTimePicker_End.Value); dataGridViewRst.Rows.Clear(); if (MyStart.gszMrktName.Length > 0) { mszRptTitle = MyStart.gszMrktName + "磅房交易表"; } else { mszRptTitle = "磅房交易表"; } if (radioButton_trade.Checked) { // mszRptTitle += "(磅房交易)"; MyFunc.GridInit(ref dataGridViewRst, mszTitle, mszTitleWidth, 15, miDefRows, true); string[] szX = mszTitle.Split(','); miCols = szX.Length; QryTrade(szBgn, szEnd); } if (radioButton_cash.Checked) { mszRptTitle += "(财务收银)"; MyFunc.GridInit(ref dataGridViewRst, mszTitleCash, mszTitleCashWidth, 15, miDefRows, true); string[] szX = mszTitleCash.Split(','); miCols = szX.Length; QryCash(szBgn, szEnd); } }
private void button_Qry_Click(object sender, EventArgs e) { /* * select * case when Z.USER_NAME is not null then Z.USER_NAME else '合计' end as '收银员', * case when Y_TYPE is not null then (if(Y_TYPE='CASH','取款',(if(Y_TYPE='ISSU','开卡',(if(Y_TYPE='ADD','充值','换卡')))))) else '小计' end as '操作类型', * case when Y_SubTYPE is not null then Y_SubTYPE else '小计' end as '操作子类型', * sum(A) as 卖方卡笔数,format(sum(B)/100,2) as '卖方金额(元)',sum(C) as 买方卡笔数,format(sum(D)/100,2) as '买方金额(元)', * sum(A+C) as 总笔数, format(sum(B+D)/100,2) as '总金额(元)' * from * ( select Y_ID,Y_TYPE,Y_SubTYPE, * case when A is not null then A else 0 end as A, * case when B is not null then B else 0 end as B, * case when C is not null then C else 0 end as C, * case when D is not null then D else 0 end as D * from * (select Oper_ID as X_ID,Oper_Type as X_TYPE,Oper_SubType as X_SubTYPE,count(Chg_Val) as A,sum(Chg_Val) as B from rec_firm where Oper_Type in('ADD','CASH','ISSU','CHG') and Oper_Time>'2019-1-19' * group by Oper_ID,Oper_Type,Oper_SubType) X * right join * (select Oper_ID as Y_ID,Oper_Type as Y_TYPE,Oper_SubType as Y_SubTYPE,count(Chg_Val) as C,sum(Chg_Val) as D from rec_user where Oper_Type in('ADD','CASH','ISSU','CHG') and Oper_Time>'2019-1-19' * group by Oper_ID,Oper_Type,Oper_SubType) Y * on X_ID=Y_ID and X_TYPE=Y_TYPE and X_SubTYPE=Y_SubTYPE * ) XX,sys_users Z * where XX.Y_ID=Z.USER_ID * group by Z.USER_NAME,XX.Y_TYPE,XX.Y_SubTYPE with rollup */ MyFunc.WriteToDbLog("查询收银汇总", "", "MSG", MyStart.giUserID); //MyFunc.WriteToDbLog("查询商品销售汇总", "", "MSG", MyStart.giUserID); string szBgn = string.Format("{0:yyyy-MM-dd HH:mm:ss}", dateTimePicker_Bgn.Value); string szEnd = string.Format("{0:yyyy-MM-dd HH:mm:ss}", dateTimePicker_End.Value); dataGridViewRst.Rows.Clear(); if (MyStart.gszMrktName.Length > 0) { mszRptTitle = MyStart.gszMrktName + "收银汇总表"; } else { mszRptTitle = "收银汇总表"; } //if (radioButton_trade.Checked) //{ // mszRptTitle += "(磅房交易)"; // MyFunc.GridInit(ref dataGridViewRst, mszTitle, mszTitleWidth, 15, miDefRows, true); // string[] szX = mszTitle.Split(','); // miCols = szX.Length; // QryTrade(szBgn, szEnd); //} if (radioButton_cash.Checked) { mszRptTitle += "(财务收银)"; MyFunc.GridInit(ref dataGridViewRst, mszTitleCash, mszTitleCashWidth, 15, miDefRows, true); string[] szX = mszTitleCash.Split(','); miCols = szX.Length; QryCash(szBgn, szEnd); } }
private void frm_Client_Qry_Load(object sender, EventArgs e) { this.Icon = new Icon(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.report1.ico")); button_Qry.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.preview.ico")); button_Rpt.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.report.ico")); button_Exit.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.exit01.ico")); checkBox_All.Checked = true; radioButton_card.Checked = true; textBox_Card.Text = ""; textBox_Cell.Text = ""; textBox_name.Text = ""; MyFunc.GridInit(ref dataGridView1, mszTitle, mszTitleWidth, 15, miDefRows, true); }
private void button_Qry_Click(object sender, EventArgs e) { MyFunc.WriteToDbLog("查询工作日志", "", "MSG", MyStart.giUserID); string szBgn = string.Format("{0:yyyy-MM-dd}", dateTimePicker_Bgn.Value) + " 00:00:00"; string szEnd = string.Format("{0:yyyy-MM-dd}", dateTimePicker_End.Value) + " 23:59:59"; dataGridViewRst.Rows.Clear(); MyFunc.GridInit(ref dataGridViewRst, mszTitle, mszTitleWidth, 15, miDefRows, true); try { DataSet ds = new DataSet(); string szSql = "select user_name,rec_time,log_rmrk,log_info from sys_log a,sys_users b " + "where a.LOG_USER = b.USER_ID and user_type>1 "; if (comboBox_User.SelectedIndex > 0) { string[] szX = comboBox_User.Text.Split('-'); szSql += " and a.LOG_USER = "******" and rec_time>='" + szBgn + "' and rec_time<='" + szEnd + "' order by a.LOG_USER,rec_time desc"; string szErr = ""; int iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst != 0) { MessageBox.Show("查询失败!", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); MyIniFile.WriteLog("查询操作日志", "SQL=" + szSql + ",Err=" + szErr); goto Eend; } DataTable dt = ds.Tables[0]; mDt = dt; int iNum = dt.Rows.Count; if (iNum == 0) { MessageBox.Show("没有数据", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); goto Eend; } int iCurNum = (iNum < miDefRows ? miDefRows : iNum); MyFunc.GridWriteDt(ref dataGridViewRst, ref dt, 0, miCols, ref iCurNum); miRows = iNum; mszRptDate = string.Format("{0:yyyy-MM-dd}", dateTimePicker_Bgn.Value) + " 至 " + string.Format("{0:yyyy-MM-dd}", dateTimePicker_End.Value); button_Rpt.Enabled = true; } catch (Exception ex) { MessageBox.Show("查询操作日志失败( " + ex.Message + " )", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } Eend: MyStart.oMyDb.Close(); }
private void frm_Firm_QryCard_Load(object sender, EventArgs e) { this.Icon = new Icon(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.idcard.ico")); //pictureBox1.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.chip.ico")); button_Card.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.idcard.ico")); button_Qry.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.preview.ico")); button_Exit.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.exit01.ico")); textBox_Card.Text = ""; textBox_time.Text = ""; textBox_stat.Text = ""; textBox_Firm.Text = ""; textBox_Person.Text = ""; textBox_Cell.Text = ""; textBox_CertID.Text = ""; textBoxOper.Text = ""; MyFunc.GridInit(ref dataGridView1, "发卡时间,卡号,卡类,卡片状态,档口联系人,联系电话,身份证,开卡人", "1,1,1,1,1,1,1,1", 15, 15, true); }
private void frm_Qry_Mkt_Load(object sender, EventArgs e) { this.Icon = new Icon(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.report2.ico")); button_Qry.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.preview.ico")); button_Rpt.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.report.ico")); button_Exit.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.exit01.ico")); groupBox2.Width = this.Width - 45; //groupBox2.Height = this.Height - groupBox2.Top - 50; dataGridViewRst.Width = groupBox2.Width - 20; //dataGridViewRst.Height = groupBox2.Height - 30; dateTimePicker_Bgn.CustomFormat = "yyyy-MM-dd HH:mm:ss"; dateTimePicker_Bgn.Format = DateTimePickerFormat.Custom; dateTimePicker_End.CustomFormat = "yyyy-MM-dd HH:mm:ss"; dateTimePicker_End.Format = DateTimePickerFormat.Custom; //dateTimePicker_End.Value = DateTime.Now; dateTimePicker_Bgn.Text = MyStart.gdtLogin.ToString("yyyy-MM-dd 08:00:00"); //DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd 08:00:00");//.AddMonths(-1); dateTimePicker_End.Text = MyStart.gdtLogin.AddDays(1).ToString("yyyy-MM-dd 07:59:59"); //DateTime.Now.ToString("yyyy-MM-dd 07:59:59"); DateTime Dt = DateTime.Now; if (Dt >= DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd 00:00:00")) && Dt < DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd 08:00:00"))) { dateTimePicker_Bgn.Text = Dt.AddDays(-1).ToString("yyyy-MM-dd 08:00:00"); //dateTimePicker_Bgn.MaxDate = DateTime.Parse(dateTimePicker_Bgn.Text); dateTimePicker_End.Text = Dt.ToString("yyyy-MM-dd 07:59:59"); //dateTimePicker_End.MaxDate = DateTime.Parse(dateTimePicker_End.Text); } else { dateTimePicker_Bgn.Text = Dt.ToString("yyyy-MM-dd 08:00:00"); //dateTimePicker_Bgn.MaxDate = DateTime.Parse(dateTimePicker_Bgn.Text); dateTimePicker_End.Text = Dt.AddDays(1).ToString("yyyy-MM-dd 07:59:59"); //dateTimePicker_End.MaxDate = DateTime.Parse(dateTimePicker_End.Text); } MyFunc.GridInit(ref dataGridViewRst, mszTitle, mszTitleWidth, 15, miDefRows, true); string[] szX = mszTitle.Split(','); miCols = szX.Length; }
private void frm_Firm_Qry_Load(object sender, EventArgs e) { this.Icon = new Icon(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.report1.ico")); button_Qry.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.preview.ico")); button_Rpt.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.report.ico")); button_Exit.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.exit01.ico")); DataSet ds = new DataSet(); string szErr = ""; int iStoreNum = 0;//卖方 string szSql = "select concat(store_id,'-',store_name) from base_store where STORE_STAT='USED' order by store_id"; int iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst == 0) { iStoreNum = ds.Tables[0].Rows.Count; if (iStoreNum > 0) { comboBox_Firm.Items.Add("0-所有卖方"); } for (int i = 0; i < iStoreNum; i++) { DataRow dr = ds.Tables[0].Rows[i]; comboBox_Firm.Items.Add(dr[0].ToString()); } } if (iStoreNum > 0) { comboBox_Firm.SelectedIndex = 0; } //MyFunc.GridInit(ref dataGridView_Total, "卖方,档口,发卡数量", "1,1,1", 15, 15, true); MyFunc.GridInit(ref dataGridView_Detail, mszTitle, mszTitleWidth, 15, miDefRows, true); miCols = mszTitle.Split(',').Length; MyStart.oMyDb.Close(); textBox_Card.Text = ""; textBox_Cell.Text = ""; textBox_name.Text = ""; radioButton_card.Checked = true; SelEnable(); }
private void frm_User_Log_Load(object sender, EventArgs e) { this.Icon = new Icon(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.report2.ico")); button_Qry.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.preview.ico")); button_Rpt.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.report.ico")); button_Exit.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.exit01.ico")); dateTimePicker_Bgn.Value = DateTime.Now.AddMonths(-1); dateTimePicker_End.Value = DateTime.Now; DataSet ds = new DataSet(); string szErr = ""; int iNum = 0;//商户 string szSql = "select concat(user_id,'-',user_name) from sys_users where /*user_type=2 and*/ user_stat='USED' order by user_id"; int iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst == 0) { iNum = ds.Tables[0].Rows.Count; if (iNum > 0) { comboBox_User.Items.Add("0-所有人员"); } for (int i = 0; i < iNum; i++) { DataRow dr = ds.Tables[0].Rows[i]; comboBox_User.Items.Add(dr[0].ToString()); } } if (iNum > 0) { comboBox_User.SelectedIndex = 0; } MyFunc.GridInit(ref dataGridViewRst, mszTitle, mszTitleWidth, 15, miDefRows, true); string[] szX = mszTitle.Split(','); miCols = szX.Length; MyStart.oMyDb.Close(); }
private void button_Quit_Click(object sender, EventArgs e) { button_Edit.Enabled = true; button_Save.Enabled = false; button_Quit.Enabled = false; button_Exit.Enabled = true; textBox_mrkt_name.Text = MyStart.gszMrktName; textBox_mrkt_addr.Text = MyStart.gszMrktAddr; textBox_mrkt_tel.Text = MyStart.gszMrktTel; textBox_Card_YTB.Text = MyStart.gszCardYtbFirst; textBox_Card_Firm.Text = MyStart.gszCardFirmFirst; textBox_FeeChgCard.Text = MyStart.giFeeChgCard.ToString("0.00"); //textBox_Pos_ID.Text = MyStart.gszPosID; textBox_Firm_ID.Text = MyStart.gszFirmID; if (MyStart.gszWeight == "斤") { radioButton_jin.Checked = true; } else { radioButton_kg.Checked = true; } dataGridViewTmn.ReadOnly = true; dataGridViewTmn.Rows.Clear(); MyFunc.GridInit(ref dataGridViewTmn, mszTitle, mszTitleWidth, 15, miDefRows, true); int iNum = mDt.Rows.Count; int iCurNum = (iNum < miDefRows ? miDefRows : iNum); MyFunc.GridWriteDt(ref dataGridViewTmn, ref mDt, 0, miCols, ref iCurNum); groupBox_para.Enabled = false; button_Exit.Select(); }
private void radioButton_cash_CheckedChanged(object sender, EventArgs e) { dataGridViewRst.Columns.Clear(); MyFunc.GridInit(ref dataGridViewRst, mszTitleCash, mszTitleCashWidth, 15, miDefRows, true); }
private void frm_User_day_Load(object sender, EventArgs e) { this.Icon = new Icon(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.report2.ico")); button_Qry.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.preview.ico")); button_Rpt.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.report.ico")); button_Exit.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.exit01.ico")); groupBox2.Width = this.Width - 50; groupBox2.Height = this.Height - groupBox2.Top - 50; dataGridViewRst.Width = groupBox2.Width - 20; dataGridViewRst.Height = groupBox2.Height - 30; dateTimePicker_Bgn.CustomFormat = "yyyy-MM-dd HH:mm:ss"; dateTimePicker_Bgn.Format = DateTimePickerFormat.Custom; dateTimePicker_End.CustomFormat = "yyyy-MM-dd HH:mm:ss"; dateTimePicker_End.Format = DateTimePickerFormat.Custom; //dateTimePicker_End.Value = DateTime.Now; dateTimePicker_Bgn.Text = MyStart.gdtLogin.ToString("yyyy-MM-dd 08:00:00"); //DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd 08:00:00");//.AddMonths(-1); dateTimePicker_End.Text = MyStart.gdtLogin.AddDays(1).ToString("yyyy-MM-dd 07:59:59"); //DateTime.Now.ToString("yyyy-MM-dd 07:59:59"); DateTime Dt = DateTime.Now; if (Dt >= DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd 00:00:00")) && Dt < DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd 08:00:00"))) { dateTimePicker_Bgn.Text = Dt.AddDays(-1).ToString("yyyy-MM-dd 08:00:00"); //dateTimePicker_Bgn.MaxDate = DateTime.Parse(dateTimePicker_Bgn.Text); dateTimePicker_End.Text = Dt.ToString("yyyy-MM-dd 07:59:59"); //dateTimePicker_End.MaxDate = DateTime.Parse(dateTimePicker_End.Text); } else { dateTimePicker_Bgn.Text = Dt.ToString("yyyy-MM-dd 08:00:00"); //dateTimePicker_Bgn.MaxDate = DateTime.Parse(dateTimePicker_Bgn.Text); dateTimePicker_End.Text = Dt.AddDays(1).ToString("yyyy-MM-dd 07:59:59"); //dateTimePicker_End.MaxDate = DateTime.Parse(dateTimePicker_End.Text); } radioButton_trade.Checked = true; DataSet ds = new DataSet(); string szErr = ""; int iDptNum = 0;// string szSql = "select USER_DPT from sys_users group by USER_DPT "; int iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst == 0) { iDptNum = ds.Tables[0].Rows.Count; if (iDptNum > 0) { comboBox_dpt.Items.Add("所有部门"); } for (int i = 0; i < iDptNum; i++) { DataRow dr = ds.Tables[0].Rows[i]; comboBox_dpt.Items.Add(dr[0].ToString()); } } if (iDptNum > 0) { comboBox_dpt.SelectedIndex = 0; DispUser(""); } radioButton_trade.Checked = true; MyFunc.GridInit(ref dataGridViewRst, mszTitle, mszTitleWidth, 15, miDefRows, true); string[] szX = mszTitle.Split(','); miCols = szX.Length; MyStart.oMyDb.Close(); }
private void button_temp_Click(object sender, EventArgs e) { MyFunc.WriteToDbLog("旧卡查询销售明细", "", "MSG", MyStart.giUserID); dataGridViewRst.Rows.Clear(); MyFunc.GridInit(ref dataGridViewRst, mszTitle, mszTitleWidth, 15, miDefRows, true); if (MyStart.gszMrktName.Length > 0) { mszRptTitle = MyStart.gszMrktName + "旧卡销售明细表"; } else { mszRptTitle = "旧卡销售明细表"; } DataSet ds = new DataSet(); //查找卖方旧卡 string szSql = "select substr(log_info,4,16) from sys_log where LOG_RMRK like '%卖方%换卡%' "; szSql += " and rec_time>='" + string.Format("{0:yyyy-MM-dd HH:mm:ss}", dateTimePicker_Bgn.Value) + "' "; // + " 00:00:00' "; szSql += " and rec_time<='" + string.Format("{0:yyyy-MM-dd HH:mm:ss}", dateTimePicker_End.Value) + "' "; // + " 23:59:59' "; string szFirmCard = ""; int iFirmNum = 0; try { string szErr = ""; int iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst != 0) { MessageBox.Show("查询卖方换卡记录失败!", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); MyIniFile.WriteLog("查询卖方换卡记录", "SQL=" + szSql + ",Err=" + szErr); goto Eend; } DataTable dt = ds.Tables[0]; mDt = dt; iFirmNum = dt.Rows.Count; if (iFirmNum == 0) { MessageBox.Show("该时间段没有卖方换卡记录", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); //goto Eend; } else { for (int i = 0; i < iFirmNum; i++) { DataRow dr = dt.Rows[i]; szFirmCard += "'" + dr[0].ToString() + "',"; } szFirmCard = szFirmCard.Substring(0, szFirmCard.Length - 1); MessageBox.Show("该时间段有" + iFirmNum + "笔卖方换卡记录", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (Exception ex) { MessageBox.Show("查询卖方换卡失败( " + ex.Message + " )", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } //查找买方旧卡 szSql = "select substr(log_info,4,16) from sys_log where LOG_RMRK like '%买方%换卡%' "; szSql += " and rec_time>='" + string.Format("{0:yyyy-MM-dd HH:mm:ss}", dateTimePicker_Bgn.Value) + "' "; // + " 00:00:00' "; szSql += " and rec_time<='" + string.Format("{0:yyyy-MM-dd HH:mm:ss}", dateTimePicker_End.Value) + "' "; // + " 23:59:59' "; string szUserCard = ""; int iUserNum = 0; try { string szErr = ""; int iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst != 0) { MessageBox.Show("查询买方换卡记录失败!", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); MyIniFile.WriteLog("查询买方换卡记录", "SQL=" + szSql + ",Err=" + szErr); goto Eend; } DataTable dt = ds.Tables[0]; mDt = dt; iUserNum = dt.Rows.Count; if (iUserNum == 0) { MessageBox.Show("该时间段没有买方换卡记录", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); if (iFirmNum == 0 && iUserNum == 0) { goto Eend; } } else { for (int i = 0; i < iUserNum; i++) { DataRow dr = dt.Rows[i]; szUserCard += "'" + dr[0].ToString() + "',"; } szUserCard = szUserCard.Substring(0, szUserCard.Length - 1); MessageBox.Show("该时间段有" + iUserNum + "笔买方换卡记录", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (Exception ex) { MessageBox.Show("查询买方换卡失败( " + ex.Message + " )", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } szSql = "select JY_ID,jy_tim,substring(a.store_card,2),user_card,prod_name,format(prod_up / 100, 2)," + "format(prod_w / 1000, 3),format(prod_nw / 1000, 3)," + "format((prod_w - prod_nw) / 1000, 3),format(prod_amount / 100, 2), " + "concat(format(MKT_BUYER_FEE_RATE/10,1),'%'),format(MKT_BUYER_FEE/ 100, 2), " + "concat(format(MKT_SELLER_FEE_RATE/10,1),'%'),format(MKT_SELLER_FEE/ 100, 2)," + "concat(format(YTB_BUYER_FEE_RATE/10,1),'%'),format(YTB_BUYER_FEE/ 100, 2), " + "concat(format(YTB_SELLER_FEE_RATE/10,1),'%'),format(YTB_SELLER_FEE/ 100, 2)," /*+ "concat(format((YTB_BUYER_FEE_RATE+YTB_SELLER_FEE_RATE)/10,1),'%')," + "format((YTB_BUYER_FEE + YTB_SELLER_FEE)/ 100, 2), "*/ + "format((prod_amount + MKT_BUYER_FEE + MKT_SELLER_FEE + YTB_BUYER_FEE + YTB_SELLER_FEE)/ 100, 2)"; string szTemp = ""; if (radioButton_SysId.Checked) { szSql += " from rec_trade a, base_prod b where a.CANCEL_MARK='N' and a.PROD_ID = b.PROD_ID " + " and JY_ID=" + textBox_SysId.Text.Trim(); mszSumSql = ""; } if (radioButton_Other.Checked) { mszSumSql = "select '合计','-','-','-','-','-'," + "format(sum(prod_w) / 1000, 3),format(sum(prod_nw) / 1000, 3)," + "format(sum(prod_w - prod_nw) / 1000, 3),format(sum(prod_amount) / 100, 2), " + "'-',format(sum(MKT_BUYER_FEE)/ 100, 2), " + "'-',format(sum(MKT_SELLER_FEE)/ 100, 2)," + "'-',format(sum(YTB_BUYER_FEE)/ 100, 2), " + "'-',format(sum(YTB_SELLER_FEE)/ 100, 2), " + "format(sum(prod_amount + MKT_BUYER_FEE + MKT_SELLER_FEE + YTB_BUYER_FEE + YTB_SELLER_FEE)/ 100, 2)"; //szTemp = " from rec_trade a, base_prod b,mng_card c,base_stall d,base_store e,sys_msg f " // + " where a.CANCEL_MARK='N' and a.STORE_CARD=c.STORE_CARD and c.STALL_ID=d.STALL_ID and d.STORE_ID=e.STORE_ID" // + " and a.PROD_ID = b.PROD_ID and a.MSG_ID=f.MSG_ID"; szTemp = " from rec_trade a, base_prod b,mng_card c,sys_msg f " + " where a.CANCEL_MARK='N' and a.STORE_CARD=c.STORE_CARD and c.CARD_TYPE<3 " + " and a.PROD_ID = b.PROD_ID and a.MSG_ID=f.MSG_ID"; string[] szItem; if (comboBox_Firm.SelectedIndex > 0) { szItem = comboBox_Firm.Text.Split('-'); szTemp += " and c.STORE_ID = " + szItem[0]; //mszRptTitle = "销售明细表(卖方:"+ szItem[1] + ")"; } //else // mszRptTitle = "销售明细表(所有卖方)"; if (comboBox_User.SelectedIndex > 0) { szItem = comboBox_User.Text.Split('-'); szTemp += " and USER_CARD = " + szItem[0]; } if (comboBox_Prod.SelectedIndex > 0) { szItem = comboBox_Prod.Text.Split('-'); szTemp += " and a.PROD_ID = " + szItem[0]; } if (comboBox_POS.SelectedIndex > 0) { szItem = comboBox_POS.Text.Split('-'); szTemp += " and f.POS_ID = '" + szItem[0] + "' "; } if (iUserNum > 0) { if (iFirmNum > 0) { szTemp += " and (USER_CARD in (" + szUserCard + ") or a.STORE_CARD in (" + szFirmCard + "))";// + } else { szTemp += " and USER_CARD in (" + szUserCard + ") ";// + } } else { if (iFirmNum > 0) { szTemp += " and a.STORE_CARD in (" + szFirmCard + ")";// + } else { ; } } //"'3352300066109473','3352300066103096','3352300066102296','3352300066106826','3352300066106388')"; szTemp += " and jy_tim>='" + string.Format("{0:yyyy-MM-dd HH:mm:ss}", dateTimePicker_Bgn.Value) + "' "; // + " 00:00:00' "; szTemp += " and jy_tim<='" + string.Format("{0:yyyy-MM-dd HH:mm:ss}", dateTimePicker_End.Value) + "' "; // + " 23:59:59' "; } szSql = szSql + szTemp + " order by jy_tim desc"; if (mszSumSql.Length > 0) { mszSumSql += szTemp; } try { string szErr = ""; int iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst != 0) { MessageBox.Show("查询失败!", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); MyIniFile.WriteLog("查询商品交易明细信息", "SQL=" + szSql + ",Err=" + szErr); goto Eend; } DataTable dt = ds.Tables[0]; mDt = dt; int iNum = dt.Rows.Count; if (iNum == 0) { MessageBox.Show("没有数据", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); goto Eend; } int iCurNum = (iNum < miDefRows ? miDefRows : iNum); MyFunc.GridWriteDt(ref dataGridViewRst, ref dt, 0, miCols, ref iCurNum); miRows = iNum; mszRptDate = string.Format("{0:yyyy-MM-dd}", dateTimePicker_Bgn.Value) + " 至 " + string.Format("{0:yyyy-MM-dd}", dateTimePicker_End.Value); button_Rpt.Enabled = true; } catch (Exception ex) { MessageBox.Show("查询失败( " + ex.Message + " )", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } Eend: MyStart.oMyDb.Close(); }
private void button_Qry_Click(object sender, EventArgs e) { string szCard = textBox_Card.Text; if (szCard.Length == 15) { szCard = MyStart.gszCardFirmFirst + textBox_Card.Text; } MyFunc.WriteToDbLog("刷卡查询销售明细", "", "MSG", MyStart.giUserID); dataGridViewRst.Rows.Clear(); MyFunc.GridInit(ref dataGridViewRst, mszTitle, mszTitleWidth, 15, miDefRows, true); if (MyStart.gszMrktName.Length > 0) { mszRptTitle = MyStart.gszMrktName + "销售明细表"; } else { mszRptTitle = "销售明细表"; } DataSet ds = new DataSet(); string szWhere = ""; string szDetailSql = "select a.sys_id,jy_tim,substring(a.store_card,2),user_card,prod_name,format(prod_up / 100, 2)," + "format(prod_w / 1000, 3),format(prod_nw / 1000, 3)," + "format((prod_w - prod_nw) / 1000, 3),format(prod_amount / 100, 2), " + "concat(format(MKT_BUYER_FEE_RATE/10,1),'%'),format(MKT_BUYER_FEE/ 100, 2), " + "concat(format(MKT_SELLER_FEE_RATE/10,1),'%'),format(MKT_SELLER_FEE/ 100, 2)," + "concat(format(YTB_BUYER_FEE_RATE/10,1),'%'),format(YTB_BUYER_FEE/ 100, 2), " + "concat(format(YTB_SELLER_FEE_RATE/10,1),'%'),format(YTB_SELLER_FEE/ 100, 2)," /*+ "concat(format((YTB_BUYER_FEE_RATE+YTB_SELLER_FEE_RATE)/10,1),'%')," + "format((YTB_BUYER_FEE + YTB_SELLER_FEE)/ 100, 2), "*/ + "format((prod_amount + MKT_BUYER_FEE + MKT_SELLER_FEE + YTB_BUYER_FEE + YTB_SELLER_FEE)/ 100, 2)"; mszSumSql = "select '合计','-','-','-','-','-'," + "format(sum(prod_w) / 1000, 3),format(sum(prod_nw) / 1000, 3)," + "format(sum(prod_w - prod_nw) / 1000, 3),format(sum(prod_amount) / 100, 2), " + "'-',format(sum(MKT_BUYER_FEE)/ 100, 2), " + "'-',format(sum(MKT_SELLER_FEE)/ 100, 2)," + "'-',format(sum(YTB_BUYER_FEE)/ 100, 2), " + "'-',format(sum(YTB_SELLER_FEE)/ 100, 2), " + "format(sum(prod_amount + MKT_BUYER_FEE + MKT_SELLER_FEE + YTB_BUYER_FEE + YTB_SELLER_FEE)/ 100, 2)"; szWhere = " from rec_trade a, base_prod b,mng_card c,base_store e,sys_msg f " + " where a.CANCEL_MARK='N' and a.STORE_CARD=c.STORE_CARD and c.STORE_ID=e.STORE_ID" + " and a.PROD_ID = b.PROD_ID and b.PROD_LEVEL=1 and a.MSG_ID=f.MSG_ID"; szWhere += " and jy_tim>='" + string.Format("{0:yyyy-MM-dd HH:mm:ss}", dateTimePicker_Bgn.Value) + "' "; // + " 00:00:00' "; szWhere += " and jy_tim<='" + string.Format("{0:yyyy-MM-dd HH:mm:ss}", dateTimePicker_End.Value) + "' "; // + " 23:59:59' "; //szWhere += " and jy_tim>='" + string.Format("{0:yyyy-MM-dd}", dateTimePicker_Bgn.Value) + " 00:00:00' "; //szWhere += " and jy_tim<='" + string.Format("{0:yyyy-MM-dd}", dateTimePicker_End.Value) + " 23:59:59' "; //验卡 string szSql = "select user_card from base_ucard where user_card='" + szCard + "'"; string szErr = ""; try { int iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst != 0) { MessageBox.Show("卡片查询失败,请换卡", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); MyIniFile.WriteLog("刷卡查询商品交易明细信息", "SQL=" + szSql + ",Err=" + szErr); goto Eend; } int iNum = ds.Tables[0].Rows.Count; if (iNum > 0)//买方卡 { if (DialogResult.No == MessageBox.Show("是否查询买方卡" + szCard + "的交易明细?", "操作提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question)) { goto Eend; } szWhere += " and a.user_card='" + szCard + "' "; } else { szSql = "select a.STORE_ID,CARD_TYPE,STORE_NAME from mng_card a,base_store b " + "where a.STORE_ID=b.STORE_ID and STORE_CARD = '" + szCard + "'"; iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst != 0) { MessageBox.Show("卡片查询失败,请换卡", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); MyIniFile.WriteLog("刷卡查询商品交易明细信息", "SQL=" + szSql + ",Err=" + szErr); goto Eend; } iNum = ds.Tables[0].Rows.Count; if (iNum == 0) { MessageBox.Show("系统中无此卡号,请换卡", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); MyIniFile.WriteLog("刷卡查询商品交易明细信息", "SQL=" + szSql + ",Err=系统中无此卡号"); goto Eend; } if (iNum > 1) { MessageBox.Show("系统中卡号重复记录,请换卡", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); MyIniFile.WriteLog("刷卡查询商品交易明细信息", "SQL=" + szSql + ",Err=系统中卡号重复记录"); goto Eend; } DataRow dr = ds.Tables[0].Rows[0]; int iFirmID = Convert.ToInt16(dr[0]); int iCardType = Convert.ToInt16(dr[1]); string szFirmName = dr[2].ToString(); if (iCardType == 2)//副卡 { if (DialogResult.No == MessageBox.Show("是否查询卖方副卡" + textBox_Card.Text + "的交易明细?", "操作提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question)) { goto Eend; } szWhere += " and a.STORE_CARD='" + szCard + "' "; } else { szSql = "select STORE_CARD from mng_card where STORE_ID=" + iFirmID + " and CARD_TYPE<3"; iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst != 0) { MessageBox.Show("查询卖方副卡失败!", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); MyIniFile.WriteLog("查询卖方副卡信息", "SQL=" + szSql + ",Err=" + szErr); goto Eend; } iNum = ds.Tables[0].Rows.Count; szCard = ""; if (iNum > 0) { if (DialogResult.No == MessageBox.Show("是否查询卖方(" + szFirmName + ")的交易明细?", "操作提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question)) { goto Eend; } szCard = ""; for (int i = 0; i < iNum; i++) { dr = ds.Tables[0].Rows[i]; szCard += "'" + dr[0].ToString() + "'"; if ((i + 1) < iNum) { szCard += ","; } } } szWhere += " and a.STORE_CARD in (" + szCard + ") "; } } szDetailSql = szDetailSql + szWhere + " order by jy_tim desc"; if (mszSumSql.Length > 0) { mszSumSql += szWhere; } iRst = MyStart.oMyDb.ReadData(szDetailSql, "tableA", ref ds, ref szErr); if (iRst != 0) { MessageBox.Show("查询失败!", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); MyIniFile.WriteLog("查询商品交易明细信息", "SQL=" + szDetailSql + ",Err=" + szErr); goto Eend; } DataTable dt = ds.Tables[0]; mDt = dt; iNum = dt.Rows.Count; if (iNum == 0) { MessageBox.Show("没有数据", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); goto Eend; } int iCurNum = (iNum < miDefRows ? miDefRows : iNum); MyFunc.GridWriteDt(ref dataGridViewRst, ref dt, 0, miCols, ref iCurNum); miRows = iNum; mszRptDate = string.Format("{0:yyyy-MM-dd}", dateTimePicker_Bgn.Value) + " 至 " + string.Format("{0:yyyy-MM-dd}", dateTimePicker_End.Value); button_Rpt.Enabled = true; } catch (Exception ex) { MessageBox.Show("查询失败( " + ex.Message + " )", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } Eend: MyStart.oMyDb.Close(); }
private void frm_Rpt_Minus_Load(object sender, EventArgs e) { this.Icon = new Icon(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.report2.ico")); button_Qry.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.preview.ico")); button_Rpt.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.report.ico")); button_Exit.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.exit01.ico")); groupBox2.Width = this.Width - 50; groupBox2.Height = this.Height - groupBox2.Top - 50; dataGridViewRst.Width = groupBox2.Width - 20; dataGridViewRst.Height = groupBox2.Height - 30; dateTimePicker_Bgn.CustomFormat = "yyyy-MM-dd HH:mm:ss"; dateTimePicker_Bgn.Format = DateTimePickerFormat.Custom; dateTimePicker_End.CustomFormat = "yyyy-MM-dd HH:mm:ss"; dateTimePicker_End.Format = DateTimePickerFormat.Custom; //dateTimePicker_End.Value = DateTime.Now; //dateTimePicker_Bgn.Text = MyStart.gdtLogin.ToString("yyyy-MM-dd 08:00:00");//DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd 08:00:00");//.AddMonths(-1); //dateTimePicker_End.Text = MyStart.gdtLogin.AddDays(1).ToString("yyyy-MM-dd 07:59:59");//DateTime.Now.ToString("yyyy-MM-dd 07:59:59"); DateTime Dt = DateTime.Now; if (Dt >= DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd 00:00:00")) && Dt < DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd 08:00:00"))) { dateTimePicker_Bgn.Text = Dt.AddDays(-1).ToString("yyyy-MM-dd 08:00:00"); //dateTimePicker_Bgn.MaxDate = DateTime.Parse(dateTimePicker_Bgn.Text); dateTimePicker_End.Text = Dt.ToString("yyyy-MM-dd 07:59:59"); //dateTimePicker_End.MaxDate = DateTime.Parse(dateTimePicker_End.Text); } else { dateTimePicker_Bgn.Text = Dt.ToString("yyyy-MM-dd 08:00:00"); //dateTimePicker_Bgn.MaxDate = DateTime.Parse(dateTimePicker_Bgn.Text); dateTimePicker_End.Text = Dt.AddDays(1).ToString("yyyy-MM-dd 07:59:59"); //dateTimePicker_End.MaxDate = DateTime.Parse(dateTimePicker_End.Text); } comboBox_Type.Items.Clear(); string szSql = "select sub_type from base_value where type=2 order by id"; string szErr = ""; DataSet ds = new DataSet(); int iNum = 0; int iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst == 0) { iNum = ds.Tables[0].Rows.Count; if (iNum > 0) { comboBox_Type.Items.Add("所有"); for (int i = 0; i < iNum; i++) { DataRow dr = ds.Tables[0].Rows[i]; comboBox_Type.Items.Add(dr[0].ToString()); } comboBox_Type.SelectedIndex = 0; } } szSql = "select concat(user_id,'-',user_name) from sys_users where USER_DPT='财务' and user_stat='USED' order by user_id"; szErr = ""; iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst == 0) { iNum = ds.Tables[0].Rows.Count; if (iNum > 0) { comboBox_POS.Items.Add("0-所有收银员"); } for (int i = 0; i < iNum; i++) { DataRow dr = ds.Tables[0].Rows[i]; comboBox_POS.Items.Add(dr[0].ToString()); } } if (iNum > 0) { comboBox_POS.SelectedIndex = 0; } textBox_buyer.Text = ""; radioButton_buyer.Checked = true; MyFunc.GridInit(ref dataGridViewRst, mszTitle, mszTitleWidth, 15, miDefRows, true); string[] szX = mszTitle.Split(','); miCols = szX.Length; MyStart.oMyDb.Close(); }
private void button_Qry_Click(object sender, EventArgs e) { MyFunc.WriteToDbLog("查询卖方销售汇总", "", "MSG", MyStart.giUserID); string szBgn = string.Format("{0:yyyy-MM-dd HH:mm:ss}", dateTimePicker_Bgn.Value); string szEnd = string.Format("{0:yyyy-MM-dd HH:mm:ss}", dateTimePicker_End.Value); dataGridViewRst.Rows.Clear(); MyFunc.GridInit(ref dataGridViewRst, mszTitle, mszTitleWidth, 15, miDefRows, true); if (MyStart.gszMrktName.Length > 0) { mszRptTitle = MyStart.gszMrktName + "卖方销售汇总表"; } else { mszRptTitle = "卖方销售汇总表"; } string szDateCondition = "jy_tim>='" + szBgn + "' and jy_tim<='" + szEnd + "'"; DataSet ds = new DataSet(); int iFirmNum = comboBox_Firm.Items.Count - 1; string[] szFirmID = new string[iFirmNum]; string[] szFirmName = new string[iFirmNum]; string[] szFirmCard = new string[iFirmNum]; int iBgnRow = 0; try { if (radioButton_Sel.Checked) { if (comboBox_Firm.SelectedIndex == 0)//all { //2)get STORE_CARD for (int i = 0; i < iFirmNum; i++) { string[] szItem = comboBox_Firm.Items[i + 1].ToString().Split('-'); szFirmID[i] = szItem[0]; szFirmName[i] = szItem[1]; szFirmCard[i] = Get_STORE_CARD(szFirmID[i], ""); //3)get data if (szFirmCard[i].Trim().Length > 0) { WriteDataToGrid(szFirmName[i], szFirmCard[i], szDateCondition, ref iBgnRow); } } } else//only one firm { //2)get STORE_CARD string[] szItem = comboBox_Firm.Items[comboBox_Firm.SelectedIndex].ToString().Split('-'); szFirmID[0] = szItem[0]; szFirmName[0] = szItem[1]; szFirmCard[0] = Get_STORE_CARD(szFirmID[0], ""); //3)get data if (szFirmCard[0].Trim().Length > 0) { WriteDataToGrid(szFirmName[0], szFirmCard[0], szDateCondition, ref iBgnRow); } } } if (radioButton_Input.Checked) { string szCard = textBox_Card.Text; if (szCard.Length == 15) { szCard = MyStart.gszCardFirmFirst + textBox_Card.Text; } //副卡,只查一张卡的交易 //第一副卡、结算卡查卖方名下所有卡交易 szFirmID[0] = szCard; szFirmCard[0] = szFirmID[0]; //check card and get name string szSql = "select a.STORE_ID,CARD_TYPE,STORE_NAME from mng_card a,base_store b " + "where a.STORE_ID = b.STORE_ID and STORE_CARD = '" + szCard + "'"; string szErr = ""; int iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst != 0) { MessageBox.Show("查询卖方卡失败!", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); MyIniFile.WriteLog("查询卖方卡信息", "SQL=" + szSql + ",Err=" + szErr); goto Eend; } int iNum = ds.Tables[0].Rows.Count; if (iNum == 0) { MessageBox.Show("系统里无此卖方卡,请换卡!", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); MyIniFile.WriteLog("查询卖方卡信息", "SQL=" + szSql + ",Err=系统里无此卖方卡"); goto Eend; } if (iNum > 1) { MessageBox.Show("重复发卡,请换卡!", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); MyIniFile.WriteLog("查询卖方卡信息", "SQL=" + szSql + ",Err=重复发卡"); goto Eend; } DataRow dr = ds.Tables[0].Rows[0]; int iFirmID = Convert.ToInt16(dr[0]); int iCardType = Convert.ToInt16(dr[1]); szFirmName[0] = dr[2].ToString(); if (iCardType != 2)//第一副卡、结算卡 { szFirmCard[0] = Get_STORE_CARD(iFirmID.ToString(), ""); } //3)get data WriteDataToGrid(szFirmName[0], szFirmCard[0], szDateCondition, ref iBgnRow); } if (radioButton_name.Checked) { //check card and get name string szSql = "select distinct(STORE_ID),STORE_PERSON from mng_card where STORE_PERSON like '%" + textBox_name.Text.Trim() + "%' order by store_ID"; string szErr = ""; int iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst != 0) { MessageBox.Show("查询卖方卡失败!", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); MyIniFile.WriteLog("查询卖方卡信息", "SQL=" + szSql + ",Err=" + szErr); goto Eend; } iFirmNum = ds.Tables[0].Rows.Count; if (iFirmNum == 0) { MessageBox.Show("系统里无此卖方卡,请换卡!", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); MyIniFile.WriteLog("查询卖方卡信息", "SQL=" + szSql + ",Err=系统里无此卖方卡"); goto Eend; } //3)get data for (int i = 0; i < iFirmNum; i++) { DataRow dr = ds.Tables[0].Rows[i]; szFirmID[i] = dr[0].ToString(); szFirmName[i] = dr[1].ToString(); szFirmCard[i] = Get_STORE_CARD(szFirmID[i], ""); //3)get data if (szFirmCard[i].Trim().Length > 0) { WriteDataToGrid(szFirmName[i], szFirmCard[i], szDateCondition, ref iBgnRow); } } } if (iBgnRow == 0) { MessageBox.Show("没有数据", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); goto Eend; } miRows = iBgnRow; mszRptDate = szBgn + " 至 " + szEnd; button_Rpt.Enabled = true; } catch (Exception ex) { MessageBox.Show("查询失败( " + ex.Message + " )", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } Eend: MyStart.oMyDb.Close(); }
private void button_Qry_Click(object sender, EventArgs e) { string szCard = textBox_Card.Text; if (szCard.Length == 15) { szCard = MyStart.gszCardFirmFirst + textBox_Card.Text; } DataSet ds = new DataSet(); string szErr = ""; string szSql = "select a.ADD_DT,CARD_STAT,a.STORE_PERSON,a.user_tel,a.cert_id,CARD_TYPE,a.STORE_ID,STORE_NAME,c.USER_NAME " + "from mng_card a,base_store b, sys_users c where a.ADD_ID=c.USER_ID " + "and a.STORE_ID=b.STORE_ID and STORE_CARD='" + szCard + "'"; try { int iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst != 0) { MessageBox.Show("卖方卡查询失败,请换卡", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); textBox_time.Text = ""; textBox_stat.Text = ""; textBox_Firm.Text = ""; textBox_Person.Text = ""; textBox_Cell.Text = ""; textBox_CertID.Text = ""; textBoxOper.Text = ""; dataGridView1.Rows.Clear(); MyFunc.GridInit(ref dataGridView1, "发卡时间,卡号,卡类,卡片状态,档口联系人,联系电话,身份证,开卡人", "1,1,1,1,1,1,1,1", 15, 15, true); goto Eend; } int iNum = ds.Tables[0].Rows.Count; if (iNum == 0) { MessageBox.Show("系统无此卖方卡信息,请换卡", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); textBox_time.Text = ""; textBox_stat.Text = ""; textBox_Firm.Text = ""; textBox_Person.Text = ""; textBox_Cell.Text = ""; textBox_CertID.Text = ""; textBoxOper.Text = ""; dataGridView1.Rows.Clear(); MyFunc.GridInit(ref dataGridView1, "发卡时间,卡号,卡类,卡片状态,档口联系人,联系电话,身份证,开卡人", "1,1,1,1,1,1,1,1", 15, 15, true); goto Eend; } DataRow dr = ds.Tables[0].Rows[0]; textBox_time.Text = dr[0].ToString(); switch (dr[1].ToString().ToUpper()) { case "BGN": textBox_stat.Text = "正常"; break; case "LOST": textBox_stat.Text = "挂失"; break; default: textBox_stat.Text = "无效"; break; } textBox_Person.Text = dr[2].ToString(); textBox_Cell.Text = dr[3].ToString(); textBox_CertID.Text = dr[4].ToString(); switch (Convert.ToInt16(dr[5])) { case 1: textBox_Type.Text = "第一副卡"; break; case 2: textBox_Type.Text = "副卡"; break; default: textBox_Type.Text = "结算卡"; break; } textBox_Firm.Text = dr[7].ToString(); textBoxOper.Text = dr[8].ToString(); //ds = null; int iFirmID = Convert.ToInt16(dr[6]); szSql = "SELECT b.ADD_DT as 发卡时间,STORE_CARD as 卡号," + "if (CARD_TYPE = 1,'第一副卡','结算卡') as 卡类," + "if (CARD_STAT = 'BGN','正常',if (CARD_STAT = 'STOP','无效','挂失')) as 状态," + "STORE_PERSON as 联系人,b.USER_TEL as 联系电话,b.cert_id as 身份证,c.USER_NAME as 开卡人 " + "FROM mng_card b, sys_users c where b.ADD_ID=c.USER_ID and b.CARD_TYPE<>2 and b.STORE_ID = " + iFirmID + " UNION " + "SELECT b.ADD_DT as 发卡时间,STORE_CARD as 卡号," + "'副卡' as 卡类," + "if (CARD_STAT = 'BGN','正常',if (CARD_STAT = 'STOP','无效','挂失')) as 状态," + "STORE_PERSON as 联系人,b.USER_TEL as 联系电话,b.cert_id as 身份证,c.USER_NAME as 开卡人 " + "FROM base_stall a,mng_card b, sys_users c where b.ADD_ID=c.USER_ID and b.CARD_TYPE=2 and a.STORE_ID = b.STORE_ID and b.STORE_ID = " + iFirmID; //+"order by CARD_TYPE,CARD_ID desc"; iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst != 0) { goto Eend; } DataTable dt = ds.Tables[0]; dataGridView1.Columns.Clear();// dataGridView1.DataSource = dt; dataGridView1.Refresh(); } catch (Exception ex) { MessageBox.Show("卖方卡查询失败( " + ex.Message + " )", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } Eend: MyStart.oMyDb.Close(); }
private void button_Qry_Click(object sender, EventArgs e) { MyFunc.WriteToDbLog("查询商品销售汇总", "", "MSG", MyStart.giUserID); string szBgn = string.Format("{0:yyyy-MM-dd HH:mm:ss}", dateTimePicker_Bgn.Value); string szEnd = string.Format("{0:yyyy-MM-dd HH:mm:ss}", dateTimePicker_End.Value); dataGridViewRst.Rows.Clear(); MyFunc.GridInit(ref dataGridViewRst, mszTitle, mszTitleWidth, 15, miDefRows, true); if (MyStart.gszMrktName.Length > 0) { mszRptTitle = MyStart.gszMrktName + "商品销售汇总表"; } else { mszRptTitle = "商品销售汇总表"; } string szDateCondition = "jy_tim>='" + szBgn + "' and jy_tim<='" + szEnd + "'"; DataSet ds = new DataSet(); int iFirmNum = comboBox_Firm.Items.Count - 1; string[] szFirmID = new string[iFirmNum]; //prod id string[] szFirmName = new string[iFirmNum]; string[] szFirmCard = new string[iFirmNum]; //prod id int iBgnRow = 0; try { if (comboBox_Firm.SelectedIndex == 0)//all { //2)get STORE_CARD for (int i = 0; i < iFirmNum; i++) { string[] szItem = comboBox_Firm.Items[i + 1].ToString().Split('-'); szFirmID[i] = szItem[0]; szFirmName[i] = szItem[1]; szFirmCard[i] = szFirmID[i]; //3)get data if (szFirmCard[i].Trim().Length > 0) { WriteDataToGrid(szFirmName[i], szFirmCard[i], szDateCondition, ref iBgnRow); } } WriteSumToGrid(szDateCondition, ref iBgnRow); } else//only one firm { //2)get STORE_CARD string[] szItem = comboBox_Firm.Items[comboBox_Firm.SelectedIndex].ToString().Split('-'); szFirmID[0] = szItem[0]; szFirmName[0] = szItem[1]; szFirmCard[0] = szFirmID[0]; //3)get data if (szFirmCard[0].Trim().Length > 0) { WriteDataToGrid(szFirmName[0], szFirmCard[0], szDateCondition, ref iBgnRow); } } if (iBgnRow == 0) { MessageBox.Show("没有数据", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); goto Eend; } miRows = iBgnRow; mszRptDate = szBgn + " 至 " + szEnd; button_Rpt.Enabled = true; } catch (Exception ex) { MessageBox.Show("查询失败( " + ex.Message + " )", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } Eend: MyStart.oMyDb.Close(); }
private void button_Qry_Click(object sender, EventArgs e) { MyFunc.WriteToDbLog("查询发卡明细", "", "MSG", MyStart.giUserID); dataGridView_Detail.Columns.Clear(); dataGridView_Detail.DataSource = null; MyFunc.GridInit(ref dataGridView_Detail, mszTitle, mszTitleWidth, 15, miDefRows, true); int iFirmNum = comboBox_Firm.Items.Count - 1; string[] szFirmID = new string[iFirmNum]; //string[] szFirmName = new string[iFirmNum]; string[] szFirmCard = new string[iFirmNum]; int iBgnRow = 0; int iCardNum = 0; int iAllCardNum = 0; int iCurLine = 0; bool bHasCard = false; int iDoFirm = 0; dataGridView_Detail.Rows.Clear(); MyFunc.GridInit(ref dataGridView_Detail, mszTitle, mszTitleWidth, 15, miDefRows, true); if (comboBox_Firm.SelectedIndex == 0)//all { //2)get STORE_CARD for (int i = 0; i < iFirmNum; i++) { string[] szItem = comboBox_Firm.Items[i + 1].ToString().Split('-'); szFirmID[i] = szItem[0]; //szFirmName[i] = szItem[1]; szFirmCard[i] = Get_STORE_CARD(szFirmID[i], ""); //if(Convert.ToInt16( szFirmID[i])>160) //{ // ; //} //3)get data iCardNum = 0; bHasCard = false; if (szFirmCard[i].Trim().Length > 16) { WriteDataToGrid(comboBox_Firm.Items[i + 1].ToString(), szFirmCard[i], "", ref iBgnRow, ref iCardNum, ref bHasCard); } if (bHasCard) { iDoFirm++; } iAllCardNum += iCardNum; //iCurLine += iCardNum + 1; } DataTable dt = new DataTable(); for (int i = 0; i < miCols; i++) { dt.Columns.Add(i.ToString(), Type.GetType("System.String")); } dt.Rows.Add(new object[] { "合计 " + iDoFirm + " 卖方已发卡", "", "", "发卡总数 " + iAllCardNum + " 张", "", "", "", "", "" }); //int iRtnNum = iCurLine; MyFunc.GridWriteDt(ref dataGridView_Detail, ref dt, iBgnRow, miCols, ref iCurLine); groupBox3.Text = "发卡明细:总卖方数 " + iFirmNum + " 户,已发卡卖方 " + iDoFirm + " 户,发卡总数 " + iAllCardNum + " 张"; } else//only one firm { if (comboBox_Stall.SelectedIndex == 0)//all {//2)get STORE_CARD string[] szItem = comboBox_Firm.Items[comboBox_Firm.SelectedIndex].ToString().Split('-'); szFirmID[0] = szItem[0]; //szFirmName[0] = szItem[1]; szFirmCard[0] = Get_STORE_CARD(szFirmID[0], ""); //3)get data iCardNum = 0; if (szFirmCard[0].Trim().Length > 0) { WriteDataToGrid(comboBox_Firm.Items[comboBox_Firm.SelectedIndex].ToString(), szFirmCard[0], "", ref iBgnRow, ref iCardNum, ref bHasCard); } groupBox3.Text = "发卡明细:该卖方发卡总数 " + iCardNum + " 张"; } else { string[] szItem = comboBox_Firm.Items[comboBox_Firm.SelectedIndex].ToString().Split('-'); szFirmID[0] = szItem[0]; string[] szX = comboBox_Stall.Items[comboBox_Stall.SelectedIndex].ToString().Split('-'); string szRentID = szX[0]; //szFirmName[0] = szItem[1]; szFirmCard[0] = Get_STORE_CARD(szFirmID[0], szRentID); //3)get data iCardNum = 0; if (szFirmCard[0].Trim().Length > 0) { WriteDataToGrid(comboBox_Firm.Items[comboBox_Firm.SelectedIndex].ToString(), szFirmCard[0], "", ref iBgnRow, ref iCardNum, ref bHasCard); } groupBox3.Text = "发卡明细:该档口发卡总数 " + iCardNum + " 张"; } } if (dataGridView_Detail.RowCount > 0) { button_Rpt.Enabled = true; } miRows = iBgnRow + 1; }
private void frm_Setup_Run_Load(object sender, EventArgs e) { this.Icon = new Icon(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.Setup.ico")); pictureBox1.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.Setup.ico")); button_Edit.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.Edit0.ico")); button_Save.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.Save.ico")); button_Quit.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.Undo.ico")); //button_update.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.Yes.ico")); button_Exit.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.exit01.ico")); textBox_mrkt_mnger.Text = MyStart.gszMrktMnger; textBox_mrkt_name.Text = MyStart.gszMrktName; textBox_mrkt_addr.Text = MyStart.gszMrktAddr; textBox_mrkt_tel.Text = MyStart.gszMrktTel; textBox_Card_YTB.Text = MyStart.gszCardYtbFirst; textBox_Card_Firm.Text = MyStart.gszCardFirmFirst; //textBox_Pos_ID.Text = MyStart.gszPosID; textBox_Firm_ID.Text = MyStart.gszFirmID; textBox_FeeChgCard.Text = MyStart.giFeeChgCard.ToString("0.00"); if (MyStart.gszWeight == "斤") { radioButton_jin.Checked = true; } else { radioButton_kg.Checked = true; } MyFunc.GridInit(ref dataGridViewTmn, mszTitle, mszTitleWidth, 15, miDefRows, true); DataSet ds = new DataSet(); string szErr = ""; string szSql = "select PSAM_NO as PSAM卡号,TMN_CODE as 终端编码,TMN_NAME as 终端名称," + "if(PSAM_STAT='Y','有效','无效') as PSAM卡状态 from base_psam order by id"; int iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst != 0) { MyStart.oMyDb.Close(); return; } DataTable dt = ds.Tables[0]; mDt = dt; int iNum = dt.Rows.Count; if (iNum == 0) { MyStart.oMyDb.Close(); return; } //miPsamNum = iNum; int iCurNum = (iNum < miDefRows ? miDefRows : iNum); MyFunc.GridWriteDt(ref dataGridViewTmn, ref dt, 0, miCols, ref iCurNum); szSql = "select sub_type from base_value where type=1"; iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst == 0) { iNum = ds.Tables[0].Rows.Count; if (iNum > 0) { textBox_AddType.Text = ""; for (int i = 0; i < iNum; i++) { DataRow dr = ds.Tables[0].Rows[i]; if (dr[0].ToString().Trim().Length == 0) { continue; } textBox_AddType.Text += dr[0].ToString() + "\r\n"; } } } szSql = "select sub_type from base_value where type=2"; iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst == 0) { iNum = ds.Tables[0].Rows.Count; if (iNum > 0) { textBox_MinusType.Text = ""; for (int i = 0; i < iNum; i++) { DataRow dr = ds.Tables[0].Rows[i]; if (dr[0].ToString().Trim().Length == 0) { continue; } textBox_MinusType.Text += dr[0].ToString() + "\r\n"; } } } groupBox_para.Enabled = false; button_Edit.Enabled = true; button_Save.Enabled = false; button_Quit.Enabled = false; button_Exit.Enabled = true; button_Exit.Select(); MyStart.oMyDb.Close(); }
private void button_qry2_Click(object sender, EventArgs e) { MyFunc.WriteToDbLog("查询发卡明细", "", "MSG", MyStart.giUserID); dataGridView_Detail.Columns.Clear(); dataGridView_Detail.DataSource = null; MyFunc.GridInit(ref dataGridView_Detail, mszTitle, mszTitleWidth, 15, miDefRows, true); string szWhere = ""; if (radioButton_card.Checked) { szWhere += "STORE_CARD like '%" + textBox_Card.Text.Trim() + "%' "; } if (radioButton_cell.Checked) { szWhere += "a.USER_TEL like '%" + textBox_Cell.Text.Trim() + "%' "; } if (radioButton_name.Checked) { szWhere += "a.STORE_PERSON like '%" + textBox_name.Text.Trim() + "%' "; } if (radioButton_date.Checked) { szWhere += "DATE_FORMAT(a.ADD_DT, '%Y-%m-%d') = '" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "' "; } string szSql = "(SELECT store_name as 卖方名称,a.ADD_DT as 发卡时间, " + "if (CARD_TYPE = 1,'第一副卡',if (CARD_TYPE = 2,'副卡','结算卡')) as 卡类,STORE_CARD as 卡号, " + "if (CARD_STAT = 'BGN','正常',if (CARD_STAT = 'STOP','无效','挂失')) as 状态,STALL_INF as 档口信息, " + "a.STORE_PERSON as 联系人,a.USER_TEL as 联系电话, a.cert_id as 身份证, b.USER_NAME as 开卡人 " + "FROM mng_card a,sys_users b,base_store c where a.ADD_ID=b.USER_ID and a.STORE_ID=c.STORE_ID and " + szWhere + " order by a.ADD_DT) " + "union " + "(select '小计', '', '', count(*), '','', '', '', '', '' from mng_card a where " + szWhere + ")"; try { string szErr = ""; DataSet ds = new DataSet(); int iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst != 0) { goto Eend; } DataTable dt = ds.Tables[0]; int iNum = dt.Rows.Count; dataGridView_Detail.Columns.Clear();// dataGridView_Detail.DataSource = dt; dataGridView_Detail.Refresh(); if (iNum > 0) { button_Rpt.Enabled = true; } else { //groupBox3.Text = "发卡明细:"; button_Rpt.Enabled = false; } if (!radioButton_date.Checked) { mszRptDate = ""; } else { mszRptDate = "发卡日期:" + dateTimePicker1.Value.ToString("yyyy-MM-dd"); } miRows = iNum; } catch (Exception ex) { MessageBox.Show("买方卡开卡查询失败( " + ex.Message + " )", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } Eend: MyStart.oMyDb.Close(); if (dataGridView_Detail.RowCount > 0) { button_Rpt.Enabled = true; } //miRows = iBgnRow + 1; }
//private void checkBox_All_Click(object sender, EventArgs e) //{ // dateTimePicker1.Enabled = !checkBox_All.Checked; //} private void button_Qry_Click(object sender, EventArgs e) { dataGridView1.Columns.Clear(); dataGridView1.DataSource = null; MyFunc.GridInit(ref dataGridView1, mszTitle, mszTitleWidth, 15, miDefRows, true); string szWhere = ""; if (radioButton_card.Checked) { szWhere += "user_card like '%" + textBox_Card.Text.Trim() + "%' "; } if (radioButton_cell.Checked) { szWhere += "a.USER_TEL like '%" + textBox_Cell.Text.Trim() + "%' "; } if (radioButton_name.Checked) { szWhere += "a.user_name like '%" + textBox_name.Text.Trim() + "%' "; } if (radioButton_date.Checked) { szWhere += "DATE_FORMAT(oper_time, '%Y-%m-%d') = '" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "' "; } string szSql = "(select a.user_name as 姓名,a.cert_id as 身份证号,a.user_tel as 联系电话,a.user_addr as 经营地址, oper_time as 开卡时间," + "user_card as 卡号,if(card_flag='BGN','正常',if(card_flag='STOP','无效','挂失')) as 卡片状态,a.rmrk as 备注, b.USER_NAME as 开卡人 " + "from base_ucard a,sys_users b where a.ADD_ID=b.USER_ID "; if (!checkBox_All.Checked) { szSql += "and " + szWhere; } szSql += "order by a.user_name,a.cert_id) "; szSql += "union (select '合计', '', '','', '', count(*), '','', '' from base_ucard a "; if (!checkBox_All.Checked) { szSql += "where " + szWhere; } szSql += ")"; try { string szErr = ""; DataSet ds = new DataSet(); int iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst != 0) { goto Eend; } DataTable dt = ds.Tables[0]; int iNum = dt.Rows.Count; dataGridView1.Columns.Clear();// dataGridView1.DataSource = dt; dataGridView1.Refresh(); if (iNum > 0) { if (checkBox_All.Checked) { groupBox3.Text = "发卡明细:共发买方卡 " + (iNum - 1).ToString() + " 张"; } else { if (radioButton_card.Checked) { groupBox3.Text = "发卡明细:卡号包含该信息的买方卡共 " + (iNum - 1).ToString() + " 张"; } if (radioButton_cell.Checked) { groupBox3.Text = "发卡明细:手机号包含该信息的买方卡共 " + (iNum - 1).ToString() + " 张"; } if (radioButton_name.Checked) { groupBox3.Text = "发卡明细:姓名包含该信息的买方卡共 " + (iNum - 1).ToString() + " 张"; } if (radioButton_date.Checked) { groupBox3.Text = "发卡明细:该日共发买方卡 " + (iNum - 1).ToString() + " 张"; } } button_Rpt.Enabled = true; } else { //groupBox3.Text = "发卡明细:"; button_Rpt.Enabled = false; } if (!radioButton_date.Checked) { mszRptDate = ""; } else { mszRptDate = "发卡日期:" + dateTimePicker1.Value.ToString("yyyy-MM-dd"); } miRows = iNum; } catch (Exception ex) { MessageBox.Show("买方卡开卡查询失败( " + ex.Message + " )", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } Eend: MyStart.oMyDb.Close(); }
private void button_Qry_Click(object sender, EventArgs e) { MyFunc.WriteToDbLog("查询买方交易汇总", "", "MSG", MyStart.giUserID); string szBgn = string.Format("{0:yyyy-MM-dd HH:mm:ss}", dateTimePicker_Bgn.Value); string szEnd = string.Format("{0:yyyy-MM-dd HH:mm:ss}", dateTimePicker_End.Value); dataGridViewRst.Rows.Clear(); MyFunc.GridInit(ref dataGridViewRst, mszTitle, mszTitleWidth, 15, miDefRows, true); if (MyStart.gszMrktName.Length > 0) { mszRptTitle = MyStart.gszMrktName + "买方交易汇总表"; } else { mszRptTitle = "买方交易汇总表"; } string szDateCondition = "jy_tim>='" + szBgn + "' and jy_tim<='" + szEnd + "'"; DataSet ds = new DataSet(); int iFirmNum = comboBox_Firm.Items.Count - 1; string[] szFirmID = new string[iFirmNum]; //user card id string[] szFirmName = new string[iFirmNum]; string[] szFirmCard = new string[iFirmNum]; //user card id int iBgnRow = 0; try { if (radioButton_Sel.Checked) { if (comboBox_Firm.SelectedIndex == 0)//all { //2)get STORE_CARD for (int i = 0; i < iFirmNum; i++) { string[] szItem = comboBox_Firm.Items[i + 1].ToString().Split('-'); szFirmID[i] = szItem[0]; szFirmName[i] = szItem[1]; szFirmCard[i] = szFirmID[i]; //3)get data if (szFirmCard[i].Trim().Length > 0) { WriteDataToGrid(szFirmName[i], szFirmCard[i], szDateCondition, ref iBgnRow); } } } else//only one firm { //2)get STORE_CARD string[] szItem = comboBox_Firm.Items[comboBox_Firm.SelectedIndex].ToString().Split('-'); szFirmID[0] = szItem[0]; szFirmName[0] = szItem[1]; szFirmCard[0] = szFirmID[0]; //3)get data if (szFirmCard[0].Trim().Length > 0) { WriteDataToGrid(szFirmName[0], szFirmCard[0], szDateCondition, ref iBgnRow); } } } if (radioButton_Input.Checked) { szFirmID[0] = textBox_Card.Text.Trim(); szFirmCard[0] = szFirmID[0]; //check card and get name string szSql = "select user_name from base_ucard where user_card='" + szFirmCard[0] + "'"; string szErr = ""; int iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst != 0) { MessageBox.Show("查询买方卡失败!", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); MyIniFile.WriteLog("查询买方卡信息", "SQL=" + szSql + ",Err=" + szErr); goto Eend; } int iNum = ds.Tables[0].Rows.Count; if (iNum == 0) { MessageBox.Show("系统里无此买方卡,请换卡!", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); MyIniFile.WriteLog("查询买方卡信息", "SQL=" + szSql + ",Err=系统里无此买方卡"); goto Eend; } if (iNum > 1) { MessageBox.Show("重复发卡,请换卡!", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); MyIniFile.WriteLog("查询买方卡信息", "SQL=" + szSql + ",Err=重复发卡"); goto Eend; } DataRow dr = ds.Tables[0].Rows[0]; szFirmName[0] = dr[0].ToString(); //3)get data if (szFirmCard[0].Trim().Length > 0) { WriteDataToGrid(szFirmName[0], szFirmCard[0], szDateCondition, ref iBgnRow); } } if (radioButton_name.Checked) { szFirmID[0] = textBox_Card.Text.Trim(); szFirmCard[0] = szFirmID[0]; //check card and get name string szSql = "select user_card,user_name from base_ucard where user_name like '%" + textBox_name.Text + "%'"; string szErr = ""; int iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst != 0) { MessageBox.Show("查询买方卡失败!", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); MyIniFile.WriteLog("查询买方卡信息", "SQL=" + szSql + ",Err=" + szErr); goto Eend; } iFirmNum = ds.Tables[0].Rows.Count; if (iFirmNum == 0) { MessageBox.Show("系统里无此买方卡,请换卡!", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); MyIniFile.WriteLog("查询买方卡信息", "SQL=" + szSql + ",Err=系统里无此买方卡"); goto Eend; } for (int i = 0; i < iFirmNum; i++) { DataRow dr = ds.Tables[0].Rows[i]; string[] szItem = comboBox_Firm.Items[i + 1].ToString().Split('-'); szFirmID[i] = dr[0].ToString(); szFirmName[i] = dr[1].ToString(); szFirmCard[i] = szFirmID[i]; //3)get data if (szFirmCard[i].Trim().Length > 0) { WriteDataToGrid(szFirmName[i], szFirmCard[i], szDateCondition, ref iBgnRow); } } } if (iBgnRow == 0) { MessageBox.Show("没有数据", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); goto Eend; } miRows = iBgnRow; mszRptDate = szBgn + " 至 " + szEnd; button_Rpt.Enabled = true; } catch (Exception ex) { MessageBox.Show("查询失败( " + ex.Message + " )", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } Eend: MyStart.oMyDb.Close(); }
private void button_Qry_Click(object sender, EventArgs e) { MyFunc.WriteToDbLog("查询市场交易汇总", "", "MSG", MyStart.giUserID); string szBgn = string.Format("{0:yyyy-MM-dd HH:mm:ss}", dateTimePicker_Bgn.Value); string szEnd = string.Format("{0:yyyy-MM-dd HH:mm:ss}", dateTimePicker_End.Value); dataGridViewRst.Rows.Clear(); MyFunc.GridInit(ref dataGridViewRst, mszTitle, mszTitleWidth, 15, miDefRows, true); if (MyStart.gszMrktName.Length > 0) { mszRptTitle = MyStart.gszMrktName + "交易汇总表"; } else { mszRptTitle = "市场交易汇总表"; } string szDateCondition = "jy_tim>='" + szBgn + "' and jy_tim<='" + szEnd + "'"; string szSql = "select '" + MyStart.gszMrktName + "',count(*), format(sum(prod_w - prod_nw) / 1000, 3)," + "format(sum(prod_amount) / 100, 2),format(sum(prod_amount) * 10 / sum(prod_w - prod_nw), 2)," + "format(sum(MKT_BUYER_FEE) / 100, 2), format(sum(MKT_SELLER_FEE) / 100, 2)," + "format(sum(MKT_BUYER_FEE + MKT_SELLER_FEE) / 100, 2), " + "format(sum(YTB_BUYER_FEE) / 100, 2), format(sum(YTB_SELLER_FEE) / 100, 2)," + "format(sum(YTB_BUYER_FEE + YTB_SELLER_FEE) / 100, 2), " + "format(sum(prod_amount + MKT_BUYER_FEE + MKT_SELLER_FEE + YTB_BUYER_FEE + YTB_SELLER_FEE) / 100, 2) " + "from rec_trade where CANCEL_MARK='N' and PROD_ID>0 and " + szDateCondition; string szErr = ""; try { DataSet ds = new DataSet(); int iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst != 0) { MessageBox.Show("查询失败!", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); MyIniFile.WriteLog("查询市场交易记录", "SQL=" + szSql + ",Err=" + szErr); goto Eend; } DataTable dt = ds.Tables[0]; int iNum = dt.Rows.Count; if (iNum == 0) { goto Eend; } //mDt = dt; int iCurNum = (iNum < miDefRows ? miDefRows : iNum); int iBgnRow = 0; MyFunc.GridWriteDt(ref dataGridViewRst, ref dt, iBgnRow, miCols, ref iCurNum); iBgnRow += iNum; if (iBgnRow == 0) { MessageBox.Show("没有数据", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); goto Eend; } miRows = iBgnRow; mszRptDate = szBgn + " 至 " + szEnd; button_Rpt.Enabled = true; } catch (Exception ex) { MessageBox.Show("查询失败( " + ex.Message + " )", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } Eend: MyStart.oMyDb.Close(); }
private void frm_Qry_DayProd_Load(object sender, EventArgs e) { this.Icon = new Icon(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.report2.ico")); button_Qry.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.preview.ico")); button_Rpt.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.report.ico")); button_Exit.Image = Image.FromStream(frm_Main.oResource.GetManifestResourceStream(frm_Main.szProjName + ".Pict.exit01.ico")); groupBox2.Width = this.Width - 50; groupBox2.Height = this.Height - groupBox2.Top - 50; dataGridViewRst.Width = groupBox2.Width - 20; dataGridViewRst.Height = groupBox2.Height - 30; dateTimePicker_Bgn.CustomFormat = "yyyy-MM-dd HH:mm:ss"; dateTimePicker_Bgn.Format = DateTimePickerFormat.Custom; dateTimePicker_End.CustomFormat = "yyyy-MM-dd HH:mm:ss"; dateTimePicker_End.Format = DateTimePickerFormat.Custom; //dateTimePicker_End.Value = DateTime.Now; dateTimePicker_Bgn.Text = MyStart.gdtLogin.ToString("yyyy-MM-dd 08:00:00"); //DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd 08:00:00");//.AddMonths(-1); dateTimePicker_End.Text = MyStart.gdtLogin.AddDays(1).ToString("yyyy-MM-dd 07:59:59"); //DateTime.Now.ToString("yyyy-MM-dd 07:59:59"); DateTime Dt = DateTime.Now; if (Dt >= DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd 00:00:00")) && Dt < DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd 08:00:00"))) { dateTimePicker_Bgn.Text = Dt.AddDays(-1).ToString("yyyy-MM-dd 08:00:00"); //dateTimePicker_Bgn.MaxDate = DateTime.Parse(dateTimePicker_Bgn.Text); dateTimePicker_End.Text = Dt.ToString("yyyy-MM-dd 07:59:59"); //dateTimePicker_End.MaxDate = DateTime.Parse(dateTimePicker_End.Text); } else { dateTimePicker_Bgn.Text = Dt.ToString("yyyy-MM-dd 08:00:00"); //dateTimePicker_Bgn.MaxDate = DateTime.Parse(dateTimePicker_Bgn.Text); dateTimePicker_End.Text = Dt.AddDays(1).ToString("yyyy-MM-dd 07:59:59"); //dateTimePicker_End.MaxDate = DateTime.Parse(dateTimePicker_End.Text); } textBox_Card.Text = ""; textBox_name.Text = ""; DataSet ds = new DataSet(); string szErr = ""; int iStoreNum = 0;//买方 string szSql = "select concat(user_card,'-',user_name) from base_ucard order by user_card"; int iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); if (iRst == 0) { iStoreNum = ds.Tables[0].Rows.Count; if (iStoreNum > 0) { comboBox_Firm.Items.Add("0-所有买方"); } for (int i = 0; i < iStoreNum; i++) { DataRow dr = ds.Tables[0].Rows[i]; comboBox_Firm.Items.Add(dr[0].ToString()); } } if (iStoreNum > 0) { comboBox_Firm.SelectedIndex = 0; } /* int iProdNum = 0;//商品 * szSql = "select concat(PROD_ID,'-',PROD_NAME) from base_prod where PROD_LEVEL=1 order by PROD_ID"; * iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); * if (iRst == 0) * { * iProdNum = ds.Tables[0].Rows.Count; * if (iProdNum > 0) * comboBox_Prod.Items.Add("0-所有商品"); * for (int i = 0; i < iProdNum; i++) * { * DataRow dr = ds.Tables[0].Rows[i]; * comboBox_Prod.Items.Add(dr[0].ToString()); * } * } * if (iProdNum > 0) * comboBox_Prod.SelectedIndex = 0; * * int iPosNum = 0; * szSql = "select concat(tmn_code,'-',tmn_name) from base_psam order by tmn_code"; * iRst = MyStart.oMyDb.ReadData(szSql, "tableA", ref ds, ref szErr); * if (iRst == 0) * { * iPosNum = ds.Tables[0].Rows.Count; * if (iPosNum > 0) * comboBox_POS.Items.Add("0-所有收银终端"); * for (int i = 0; i < iPosNum; i++) * { * DataRow dr = ds.Tables[0].Rows[i]; * comboBox_POS.Items.Add(dr[0].ToString()); * } * } * if (iPosNum > 0) * comboBox_POS.SelectedIndex = 0;*/ MyFunc.GridInit(ref dataGridViewRst, mszTitle, mszTitleWidth, 15, miDefRows, true); string[] szX = mszTitle.Split(','); miCols = szX.Length; radioButton_Sel.Checked = true; MyStart.oMyDb.Close(); }