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();
}