private void WriteDataToGrid2(string szFirmName, string szID, string szBgn, string szEnd, ref int iBgnRow)
{
/* string szSql = "select b.USER_NAME,if (oper_type = 'ADD','买方充值',"
+ "if (oper_type = 'TRADE','买方消费',if (oper_type = 'CASH','买方取现',if (oper_type='CHG','买方换卡','买方取消交易')))),"
+ "format(sum(chg_val)/100,2) from rec_user a,sys_users b "
+ "where a.Oper_ID = b.USER_ID and b.USER_NAME = '" + szFirmName + "' and " + szDateCondition + " group by oper_type "
+ "union "
+ "select b.USER_NAME,if (oper_type = 'ADD','卖方充值',"
+ "if (oper_type = 'TRADE','卖方消费',if (oper_type = 'CASH','卖方取现',if (oper_type='CHG','卖方换卡','卖方取消交易')))),"
+ "format(sum(chg_val)/100,2) from rec_firm a,sys_users b "
+ "where a.Oper_ID = b.USER_ID and b.USER_NAME = '" + szFirmName + "' and " + szDateCondition + " group by oper_type "
+ "union "
+ "select '小计','-','-' from dual";*/
/*select z.USER_ID, z.USER_NAME,i as 买方提现,k as 卖方充值,l as 卖方提现,k-i-l as 结合
* from
* (sys_users z
* left join
* (select a.Oper_ID as h,sum(a.Chg_Val) as i from rec_firm a where a.Oper_Type='TRADE' group by a.Oper_ID) x
* on z.USER_ID=x.h)
* left join
* (select b.Oper_ID as j,sum(b.Chg_Val) as k,sum(c.Chg_Val) as l from rec_user b,rec_user c
* where b.Oper_ID=c.Oper_ID and b.Oper_Type='ADD' and c.Oper_Type='CASH' group by b.Oper_ID,c.Oper_ID) y
* on z.USER_ID=y.j
* where z.USER_STAT='USED' */
Int64[] iItem = new Int64[miCols - 1];
string szSql = "select Oper_ID,sum(Chg_Val) from rec_user where Oper_Type='ADD' and Oper_ID=" + szID
+ " and Oper_Time>='" + szBgn + "' and Oper_Time<='" + szEnd + "'";
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;
}
DataRow dr = ds.Tables[0].Rows[0];
try
{
iItem[0] = Convert.ToInt64(dr[1]);//买方充值
}
catch
{
iItem[0] = 0;
}
iItem[1] = iItem[0];//收入结余
szSql = "select Oper_ID,sum(Chg_Val) from rec_user where Oper_Type='CASH' and Oper_ID=" + szID
+ " and Oper_Time>='" + szBgn + "' and Oper_Time<='" + szEnd + "'";
szErr = "";
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;
}
dr = ds.Tables[0].Rows[0];
try
{
iItem[2] = Convert.ToInt64(dr[1]);//买方提现
}
catch
{
iItem[2] = 0;
}
szSql = "select Oper_ID,sum(Chg_Val) from rec_firm where Oper_Type='CASH' and Oper_ID=" + szID
+ " and Oper_Time>='" + szBgn + "' and Oper_Time<='" + szEnd + "'";
szErr = "";
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;
}
dr = ds.Tables[0].Rows[0];
try
{
iItem[3] = Convert.ToInt64(dr[1]);//卖方提现
}
catch
{
iItem[3] = 0;
}
iItem[4] = iItem[2] + iItem[3]; //支出结余
iItem[5] = iItem[1] - iItem[4]; //总结余
szSql = szID + "-" + szFirmName;
for (int index = 0; index < miCols - 1; index++)
{
szSql += "#" + ((decimal)iItem[index] / 100).ToString("0,000.00");
}
MyFunc.GridWriteData(ref dataGridViewRst, szSql, ref iBgnRow);
}
private void QryCash(string szBgn, string 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)
{
WriteDataToGrid2(szFirmName[i], szFirmID[i], szBgn, szEnd, ref iBgnRow);
}
}
Decimal[] dItem = new Decimal[miCols - 1];
for (int i = 0; i < iBgnRow - 1; i++)
{
for (int j = 0; j < miCols - 1; j++)
{
dItem[j] += Convert.ToDecimal(dataGridViewRst.Rows[i].Cells[j + 1].Value);
}
}
string szText = "合计";
for (int index = 0; index < miCols - 1; index++)
{
szText += "#" + dItem[index].ToString("#0,000.00");
}
MyFunc.GridWriteData(ref dataGridViewRst, szText, 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)
{
WriteDataToGrid2(szFirmName[0], szFirmID[0], szBgn, szEnd, 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();
}