private void WriteDataToGrid(string szFirmName, string szFirmCard, string szDateCondition, ref int iBgnRow)
{
string szSql = "select '" + szFirmName + "',PROD_NAME,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), 0.00," //format(sum(MKT_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) "
+ "format(sum(prod_amount + MKT_BUYER_FEE + YTB_BUYER_FEE + YTB_SELLER_FEE) / 100, 2) "
+ "from rec_trade a,base_prod b "
+ "where a.CANCEL_MARK='N' and user_card in (" + szFirmCard + ") and a.PROD_ID = b.PROD_ID and b.PROD_LEVEL=1 and " + szDateCondition
+ " group by a.prod_id";
string szErr = "";
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);
MyStart.oMyDb.Close();
return;
}
DataTable dt = ds.Tables[0];
int iNum = dt.Rows.Count;
if (iNum == 0)
{
MyStart.oMyDb.Close();
return;
}
//mDt = dt;
int iCurNum = (iNum < miDefRows ? miDefRows : iNum);
MyFunc.GridWriteDt(ref dataGridViewRst, ref dt, iBgnRow, miCols, ref iCurNum);
iBgnRow += iNum;
szSql = "select ' ','汇总',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),0.00," // format(sum(MKT_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)"
+ "format(sum(prod_amount + MKT_BUYER_FEE + YTB_BUYER_FEE + YTB_SELLER_FEE) / 100, 2)"
+ "from rec_trade where CANCEL_MARK='N' and user_card in (" + szFirmCard + ") and PROD_ID>0 and " + szDateCondition;
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);
MyStart.oMyDb.Close();
return;
}
dt = ds.Tables[0];
//mDt = dt;
iCurNum = 1;// (iNum < miDefRows ? miDefRows : iNum);
MyFunc.GridWriteDt(ref dataGridViewRst, ref dt, iBgnRow, miCols, ref iCurNum);
iBgnRow++;
MyStart.oMyDb.Close();
}
private void WriteSumToGrid(string szDateCondition, ref int iBgnRow)
{
string szSql = "select '汇总',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(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)"
+ "from rec_trade a,base_prod b where CANCEL_MARK='N' and a.PROD_ID = b.PROD_ID and b.PROD_LEVEL=1 and " + szDateCondition;
string szErr = "";
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);
MyStart.oMyDb.Close();
return;
}
DataTable dt = ds.Tables[0];
//mDt = dt;
int iCurNum = 1;// (iNum < miDefRows ? miDefRows : iNum);
MyFunc.GridWriteDt(ref dataGridViewRst, ref dt, iBgnRow, miCols, ref iCurNum);
iBgnRow++;
}
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 WriteSumToGrid(string szDateCondition, ref int iBgnRow)
{
//string szSql = "select '汇总',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(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 " + szDateCondition;
string szSql = "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)*10 / sum(prod_w - prod_nw),2),format(sum(prod_amount) / 100, 2), "
+ "format(sum(MKT_BUYER_FEE + MKT_SELLER_FEE + YTB_BUYER_FEE + YTB_SELLER_FEE) / 100,2) "
+ "from rec_trade a,sys_users b "
+ "where a.USER_ID2 = b.USER_ID and a.CANCEL_MARK='N' and a.PROD_ID>0 and " + szDateCondition;
if (comboBox_dpt.SelectedIndex > 0)
{
if (comboBox_Firm.SelectedIndex == 0)
{
szSql += " and b.USER_DPT='" + comboBox_dpt.Text.Trim() + "'";
}
}
string szErr = "";
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);
MyStart.oMyDb.Close();
return;
}
DataTable dt = ds.Tables[0];
//mDt = dt;
int iCurNum = 1;// (iNum < miDefRows ? miDefRows : iNum);
MyFunc.GridWriteDt(ref dataGridViewRst, ref dt, iBgnRow, miCols, ref iCurNum);
iBgnRow++;
}
private void WriteDataToGrid(string szFirm, string szFirmCard, string szDateCondition, ref int iBgnRow, ref int iCardNum, ref bool HasCard)
{
HasCard = false;
string szSql = "(SELECT '" + szFirm + "' 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 档口信息, "
+ "STORE_PERSON as 联系人,a.USER_TEL as 联系电话, a.cert_id as 身份证, b.USER_NAME as 开卡人 "
+ "FROM mng_card a,sys_users b where a.ADD_ID=b.USER_ID and STORE_CARD in( " + szFirmCard + ") order by a.ADD_DT) "
+ "union "
+ "(select '小计', '', '', count(*), '','', '', '', '', '' from mng_card where STORE_CARD in( " + szFirmCard + "))";
string szErr = "";
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);
MyStart.oMyDb.Close();
return;
}
DataTable dt = ds.Tables[0];
int iNum = dt.Rows.Count;
if (iNum == 1)
{
MyStart.oMyDb.Close();
return;
}
//mDt = dt;
iCardNum = iNum - 1;
int iCurNum = (iNum < miDefRows ? miDefRows :iNum);
MyFunc.GridWriteDt(ref dataGridView_Detail, ref dt, iBgnRow, miCols, ref iCurNum);
iBgnRow += iNum;
HasCard = true;
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 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_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_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 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 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();
}