private void btnQuery_Click(object sender, System.EventArgs e)
{
string strSql = "select cnvcJobName,cast (cnvcSeat as int) as cnvcSeat,cnvcOperName,cndOperDate from tbShowSeat where cnvcState = '" + ConstApp.Show_Seat_State_Remain + "' and cnvcSeat is not null";
if (cmbShow.SelectedItem != null)
{
strSql += " and cnnJobID=" + cmbShow.SelectedItem.DataValue.ToString();
}
if (chkBeginDate.Checked)
{
strSql += " and cndOperDate >='" + cmdBeginDate.Text + "'";
}
if (chkEndDate.Checked)
{
strSql += " and cndOperDate <='" + cmbEndDate.Text + "'";
}
strSql += " order by cast (cnvcSeat as int)";
DataTable dt = Helper.Query(strSql); //query.Report(strSql);
this.ultraGrid1.DataSource = null;
this.ultraGrid1.DataSource = dt;
this.ultraGrid1.SetDataBinding(dt, null);
ClientHelper.AddGridColumn(this.ultraGrid1, this.oper.cnvcOperName);
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Count, "{0}", 0);
}
private void btnQuery_Click(object sender, System.EventArgs e)
{
try
{
//string strSql = "select cnnJobID,'' as cnvcJobName,cnvcMemberCardNo,cnvcPaperNo,'' as cnvcMemberName,cnnPrepay,cnnReturn,cnnBalance,cnvcState,cnvcOperName,cndOperDate from tbPrepay where 1=1 ";
string strSql = "select * from tbProduct";
strSql += " where 1=1";
if (txtMemberCardNo.Text.Trim().Length > 0)
{
strSql += " and cnvcMemberCardNo like '%" + txtMemberCardNo.Text + "%'";
}
if (txtMemberName.Text != "")
{
strSql += " and cnvcMemberName like '%" + txtMemberName.Text + "%'";
}
if (txtPaperNo.Text.Trim().Length > 0)
{
strSql += " and cnvcPaperNo like '%" + txtPaperNo.Text + "%'";
}
if (cmbOperName.Text.Trim().Length > 0)
{
strSql += " and cnvcOperName like '%" + cmbOperName.Text + "%'";
}
if (chkBeginDate.Checked)
{
strSql += " and cndOperDate >= '" + cmbBeginDate.Value.ToString() + "'";
}
if (chkEndDate.Checked)
{
strSql += " and cndOperDate <= '" + cmbEndDate.Value.ToString() + "'";
}
// if (cmbState.Text.Trim().Length > 0)
// {
// strSql += " and a.cnvcState = '"+cmbState.Text+"'";
// }
if (cmbProduct.Text.Trim().Length > 0)
{
strSql += " and cnvcProduct like '%" + cmbProduct.Text + "%'";
}
DataTable dtPrepay = Helper.Query(strSql);
this.ultraGrid1.DataSource = null;
this.ultraGrid1.DataSource = dtPrepay;
this.ultraGrid1.DataBind();
ClientHelper.AddGridColumn(this.ultraGrid1, this.oper.cnvcOperName);
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Count, "服务产品消费次数：{0}", "cnvcMemberCardNo");
}
catch (BusinessException bex)
{
MessageBox.Show(this, bex.Message, bex.Type, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (System.Exception ex)
{
MessageBox.Show(this, ex.Message, "系统错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnQuery_Click(object sender, System.EventArgs e)
{
//查询
try
{
//this.btnInlose.Enabled = true;
string strSql = "select "
+ " cnvcMemberCardNo,cnvcMemberPwd,cnvcMemberName,cnvcPaperNo,cnvcCorporation, "
+ " cnvcCompanyAddress,cnvcLinkman,cnvcLinkPhone,cnvcEmail,cnvcFax,cnvcComments,cnvcMemberRight,cnvcEnterpriseType,cnvcDiscount,cnvcFree,cnvcProduct,cnvcState,cnnMemberFee, "
+ " cnnPrepay,case when cndEndDate ='(none)' then null else convert(char(10),cast(cndEndDate as datetime),121) end cndEndDate,cnvcOperName,cndOperDate,cnvcMobileTelePhone,cnvcPostalCode,cnvcSales,cnvcHandleman,cnvcHandlemanPaperNo, "
+ " cnvcTrade,cnvcCustomerService,cnvcCustomerService as cnvcOldCustomerService "
+ " ,case cniSynch "
+ " when 0 then '会员系统' "
+ " when 1 then '网站操作' "
+ " when 2 then '触摸屏操作' "
+ " end cnvcSynch,cndInNetDate from tbMember where Len(cnvcMemberCardNo)=6 ";
if (txtMemberCardNo.Text.Trim().Length > 0)
{
strSql += " and cnvcMemberCardNo like '%" + txtMemberCardNo.Text + "%'";
}
if (txtMemberName.Text.Trim().Length > 0)
{
strSql += " and cnvcMemberName like '%" + txtMemberName.Text + "%'";
}
if (txtPaperNo.Text.Trim().Length > 0)
{
strSql += " and cnvcPaperNo like '%" + txtPaperNo.Text + "%'";
}
if (cmbState.Text.Trim().Length > 0)
{
strSql += " and cnvcState = '" + cmbState.Text + "'";
}
if (cmbEnterpriseType.Text.Trim().Length > 0)
{
strSql += " and cnvcEnterpriseType like '%" + cmbEnterpriseType.Text + "%'";
}
if (cmbTrade.Text.Trim().Length > 0)
{
strSql += " and cnvcTrade like '%" + cmbTrade.Text + "%'";
}
if (cmbSales.Text.Trim().Length > 0)
{
strSql += " and cnvcSales like '%" + cmbSales.Text + "%'";
}
if (cmbCustomerService.Text.Trim().Length > 0)
{
strSql += " and cnvcCustomerService like '%" + cmbCustomerService.Text + "%'";
}
if (chkEndDate.Checked)
{
// string strEndDate="";
// string aa = cmbEndDate.Text.Trim();
//
// if (DateTime.Parse(aa).Month<10)
// {
// strEndDate =DateTime.Parse(aa).Year.ToString()+ "-" + "0" + DateTime.Parse(aa).Month.ToString();
// }
// else
// {
// strEndDate =DateTime.Parse(aa).Year.ToString()+ "-" + DateTime.Parse(aa).Month.ToString();
// }
// if (DateTime.Parse(aa).Day<10)
// {
// strEndDate +="-" + "0" + DateTime.Parse(aa).Day.ToString();
// }
// else
// {
// strEndDate +="-" + DateTime.Parse(aa).Day.ToString();
// }
strSql += " and convert(char(10),cast(cndEndDate as datetime),121) < = '" + cmbEndDate.Text.Trim() + "' ";
}
if (chkEndDateBegin.Checked)
{
strSql += " and convert(char(10),cast(cndEndDate as datetime),121) > = '" + cmbEndDateBegin.Text.Trim() + "' ";
}
if (chkBeginDate.Checked)
{
strSql += " and cndInNetDate >='" + cmdBeginDate.Text + "'";
}
if (chkEndDate2.Checked)
{
strSql += " and cndInNetDate <='" + cmbEndDate2.Text + "'";
}
if (cmbMemberRight.Text.Trim().Length > 0)
{
strSql += " and cnvcMemberRight = '" + cmbMemberRight.Text + "'";
}
Query query = new Query();
DataTable dtMember = query.Report(strSql);
this.ultraGrid1.DataSource = null;
this.ultraGrid1.DataSource = dtMember;
this.ultraGrid1.DataBind();
ClientHelper.AddGridColumn(this.ultraGrid1, this.oper.cnvcOperName);
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Count, "{0}", 0);
}
catch (BusinessException bex)
{
MessageBox.Show(this, bex.Message, bex.Type, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (System.Exception ex)
{
MessageBox.Show(this, ex.Message, "系统错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnQuery_Click(object sender, System.EventArgs e)
{
//查询
try
{
//this.btnInlose.Enabled = true;
string strSql = "select* from tbFMember where 1=1 ";
// if (txtMemberCardNo.Text.Trim().Length > 0)
// {
// strSql += " and cnvcMemberCardNo like '%"+txtMemberCardNo.Text+"%'";
// }
if (txtMemberName.Text.Trim().Length > 0)
{
strSql += " and cnvcMemberName like '%" + txtMemberName.Text + "%'";
}
if (txtPaperNo.Text.Trim().Length > 0)
{
strSql += " and cnvcPaperNo like '%" + txtPaperNo.Text + "%'";
}
if (cmbEnterpriseType.Text.Trim().Length > 0)
{
strSql += " and cnvcEnterpriseType like '%" + cmbEnterpriseType.Text + "%'";
}
if (cmbTrade.Text.Trim().Length > 0)
{
strSql += " and cnvcTrade like '%" + cmbTrade.Text + "%'";
}
if (txtSales.Text.Trim().Length > 0)
{
strSql += " and cnvcSales like '%" + txtSales.Text + "%'";
}
if (txtCustomerService.Text.Trim().Length > 0)
{
strSql += " and cnvcCustomerService like '%" + txtCustomerService.Text + "%'";
}
if (chkBeginDate.Checked)
{
strSql += " and cndInNetDate >='" + cmdBeginDate.Text + "'";
}
if (chkEndDate.Checked)
{
strSql += " and cndInNetDate <='" + cmbEndDate.Text + "'";
}
// if (cmbState.Text.Trim().Length > 0)
// {
// strSql += " and cnvcState = '"+cmbState.Text+"'";
// }
//Query query = new Query();
DataTable dtMember = Helper.Query(strSql); //query.Report(strSql);
this.ultraGrid1.DataSource = null;
this.ultraGrid1.DataSource = dtMember;
this.ultraGrid1.DataBind();
ClientHelper.AddGridColumn(this.ultraGrid1, this.oper.cnvcOperName);
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Count, "{0}", 0);
}
catch (BusinessException bex)
{
MessageBox.Show(this, bex.Message, bex.Type, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (System.Exception ex)
{
MessageBox.Show(this, ex.Message, "系统错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnQuery_Click(object sender, System.EventArgs e)
{
//工本费
//string strSql_Cost = "select '工本费' as cnvcMoneyType,a.cnvcMemberCardNo,b.cnvcMemberName,b.cnvcPaperNo,b.cnvcMemberRight,0 as cnnLastBalance,a.cnnCost as cnnPrepay,0 as cnnDonate,0 as cnnBalance,a.cnvcOperName,a.cndOperDate from tbCostLog a join tbMember b on a.cnvcMemberCardNo=b.cnvcMemberCardNo";
//strSql_Cost += " where 1=1 ";
string strSql_MemberProduct = "select a.cnvcProductName as cnvcMoneyType,a.cnvcMemberCardNo,b.cnvcMemberName,b.cnvcPaperNo,b.cnvcMemberRight,a.cnnLastBalance,a.cnnPrepay,0 as cnnDonate,a.cnnBalance,case when e.cnvcDeptName is null then '云南人才市场' else e.cnvcDeptName end as cnvcDeptName,a.cnvcSales,a.cnvcOperName,a.cndOperDate from tbMemberProductLog a join tbMember b on a.cnvcMemberCardNo=b.cnvcMemberCardNo ";
//strSql_MemberProduct += " where 1=1 ";
//strSql_MemberProduct += " left join tbOper c on a.cnvcOperName=c.cnvcOperName left join tbDept d on c.cnnDeptID=d.cnnDeptID ";
strSql_MemberProduct += " left join tbSales c on a.cnvcSales=c.cnvcSales left join tbOperDept d on c.cnnDeptID=d.cnnDeptID and c.cnnSales=d.cnnOperID left join tbDept e on c.cnnDeptID=e.cnnDeptID ";
//if (this.oper.cnnDeptID != 0)
//{
// //strSql_MemberProduct += " where (d.cnnParentDeptID=" + this.oper.cnnDeptID.ToString() + " or d.cnnDeptID=" + this.oper.cnnDeptID.ToString() + ") ";
// strSql_MemberProduct += string.Format(" where d.cnnOperID={0} ", this.oper.cnnOperID.ToString());
//}
//else
//{
strSql_MemberProduct += " where 1=1 ";
//}
string strSql_FMemberProduct = "select a.cnvcProductName as cnvcMoneyType,'' as cnvcMemberCardNo,b.cnvcMemberName,b.cnvcPaperNo,'' as cnvcMemberRight,a.cnnLastBalance,a.cnnPrepay,0 as cnnDonate,a.cnnBalance,case when e.cnvcDeptName is null then '云南人才市场' else e.cnvcDeptName end as cnvcDeptName,a.cnvcSales,a.cnvcOperName,a.cndOperDate from tbFMemberProductLog a join tbFMember b on a.cnvcPaperNo=b.cnvcPaperNo ";
//strSql_FMemberProduct += " where 1=1 ";
//strSql_FMemberProduct += " left join tbOper c on a.cnvcOperName=c.cnvcOperName left join tbDept d on c.cnnDeptID=d.cnnDeptID ";
strSql_FMemberProduct += " left join tbSales c on a.cnvcSales=c.cnvcSales left join tbOperDept d on c.cnnDeptID=d.cnnDeptID and c.cnnSales=d.cnnOperID left join tbDept e on c.cnnDeptID=e.cnnDeptID ";
//if (this.oper.cnnDeptID != 0)
//{
// //strSql_FMemberProduct += " where (d.cnnParentDeptID=" + this.oper.cnnDeptID.ToString() + " or d.cnnDeptID=" + this.oper.cnnDeptID.ToString() + ") ";
// strSql_FMemberProduct += string.Format(" where d.cnnOperID={0} ", this.oper.cnnOperID.ToString());
//}
//else
//{
strSql_FMemberProduct += " where 1=1 ";
//}
if (!string.IsNullOrEmpty(txtMemberCardNo.Text))
{
strSql_MemberProduct += " and a.cnvcMemberCardNo like '%" + txtMemberCardNo.Text + "%' ";
}
if (!string.IsNullOrEmpty(txtMemberName.Text))
{
strSql_MemberProduct += " and a.cnvcMemberName like '%" + txtMemberName.Text + "%'";
strSql_FMemberProduct += " and a.cnvcMemberName like '%" + txtMemberName.Text + "%'";
}
if (!string.IsNullOrEmpty(txtPaperNo.Text))
{
strSql_MemberProduct += " and a.cnvcPaperNo like '%" + txtPaperNo.Text + "%'";
strSql_FMemberProduct += " and a.cnvcPaperNo like '%" + txtPaperNo.Text + "%'";
}
if (cmbOperName.Text.Trim().Length > 0)
{
//strSql_Cost += " and a.cnvcOperName like '%" + cmbOperName.Text + "%'";
strSql_MemberProduct += " and a.cnvcOperName like '%" + cmbOperName.Text + "%'";
strSql_FMemberProduct += " and a.cnvcOperName like '%" + cmbOperName.Text + "%'";
}
if (chkBeginDate.Checked)
{
//strSql_Cost += " and a.cndOperDate >= '" + cmbBeginDate.Value.ToString() + "'";
strSql_MemberProduct += " and a.cndOperDate >= '" + cmbBeginDate.Value.ToString() + "'";
strSql_FMemberProduct += " and a.cndOperDate >= '" + cmbBeginDate.Value.ToString() + "'";
}
if (chkEndDate.Checked)
{
//strSql_Cost += " and a.cndOperDate <= '" + cmbEndDate.Value.ToString() + "'";
strSql_MemberProduct += " and a.cndOperDate <= '" + cmbEndDate.Value.ToString() + "'";
strSql_FMemberProduct += " and a.cndOperDate <= '" + cmbEndDate.Value.ToString() + "'";
}
if (cmbMemberRight.Text.Trim().Length > 0)
{
//strSql_Cost += " and b.cnvcMemberRight like '%" + cmbMemberRight.Text + "%'";
strSql_MemberProduct += " and b.cnvcMemberRight like '%" + cmbMemberRight.Text + "%'";
}
if (cmbSales.Text.Trim().Length > 0)
{
strSql_MemberProduct += " and a.cnvcSales like '%" + cmbSales.Text + "%'";
strSql_FMemberProduct += " and a.cnvcSales like '%" + cmbSales.Text + "%'";
}
if (cmbDept.Text.Trim().Length > 0)
{
strSql_MemberProduct += " and e.cnvcDeptName like '%" + cmbDept.Text + "%'";
strSql_FMemberProduct += " and e.cnvcDeptName like '%" + cmbDept.Text + "%'";
}
DataTable dtCost = null;
//if (this.ultraComboEditor1.Text == "工本费")
//{
// dtCost = Helper.Query(strSql_Cost);
//}
//else
//{
if (cmbMember.Text.Trim().Length == 0)
{
//dtCost = Helper.Query(strSql_Cost);
dtCost = Helper.Query(strSql_MemberProduct);
DataTable dtFMemberProduct = Helper.Query(strSql_FMemberProduct);
//foreach (DataRow dr in dtMemberProduct.Rows)
//{
// dtCost.Rows.Add(dr.ItemArray);
//}
foreach (DataRow dr in dtFMemberProduct.Rows)
{
dtCost.Rows.Add(dr.ItemArray);
}
}
else if (cmbMember.Text == "会员")
{
//dtCost = Helper.Query(strSql_Cost);
dtCost = Helper.Query(strSql_MemberProduct);
//foreach (DataRow dr in dtMemberProduct.Rows)
//{
// dtCost.Rows.Add(dr.ItemArray);
//}
}
else if (cmbMember.Text == "非会员")
{
dtCost = Helper.Query(strSql_FMemberProduct);
}
//}
this.ultraGrid1.DataSource = null;
this.ultraGrid1.DataSource = dtCost;
this.ultraGrid1.DataBind();
ClientHelper.AddGridColumn(this.ultraGrid1, this.oper.cnvcOperName);
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Count, "收入合计，共{0}条", "cnvcMemberRight");
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Sum, "{0}", "cnnPrepay");
}
private void btnQuery_Click(object sender, System.EventArgs e)
{
try
{
string strSql = "select cnnSerialNo,cnvcMemberCardNo,cnvcPaperNo,cnvcMemberName,cnvcProductName,cnnProductPrice,cnvcProductDiscount,cnnPrepay,cnvcFree,cnvcOperName,cndOperDate from tbMemberProductLog";
strSql += " where 1=1";
if (txtMemberCardNo.Text.Trim().Length > 0)
{
strSql += " and cnvcMemberCardNo like '%" + txtMemberCardNo.Text + "%'";
}
if (txtMemberName.Text != "")
{
strSql += " and cnvcMemberName like '%" + txtMemberName.Text + "%'";
}
if (txtPaperNo.Text.Trim().Length > 0)
{
strSql += " and cnvcPaperNo like '%" + txtPaperNo.Text + "%'";
}
if (cmbOperName.Text.Trim().Length > 0)
{
strSql += " and cnvcOperName like '%" + cmbOperName.Text + "%'";
}
if (chkBeginDate.Checked)
{
strSql += " and cndOperDate >= '" + cmbBeginDate.Value.ToString() + "'";
}
if (chkEndDate.Checked)
{
strSql += " and cndOperDate <= '" + cmbEndDate.Value.ToString() + "'";
}
if (cmbProduct.Text.Trim().Length > 0)
{
strSql += " and cnvcProduct like '%" + cmbProduct.Text + "%'";
}
string strSql2 = "select cnnSerialNo,'' as cnvcMemberCardNo,cnvcPaperNo,cnvcMemberName,cnvcProductName,cnnProductPrice,cnvcProductDiscount,cnnPrepay,cnvcFree,cnvcOperName,cndOperDate from tbFMemberProductLog";
strSql2 += " where 1=1";
// if (txtMemberCardNo.Text.Trim().Length > 0)
// {
// strSql2 += " and cnvcMemberCardNo like '%"+txtMemberCardNo.Text+"%'";
// }
if (txtMemberName.Text != "")
{
strSql2 += " and cnvcMemberName like '%" + txtMemberName.Text + "%'";
}
if (txtPaperNo.Text.Trim().Length > 0)
{
strSql2 += " and cnvcPaperNo like '%" + txtPaperNo.Text + "%'";
}
if (cmbOperName.Text.Trim().Length > 0)
{
strSql2 += " and cnvcOperName like '%" + cmbOperName.Text + "%'";
}
if (chkBeginDate.Checked)
{
strSql2 += " and cndOperDate >= '" + cmbBeginDate.Value.ToString() + "'";
}
if (chkEndDate.Checked)
{
strSql2 += " and cndOperDate <= '" + cmbEndDate.Value.ToString() + "'";
}
if (cmbProduct.Text.Trim().Length > 0)
{
strSql2 += " and cnvcProduct like '%" + cmbProduct.Text + "%'";
}
DataTable dtPrepay = new DataTable();
if (cmbMember.Text.Trim().Length == 0)
{
dtPrepay = Helper.Query(strSql);
DataTable dtPrepay2 = Helper.Query(strSql2);
foreach (DataRow dr in dtPrepay2.Rows)
{
dtPrepay.Rows.Add(dr.ItemArray);
}
}
else if (cmbMember.Text == "会员")
{
dtPrepay = Helper.Query(strSql);
}
else if (cmbMember.Text == "非会员")
{
dtPrepay = Helper.Query(strSql2);
}
this.ultraGrid1.DataSource = null;
this.ultraGrid1.DataSource = dtPrepay;
this.ultraGrid1.DataBind();
ClientHelper.AddGridColumn(this.ultraGrid1, this.oper.cnvcOperName);
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Count, "服务产品消费次数：{0}", "cnvcMemberCardNo");
}
catch (BusinessException bex)
{
MessageBox.Show(this, bex.Message, bex.Type, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (System.Exception ex)
{
MessageBox.Show(this, ex.Message, "系统错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnQuery_Click(object sender, System.EventArgs e)
{
try
{
//发卡
// string strSql_Renew = "select '发卡' as cnvcMoneyType,cnvcMemberCardNo,cnvcMemberName,cnvcPaperNo,cnvcMemberRight,cnnPrepay,cnvcOperName,cndInNetDate as cndOperDate from tbMember ";
// strSql_Renew += " where (cnvcState='"+ConstApp.Card_State_InUse+"' or cnvcState='"+ConstApp.Card_State_InLose+"')";
//会员充值
string strSql_InMoney = "select cnvcOperFlag as cnvcMoneyType,a.cnvcMemberCardNo,b.cnvcMemberName,b.cnvcPaperNo,b.cnvcMemberRight,a.cnnPrepay,a.cnvcOperName,a.cndOperDate from tbMemberPrepayLog a join tbMember b on a.cnvcMemberCardNo=b.cnvcMemberCardNo ";
//strSql_InMoney += " where b.cnvcState<>'"+ConstApp.Card_State_InAdd+"' ";
//工本费
string strSql_Cost = "select '工本费' as cnvcMoneyType,a.cnvcMemberCardNo,b.cnvcMemberName,b.cnvcPaperNo,b.cnvcMemberRight,a.cnnCost as cnnPrepay,a.cnvcOperName,a.cndOperDate from tbCostLog a join tbMember b on a.cnvcMemberCardNo=b.cnvcMemberCardNo";
strSql_Cost += " where 1=1 ";
string strSql_MemberProduct = "select a.cnvcProductName as cnvcMoneyType,a.cnvcMemberCardNo,b.cnvcMemberName,b.cnvcPaperNo,b.cnvcMemberRight,a.cnnPrepay,a.cnvcOperName,a.cndOperDate from tbMemberProductLog a join tbMember b on a.cnvcMemberCardNo=b.cnvcMemberCardNo ";
strSql_MemberProduct += " where 1=1 ";
string strSql_FMemberProduct = "select a.cnvcProductName as cnvcMoneyType,'' as cnvcMemberCardNo,b.cnvcMemberName,b.cnvcPaperNo,'' as cnvcMemberRight,a.cnnPrepay,a.cnvcOperName,a.cndOperDate from tbMemberProductLog a join tbFMember b on a.cnvcPaperNo=b.cnvcPaperNo ";
strSql_FMemberProduct += " where 1=1 ";
string strSql_Prepay = "select '缴费' as cnvcMoneyType,'' as cnvcMemberCardNo,c.cnvcMemberName,c.cnvcPaperNo,'' as cnvcMemberRight,"
// + " case "
// + " when a.cnvcMemberCardNo is not null then b.cnvcMemberCardNo"
// + " when a.cnvcMemberCardNo is null then '' "
// + " end cnvcMemberCardNo "
// + " , "
// + " case "
// + " when a.cnvcMemberCardNo is not null then b.cnvcMemberName "
// + " when a.cnvcMemberCardNo is null then c.cnvcMemberName "
// + " end cnvcMemberName, "
// + " case "
// + " when a.cnvcMemberCardNo is not null then b.cnvcPaperNo "
// + " when a.cnvcMemberCardNo is null then c.cnvcPaperNo "
// + " end cnvcPaperNo, "
// + " case "
// + " when a.cnvcMemberCardNo is not null then b.cnvcMemberRight "
// + " when a.cnvcMemberCardNo is null then '' "
// + " end cnvcMemberRight, "
+ " a.cnnBalance as cnnPrepay,a.cnvcOperName,a.cndOperDate "
+ " from tbPrepay a "
//+ " left join tbMember b on a.cnvcMemberCardNo=b.cnvcMemberCardNo and a.cnvcMemberCardNo is not null "
+ " left join tbFMember c on a.cnvcPaperNo=c.cnvcPaperNo and a.cnvcMemberCardNo is null ";
strSql_Prepay += " where 1=1 ";
if (cmbOperName.Text.Trim().Length > 0)
{
//strSql_Renew += " and cnvcOperName like '%"+cmbOperName.Text+"%'";
strSql_InMoney += " and a.cnvcOperName like '%" + cmbOperName.Text + "%'";
strSql_Cost += " and a.cnvcOperName like '%" + cmbOperName.Text + "%'";
strSql_MemberProduct += " and a.cnvcOperName like '%" + cmbOperName.Text + "%'";
strSql_FMemberProduct += " and a.cnvcOperName like '%" + cmbOperName.Text + "%'";
strSql_Prepay += " and a.cnvcOperName like '%" + cmbOperName.Text + "%'";
}
if (chkBeginDate.Checked)
{
//strSql_Renew += " and cndInNetDate >='"+cmbBeginDate.Value.ToString()+"'";
strSql_InMoney += " and a.cndOperDate >= '" + cmbBeginDate.Value.ToString() + "'";
strSql_Cost += " and a.cndOperDate >= '" + cmbBeginDate.Value.ToString() + "'";
strSql_MemberProduct += " and a.cndOperDate >= '" + cmbBeginDate.Value.ToString() + "'";
strSql_FMemberProduct += " and a.cndOperDate >= '" + cmbBeginDate.Value.ToString() + "'";
strSql_Prepay += " and a.cndOperDate >= '" + cmbBeginDate.Value.ToString() + "'";
}
if (chkEndDate.Checked)
{
//strSql_Renew += " and cndInNetDate <='"+cmbEndDate.Value.ToString()+"'";
strSql_InMoney += " and a.cndOperDate <= '" + cmbEndDate.Value.ToString() + "'";
strSql_Cost += " and a.cndOperDate <= '" + cmbEndDate.Value.ToString() + "'";
strSql_MemberProduct += " and a.cndOperDate <= '" + cmbEndDate.Value.ToString() + "'";
strSql_FMemberProduct += " and a.cndOperDate <= '" + cmbEndDate.Value.ToString() + "'";
strSql_Prepay += " and a.cndOperDate <= '" + cmbEndDate.Value.ToString() + "'";
}
if (cmbMember.Text == "会员")
{
strSql_Prepay += " and a.cnvcMemberCardNo is not null ";
}
if (cmbMember.Text == "非会员")
{
strSql_Prepay += " and a.cnvcMemberCardNo is null";
//strSql_Renew += " and 1<>1";
}
if (cmbMemberRight.Text.Trim().Length > 0)
{
//strSql_Renew += " and cnvcMemberRight like '%"+cmbMemberRight.Text+"%'";
strSql_InMoney += " and b.cnvcMemberRight like '%" + cmbMemberRight.Text + "%'";
strSql_Cost += " and b.cnvcMemberRight like '%" + cmbMemberRight.Text + "%'";
strSql_MemberProduct += " and b.cnvcMemberRight like '%" + cmbMemberRight.Text + "%'";
//strSql_FMemberProduct += " and b.cnvcMemberRight like '%"+cmbMemberRight.Text+"%'";
//strSql_Prepay += " and b.cnvcMemberRight like '%"+cmbMemberRight.Text+"%'";
}
DataTable dtPrepay = null;
if (cmbMember.Text.Trim().Length == 0)
{
dtPrepay = Helper.Query(strSql_InMoney);
DataTable dtCost = Helper.Query(strSql_Cost);
DataTable dtMemberProduct = Helper.Query(strSql_MemberProduct);
DataTable dtFMemberProduct = Helper.Query(strSql_FMemberProduct);
DataTable dtPay = Helper.Query(strSql_Prepay);
//DataTable dtRenew = Helper.Query(strSql_Renew);
foreach (DataRow dr in dtCost.Rows)
{
dtPrepay.Rows.Add(dr.ItemArray);
}
foreach (DataRow dr in dtMemberProduct.Rows)
{
dtPrepay.Rows.Add(dr.ItemArray);
}
foreach (DataRow dr in dtFMemberProduct.Rows)
{
dtPrepay.Rows.Add(dr.ItemArray);
}
foreach (DataRow dr in dtPay.Rows)
{
dtPrepay.Rows.Add(dr.ItemArray);
}
// foreach (DataRow dr in dtRenew.Rows)
// {
// dtPrepay.Rows.Add(dr.ItemArray);
// }
}
else if (cmbMember.Text == "会员")
{
dtPrepay = Helper.Query(strSql_InMoney);
DataTable dtCost = Helper.Query(strSql_Cost);
DataTable dtMemberProduct = Helper.Query(strSql_MemberProduct);
//DataTable dtFMemberProduct = Helper.Query(strSql_FMemberProduct);
DataTable dtPay = Helper.Query(strSql_Prepay);
//DataTable dtRenew = Helper.Query(strSql_Renew);
foreach (DataRow dr in dtCost.Rows)
{
dtPrepay.Rows.Add(dr.ItemArray);
}
foreach (DataRow dr in dtMemberProduct.Rows)
{
dtPrepay.Rows.Add(dr.ItemArray);
}
foreach (DataRow dr in dtPay.Rows)
{
dtPrepay.Rows.Add(dr.ItemArray);
}
// foreach (DataRow dr in dtRenew.Rows)
// {
// dtPrepay.Rows.Add(dr.ItemArray);
// }
}
else if (cmbMember.Text == "非会员")
{
//dtPrepay = Helper.Query(strSql_InMoney);
//DataTable dtCost= Helper.Query(strSql_Cost);
//DataTable dtMemberProduct = Helper.Query(strSql_MemberProduct);
dtPrepay = Helper.Query(strSql_FMemberProduct);
DataTable dtPay = Helper.Query(strSql_Prepay);
// foreach (DataRow dr in dtCost.Rows)
// {
// dtPrepay.Rows.Add(dr.ItemArray);
// }
// foreach (DataRow dr in dtMemberProduct.Rows)
// {
// dtPrepay.Rows.Add(dr.ItemArray);
// }
// foreach (DataRow dr in dtFMemberProduct.Rows)
// {
// dtPrepay.Rows.Add(dr.ItemArray);
// }
foreach (DataRow dr in dtPay.Rows)
{
dtPrepay.Rows.Add(dr.ItemArray);
}
}
this.ultraGrid1.DataSource = null;
this.ultraGrid1.DataSource = dtPrepay;
this.ultraGrid1.DataBind();
ClientHelper.AddGridColumn(this.ultraGrid1, this.oper.cnvcOperName);
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Count, "收入合计，共{0}条", "cnvcMemberRight");
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Sum, "{0}", "cnnPrepay");
}
catch (BusinessException bex)
{
MessageBox.Show(this, bex.Message, bex.Type, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (System.Exception ex)
{
MessageBox.Show(this, ex.Message, "系统错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnQuery_Click(object sender, System.EventArgs e)
{
try
{
//string strSql = "select cnnJobID,'' as cnvcJobName,cnvcMemberCardNo,cnvcPaperNo,'' as cnvcMemberName,cnnPrepay,cnnReturn,cnnBalance,cnvcState,cnvcOperName,cndOperDate from tbPrepay where 1=1 ";
string strSql = "select a.cnvcMemberCardNo,a.cnvcMemberName,a.cnvcPaperNo,a.cnvcMemberRight,a.cnnMemberFee,a.cnvcDiscount,a.cnvcFree as cnvcFreeBalance,a.cnnPrepay as cnnBalance,b.cnnPrepay,b.cnvcFree,a.cnvcState,a.cndEndDate,b.cnvcOperName,b.cndOperDate from tbMemberPrepayLog b"
+ " left outer join tbMember a on a.cnvcMemberCardNo = b.cnvcMemberCardNo";
//strSql += " where a.cnvcState<>'"+ConstApp.Card_State_InAdd+"' and b.cnvcOperFlag='"+ConstApp.Fee_Flag_InMoney+"'";
strSql += " where b.cnvcOperFlag='" + ConstApp.Fee_Flag_InMoney + "'";
if (txtMemberCardNo.Text.Trim().Length > 0)
{
strSql += " and a.cnvcMemberCardNo like '%" + txtMemberCardNo.Text + "%'";
}
if (txtMemberName.Text != "")
{
strSql += " and a.cnvcMemberName like '%" + txtMemberName.Text + "%'";
}
if (txtPaperNo.Text.Trim().Length > 0)
{
strSql += " and a.cnvcPaperNo like '%" + txtPaperNo.Text + "%'";
}
if (cmbOperName.Text.Trim().Length > 0)
{
strSql += " and b.cnvcOperName like '%" + cmbOperName.Text + "%'";
}
if (chkBeginDate.Checked)
{
strSql += " and b.cndOperDate >= '" + cmbBeginDate.Value.ToString() + "'";
}
if (chkEndDate.Checked)
{
strSql += " and b.cndOperDate <= '" + cmbEndDate.Value.ToString() + "'";
}
if (cmbState.Text.Trim().Length > 0)
{
strSql += " and a.cnvcState = '" + cmbState.Text + "'";
}
if (cmbMemberRight.Text.Trim().Length > 0)
{
strSql += " and a.cnvcMemberRight = '" + cmbMemberRight.Text + "'";
}
DataTable dtPrepay = Helper.Query(strSql);
this.ultraGrid1.DataSource = null;
this.ultraGrid1.DataSource = dtPrepay;
this.ultraGrid1.DataBind();
ClientHelper.AddGridColumn(this.ultraGrid1, this.oper.cnvcOperName);
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Count, "充值条数：{0}", "cnvcPaperNo");
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Sum, "充值总额：{0}", "cnnPrepay");
//Helper.AddGridSummary(this.ultraGrid1,SummaryType.Sum,"免费场次余额总额：{0}","cnvcFreeBalance");
//Helper.AddGridSummary(this.ultraGrid1,SummaryType.Sum,"缴费总额：{0}","cnnBalance");
//Helper.AddGridSummary(this.ultraGrid1,SummaryType.Sum,"场次总额：{0}","cnvcFree");
//Helper.AddGridSummary(this.ultraGrid1,SummaryType.Sum,"会员费总额：{0}","cnnMemberFee");
}
catch (BusinessException bex)
{
MessageBox.Show(this, bex.Message, bex.Type, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (System.Exception ex)
{
MessageBox.Show(this, ex.Message, "系统错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnQuery_Click(object sender, System.EventArgs e)
{
try
{
//string strSql = "select cnnJobID,'' as cnvcJobName,cnvcMemberCardNo,cnvcPaperNo,'' as cnvcMemberName,cnnPrepay,cnnReturn,cnnBalance,cnvcState,cnvcOperName,cndOperDate from tbPrepay where 1=1 ";
string strSql = "select a.cnvcOperName,a.cndOperDate,a.cnvcMemberCardNo,case when b.cnvcMemberCardNo is null then c.cnvcPaperNo "
+ " else b.cnvcPaperNo end as cnvcPaperNo,"
+ " case when b.cnvcMemberName is null then c.cnvcMemberName"
+ " when c.cnvcMemberName is null then b.cnvcMemberName else b.cnvcMemberName "
+ " end cnvcMemberName,a.cnvcFree,a.cnnPrepay,a.cnvcOperFlag"
+ " ,case a.cniSynch "
+ " when 0 then '会员系统' "
+ " when 1 then '网站操作' "
+ " when 2 then '触摸屏操作' "
+ " when 3 then '客服预定' "
+ " end cnvcSynch "
+ " from tbOperLog a"
+ " left outer join tbMember b on a.cnvcMemberCardNo=b.cnvcMemberCardNo "
+ " left outer join tbFMember c on a.cnvcPaperNo=c.cnvcPaperNo";
strSql += " where (a.cnvcMemberCardNo is null or Len(a.cnvcMemberCardNo)=6) ";
if (txtMemberCardNo.Text.Trim().Length > 0)
{
strSql += " and a.cnvcMemberCardNo is not null and a.cnvcMemberCardNo like '%" + txtMemberCardNo.Text + "%'";
}
if (txtMemberName.Text != "")
{
strSql += " and (b.cnvcMemberName like '%" + txtMemberName.Text + "%' or c.cnvcMemberName like '" + txtMemberName.Text + "')";
}
if (txtPaperNo.Text.Trim().Length > 0)
{
strSql += " and (b.cnvcPaperNo like '" + txtPaperNo.Text + "' or c.cnvcPaperNo like '%" + txtPaperNo.Text + "%')";
}
if (cmbOperName.Text.Trim().Length > 0)
{
strSql += " and a.cnvcOperName like '%" + cmbOperName.Text + "%'";
}
if (chkBeginDate.Checked)
{
strSql += " and a.cndOperDate >= '" + cmbBeginDate.Value.ToString() + "'";
}
if (chkEndDate.Checked)
{
strSql += " and a.cndOperDate <= '" + cmbEndDate.Value.ToString() + "'";
}
if (cmbSynch.Text.Trim().Length > 0)
{
strSql += " and a.cniSynch = " + cmbSynch.Value.ToString();
}
if (cmbOperFlag.Text.Trim().Length > 0)
{
strSql += " and cnvcOperFlag = '" + cmbOperFlag.Text + "'";
}
strSql += " order by a.cndOperDate ";
DataTable dtPrepay = Helper.Query(strSql);
this.ultraGrid1.DataSource = null;
this.ultraGrid1.DataSource = dtPrepay;
this.ultraGrid1.DataBind();
ClientHelper.AddGridColumn(this.ultraGrid1, this.oper.cnvcOperName);
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Count, "操作条数：{0}", "cnvcOperName");
// Helper.AddGridSummary(this.ultraGrid1,SummaryType.Sum,"充值总额：{0}","cnnPrepay");
// Helper.AddGridSummary(this.ultraGrid1,SummaryType.Sum,"免费场次余额总额：{0}","cnvcFreeBalance");
// Helper.AddGridSummary(this.ultraGrid1,SummaryType.Sum,"缴费总额：{0}","cnnBalance");
// Helper.AddGridSummary(this.ultraGrid1,SummaryType.Sum,"免费场次总额：{0}","cnvcFree");
// Helper.AddGridSummary(this.ultraGrid1,SummaryType.Sum,"会员费总额：{0}","cnnMemberFee");
}
catch (BusinessException bex)
{
MessageBox.Show(this, bex.Message, bex.Type, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (System.Exception ex)
{
MessageBox.Show(this, ex.Message, "系统错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnQuery_Click(object sender, System.EventArgs e)
{
string strSql = "select a.cnvcState,a.cnvcMemberCardNo,a.cnvcMemberName,a.cnvcPaperNo,a.cnvcJobName,cast (a.cnvcSeat as int) as cnvcSeat,a.cnvcOperName,a.cndOperDate,"
+ " case "
+ " when a.cnvcMemberCardNo is null then '' "
+ " when a.cnvcMemberCardNo is not null then b.cnvcMemberRight "
+ " end cnvcMemberRight ,"
+ " case "
+ " when a.cnvcMemberCardNo is null then c.cnvcTrade "
+ " when a.cnvcMemberCardNo is not null then b.cnvcTrade "
+ " end cnvcTrade ,"
+ " case "
+ " when a.cnvcMemberCardNo is null then c.cnvcSales "
+ " when a.cnvcMemberCardNo is not null then b.cnvcSales "
+ " end cnvcSales ,"
+ " case "
+ " when a.cnvcMemberCardNo is null then c.cnvcCustomerService "
+ " when a.cnvcMemberCardNo is not null then b.cnvcCustomerService "
+ " end cnvcCustomerService "
+ " ,case a.cniSynch "
+ " when 0 then '会员系统' "
+ " when 1 then '网站操作' "
+ " when 2 then '触摸屏操作' "
+ " end cnvcSynch "
+ " from tbMemberSeat a "
+ " left outer join tbMember b on a.cnvcMemberCardNo = b.cnvcMemberCardNo and a.cnvcMemberCardNo is not null "
+ " left outer join tbFMember c on a.cnvcPaperNo=c.cnvcPaperNo and a.cnvcMemberCardNo is null ";
strSql += " where a.cnvcState<>'" + ConstApp.Show_Seat_State_SignIn + "' and a.cnvcState<>'" + ConstApp.Show_Seat_State_Booking + "'";
if (cmbMember.Text == "会员")
{
strSql += " and a.cnvcMemberCardNo is not null";
}
if (cmbMember.Text == "非会员")
{
strSql += " and a.cnvcMemberCardNo is null";
}
if (cmbShow.SelectedItem != null)
{
strSql += " and a.cnnID=" + cmbShow.SelectedItem.DataValue.ToString();
}
if (chkBeginDate.Checked)
{
strSql += " and a.cndOperDate >='" + cmdBeginDate.Text + "'";
}
if (chkEndDate.Checked)
{
strSql += " and a.cndOperDate <='" + cmbEndDate.Text + "'";
}
if (txtMemberCardNo.Text.Trim().Length > 0)
{
strSql += " and a.cnvcMemberCardNo like '%" + txtMemberCardNo.Text + "%'";
}
if (txtMemberName.Text.Trim().Length > 0)
{
strSql += " and a.cnvcMemberName like '%" + txtMemberName.Text + "%'";
}
if (txtPaperNo.Text.Trim().Length > 0)
{
strSql += " and a.cnvcPaperNo like '%" + txtPaperNo.Text + "%'";
}
if (cmbTrade.Text.Trim().Length > 0)
{
strSql += " and (b.cnvcTrade like '%" + cmbTrade.Text + "%' or c.cnvcTrade like '%" + cmbTrade.Text + "%')";
}
if (txtSales.Text.Trim().Length > 0)
{
strSql += " and (b.cnvcSales like '%" + txtSales.Text + "%' or c.cnvcSales like '%" + txtSales.Text + "%')";
//strSql += " and cnvcSales like '%"+txtSales.Text+"%'";
}
if (txtCustomerService.Text.Trim().Length > 0)
{
strSql += " and (b.cnvcCustomerService like '%" + txtCustomerService.Text + "%' or c.cnvcCustomerService like '%" + txtCustomerService.Text + "%')";
//strSql += " and cnvcCustomerService like '%"+txtCustomerService.Text+"%'";
}
if (cmbSynch.Text.Trim().Length > 0)
{
strSql += " and a.cniSynch = " + cmbSynch.Value.ToString();
}
if (cmbMemberRight.Text.Trim().Length > 0)
{
strSql += " and b.cnvcMemberRight = '" + cmbMemberRight.Text + "'";
}
strSql += " order by cast (a.cnvcSeat as int)";
DataTable dt = Helper.Query(strSql); //query.Report(strSql);
this.ultraGrid1.DataSource = null;
this.ultraGrid1.DataSource = dt;
this.ultraGrid1.SetDataBinding(dt, null);
ClientHelper.AddGridColumn(this.ultraGrid1, this.oper.cnvcOperName);
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Count, "{0}", 0);
}
private void btnQuery_Click(object sender, System.EventArgs e)
{
//
//try
//{
//string strSql = "select cnnJobID,'' as cnvcJobName,cnvcMemberCardNo,cnvcPaperNo,'' as cnvcMemberName,cnnPrepay,cnnReturn,cnnBalance,cnvcState,cnvcOperName,cndOperDate from tbPrepay where 1=1 ";
string strSql = "select a.cnvcPaperNo,"
+ " d.cnvcMemberName,a.cnnLastBalance,a.cnnPrepay,a.cnnBalance,case when g.cnvcDeptName is null then '云南人才市场' else g.cnvcDeptName end as cnvcDeptName,a.cnvcSales,a.cnvcOperName,a.cndOperDate "
+ " from tbFMemberPrepayLog a";
//+ " left outer join tbJob b on a.cnnJobID=b.cnnJobID";
//strSql += " left outer join tbMember c on a.cnvcMemberCardNo = c.cnvcMemberCardNo";
strSql += " left outer join tbFMember d on a.cnvcPaperNo=d.cnvcPaperNo ";
strSql += " left join tbSales e on a.cnvcSales=e.cnvcSales left join tbOperDept f on e.cnnDeptID=f.cnnDeptID and e.cnnSales=f.cnnOperID left join tbDept g on e.cnnDeptID=g.cnnDeptID ";
//if (this.oper.cnnDeptID != 0)
//{
// strSql += string.Format(" where f.cnnOperID={0} ",this.oper.cnnOperID.ToString());
//}
//else
//{
strSql += " where 1=1";
//}
//strSql += " where 1=1";
//if (cmbShow.Text.Trim().Length > 0)
//{
// strSql += " and b.cnvcJobName like '%"+cmbShow.Text+"%'";
//}
// if (txtMemberCardNo.Text.Trim().Length > 0)
// {
// strSql += " and c.cnvcMemberCardNo like '%"+txtMemberCardNo.Text+"%'";
// }
if (txtMemberName.Text != "")
{
strSql += " and d.cnvcMemberName like '%" + txtMemberName.Text + "%'";
}
if (txtPaperNo.Text.Trim().Length > 0)
{
strSql += " and d.cnvcPaperNo like '%" + txtPaperNo.Text + "%'";
}
if (cmbOperName.Text.Trim().Length > 0)
{
strSql += " and a.cnvcOperName like '%" + cmbOperName.Text + "%'";
}
if (chkBeginDate.Checked)
{
strSql += " and a.cndOperDate >= '" + cmbBeginDate.Value.ToString() + "'";
}
if (chkEndDate.Checked)
{
strSql += " and a.cndOperDate <= '" + cmbEndDate.Value.ToString() + "'";
}
//if (cmbFeeDirection.Text.Trim()=="缴费")
//{
// strSql += "and a.cnnReturn =0 ";
//}
//else if (cmbFeeDirection.Text.Trim()=="退费")
//{
// strSql += "and a.cnnReturn >0 ";
//}
if (cmbSales.Text.Trim().Length > 0)
{
strSql += " and a.cnvcSales='" + cmbSales.Text + "'";
}
if (cmbDept.Text.Trim().Length > 0)
{
strSql += " and g.cnvcDeptName='" + cmbDept.Text + "'";
}
DataTable dtPrepay = Helper.Query(strSql);
this.ultraGrid1.DataSource = null;
this.ultraGrid1.DataSource = dtPrepay;
this.ultraGrid1.DataBind();
ClientHelper.AddGridColumn(this.ultraGrid1, this.oper.cnvcOperName);
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Count, "会员数量：{0}", "cnvcPaperNo");
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Sum, "充值总额：{0}", "cnnPrepay");
//Helper.AddGridSummary(this.ultraGrid1,SummaryType.Sum,"退费总额：{0}","cnnReturn");
//Helper.AddGridSummary(this.ultraGrid1,SummaryType.Sum,"余额总额：{0}","cnnBalance");
//}
//catch (BusinessException bex)
//{
// MessageBox.Show(this, bex.Message, bex.Type,MessageBoxButtons.OK,MessageBoxIcon.Error);
//}
//catch (System.Exception ex)
//{
// MessageBox.Show(this,ex.Message,"系统错误",MessageBoxButtons.OK,MessageBoxIcon.Error);
//}
}
private void btnQuery_Click(object sender, System.EventArgs e)
{
//
try
{
//string strSql = "select cnnJobID,'' as cnvcJobName,cnvcMemberCardNo,cnvcPaperNo,'' as cnvcMemberName,cnnPrepay,cnnReturn,cnnBalance,cnvcState,cnvcOperName,cndOperDate from tbPrepay where 1=1 ";
string strSql = "select a.cnnPrepayID,a.cnnJobID,b.cnvcJobName,a.cnvcPaperNo,"
+ " d.cnvcMemberName,a.cnnPrepay,a.cnnReturn,a.cnnBalance,a.cnvcState,a.cnvcOperName,a.cndOperDate "
+ " from tbPrepay a"
+ " left outer join tbJob b on a.cnnJobID=b.cnnJobID";
//strSql += " left outer join tbMember c on a.cnvcMemberCardNo = c.cnvcMemberCardNo";
strSql += " left outer join tbFMember d on a.cnvcPaperNo=d.cnvcPaperNo and a.cnvcMemberCardNo is null";
strSql += " where 1=1";
if (cmbShow.Text.Trim().Length > 0)
{
strSql += " and b.cnvcJobName like '%" + cmbShow.Text + "%'";
}
// if (txtMemberCardNo.Text.Trim().Length > 0)
// {
// strSql += " and c.cnvcMemberCardNo like '%"+txtMemberCardNo.Text+"%'";
// }
if (txtMemberName.Text != "")
{
strSql += " and d.cnvcMemberName like '%" + txtMemberName.Text + "%'";
}
if (txtPaperNo.Text.Trim().Length > 0)
{
strSql += " and d.cnvcPaperNo like '%" + txtPaperNo.Text + "%'";
}
if (cmbOperName.Text.Trim().Length > 0)
{
strSql += " and a.cnvcOperName like '%" + cmbOperName.Text + "%'";
}
if (chkBeginDate.Checked)
{
strSql += " and a.cndOperDate >= '" + cmbBeginDate.Value.ToString() + "'";
}
if (chkEndDate.Checked)
{
strSql += " and a.cndOperDate <= '" + cmbEndDate.Value.ToString() + "'";
}
if (cmbFeeDirection.Text.Trim() == "缴费")
{
strSql += "and a.cnnReturn =0 ";
}
else if (cmbFeeDirection.Text.Trim() == "退费")
{
strSql += "and a.cnnReturn >0 ";
}
DataTable dtPrepay = Helper.Query(strSql);
this.ultraGrid1.DataSource = null;
this.ultraGrid1.DataSource = dtPrepay;
this.ultraGrid1.DataBind();
ClientHelper.AddGridColumn(this.ultraGrid1, this.oper.cnvcOperName);
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Count, "会员数量：{0}", "cnvcPaperNo");
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Sum, "展位费总额：{0}", "cnnPrepay");
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Sum, "退费总额：{0}", "cnnReturn");
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Sum, "余额总额：{0}", "cnnBalance");
}
catch (BusinessException bex)
{
MessageBox.Show(this, bex.Message, bex.Type, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (System.Exception ex)
{
MessageBox.Show(this, ex.Message, "系统错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnQuery_Click(object sender, System.EventArgs e)
{
try
{
string strSql = "select a.cnnSerialNo,a.cnvcMemberCardNo,a.cnvcPaperNo,a.cnvcMemberName,a.cnvcProductName,a.cnnProductPrice,a.cnvcProductDiscount,a.cnnCount,a.cnnLastBalance,a.cnnPrepay,a.cnnBalance,a.cnvcFree,case when e.cnvcDeptName is null then '云南人才市场' else e.cnvcDeptName end as cnvcDeptName,a.cnvcSales,a.cnvcOperName,a.cndOperDate from tbMemberProductLog a ";
//strSql += " left join tbOper c on a.cnvcOperName=c.cnvcOperName left join tbDept d on c.cnnDeptID=d.cnnDeptID ";
strSql += " left join tbSales c on a.cnvcSales=c.cnvcSales left join tbOperDept d on c.cnnDeptID=d.cnnDeptID and c.cnnSales=d.cnnOperID left join tbDept e on c.cnnDeptID=e.cnnDeptID";
//if (this.oper.cnnDeptID != 0)
//{
// //strSql += " where (d.cnnParentDeptID=" + this.oper.cnnDeptID.ToString() + " or d.cnnDeptID=" + this.oper.cnnDeptID.ToString() + ") ";
// strSql += string.Format(" where d.cnnOperID={0} ", this.oper.cnnOperID.ToString());
//}
//else
//{
strSql += " where 1=1 ";
//}
//strSql += " where 1=1";
if (txtMemberCardNo.Text.Trim().Length > 0)
{
strSql += " and a.cnvcMemberCardNo like '%" + txtMemberCardNo.Text + "%'";
}
if (txtMemberName.Text != "")
{
strSql += " and a.cnvcMemberName like '%" + txtMemberName.Text + "%'";
}
if (txtPaperNo.Text.Trim().Length > 0)
{
strSql += " and a.cnvcPaperNo like '%" + txtPaperNo.Text + "%'";
}
if (cmbOperName.Text.Trim().Length > 0)
{
strSql += " and a.cnvcOperName like '%" + cmbOperName.Text + "%'";
}
if (chkBeginDate.Checked)
{
strSql += " and a.cndOperDate >= '" + cmbBeginDate.Value.ToString() + "'";
}
if (chkEndDate.Checked)
{
strSql += " and a.cndOperDate <= '" + cmbEndDate.Value.ToString() + "'";
}
if (cmbProduct.Text.Trim().Length > 0)
{
strSql += " and a.cnvcProductName like '%" + cmbProduct.Text + "%'";
}
if (cmbSales.Text.Trim().Length > 0)
{
strSql += " and a.cnvcSales like '%" + cmbSales.Text + "%'";
}
if (cmbDept.Text.Trim().Length > 0)
{
strSql += " and e.cnvcDeptName like '%" + cmbDept.Text + "%'";
}
string strSql2 = "select a.cnnSerialNo,'' as cnvcMemberCardNo,a.cnvcPaperNo,a.cnvcMemberName,a.cnvcProductName,a.cnnProductPrice,a.cnvcProductDiscount,a.cnnCount,a.cnnLastBalance,a.cnnPrepay,a.cnnBalance,a.cnvcFree,case when e.cnvcDeptName is null then '云南人才市场' else e.cnvcDeptName end as cnvcDeptName,a.cnvcSales,a.cnvcOperName,a.cndOperDate from tbFMemberProductLog a ";
//strSql2 += " left join tbOper c on a.cnvcOperName=c.cnvcOperName left join tbDept d on c.cnnDeptID=d.cnnDeptID ";
strSql2 += " left join tbSales c on a.cnvcSales=c.cnvcSales left join tbOperDept d on c.cnnDeptID=d.cnnDeptID and c.cnnSales=d.cnnOperID left join tbDept e on c.cnnDeptID=e.cnnDeptID";
//if (this.oper.cnnDeptID != 0)
//{
// //strSql2 += " where (d.cnnParentDeptID=" + this.oper.cnnDeptID.ToString() + " or d.cnnDeptID=" + this.oper.cnnDeptID.ToString() + ") ";
// strSql2 += string.Format(" where d.cnnOperID={0} ", this.oper.cnnOperID.ToString());
//}
//else
//{
strSql2 += " where 1=1 ";
//}
//strSql2 += " where 1=1";
// if (txtMemberCardNo.Text.Trim().Length > 0)
// {
// strSql2 += " and cnvcMemberCardNo like '%"+txtMemberCardNo.Text+"%'";
// }
if (txtMemberName.Text != "")
{
strSql2 += " and a.cnvcMemberName like '%" + txtMemberName.Text + "%'";
}
if (txtPaperNo.Text.Trim().Length > 0)
{
strSql2 += " and a.cnvcPaperNo like '%" + txtPaperNo.Text + "%'";
}
if (cmbOperName.Text.Trim().Length > 0)
{
strSql2 += " and a.cnvcOperName like '%" + cmbOperName.Text + "%'";
}
if (chkBeginDate.Checked)
{
strSql2 += " and a.cndOperDate >= '" + cmbBeginDate.Value.ToString() + "'";
}
if (chkEndDate.Checked)
{
strSql2 += " and a.cndOperDate <= '" + cmbEndDate.Value.ToString() + "'";
}
if (cmbProduct.Text.Trim().Length > 0)
{
strSql2 += " and a.cnvcProductName like '%" + cmbProduct.Text + "%'";
}
if (cmbSales.Text.Trim().Length > 0)
{
strSql2 += " and a.cnvcSales like '%" + cmbSales.Text + "%'";
}
if (cmbDept.Text.Trim().Length > 0)
{
strSql2 += " and e.cnvcDeptName like '%" + cmbDept.Text + "%'";
}
DataTable dtPrepay = new DataTable();
if (cmbMember.Text.Trim().Length == 0)
{
dtPrepay = Helper.Query(strSql);
DataTable dtPrepay2 = Helper.Query(strSql2);
foreach (DataRow dr in dtPrepay2.Rows)
{
dtPrepay.Rows.Add(dr.ItemArray);
}
}
else if (cmbMember.Text == "会员")
{
dtPrepay = Helper.Query(strSql);
}
else if (cmbMember.Text == "非会员")
{
dtPrepay = Helper.Query(strSql2);
}
this.ultraGrid1.DataSource = null;
this.ultraGrid1.DataSource = dtPrepay;
this.ultraGrid1.DataBind();
ClientHelper.AddGridColumn(this.ultraGrid1, this.oper.cnvcOperName);
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Count, "服务产品消费次数：{0}", "cnvcMemberCardNo");
}
catch (BusinessException bex)
{
MessageBox.Show(this, bex.Message, bex.Type, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (System.Exception ex)
{
MessageBox.Show(this, ex.Message, "系统错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnQuery_Click(object sender, System.EventArgs e)
{
try
{
//string strSql = "select cnnJobID,'' as cnvcJobName,cnvcMemberCardNo,cnvcPaperNo,'' as cnvcMemberName,cnnPrepay,cnnReturn,cnnBalance,cnvcState,cnvcOperName,cndOperDate from tbPrepay where 1=1 ";
string strSql = "select a.cnvcMemberCardNo,a.cnvcMemberName,a.cnvcPaperNo,a.cnvcMemberRight,a.cnnMemberFee,a.cnvcDiscount,b.cnvcOperFlag,a.cnvcFree as cnvcFreeBalance,b.cnnLastBalance,b.cnnPrepay,b.cnnDonate,b.cnnBalance,b.cnvcFree,a.cnvcState,a.cndEndDate,case when e.cnvcDeptName is null then '云南人才市场' else e.cnvcDeptName end as cnvcDeptName,b.cnvcSales,b.cnvcOperName,b.cndOperDate from tbMemberPrepayLog b"
+ " left outer join tbMember a on a.cnvcMemberCardNo = b.cnvcMemberCardNo";
//strSql += " where a.cnvcState<>'"+ConstApp.Card_State_InAdd+"' and b.cnvcOperFlag='"+ConstApp.Fee_Flag_InMoney+"'";
//strSql += " left join tbOper c on b.cnvcOperName=c.cnvcOperName left join tbDept d on c.cnnDeptID=d.cnnDeptID ";
strSql += " left join tbSales c on b.cnvcSales=c.cnvcSales left join tbOperDept d on c.cnnDeptID=d.cnnDeptID and c.cnnSales=d.cnnOperID left join tbDept e on c.cnnDeptID=e.cnnDeptID";
//if (this.oper.cnnDeptID != 0)
//{
// //strSql += " where (d.cnnParentDeptID=" + this.oper.cnnDeptID.ToString() + " or d.cnnDeptID=" + this.oper.cnnDeptID.ToString() + ") and ";
// strSql += string.Format(" where d.cnnOperID={0} and ", this.oper.cnnOperID.ToString());
//}
//else
//{
strSql += " where ";
//}
strSql += " LEN(b.cnvcMemberCardNo)=6 ";
if (txtMemberCardNo.Text.Trim().Length > 0)
{
strSql += " and a.cnvcMemberCardNo like '%" + txtMemberCardNo.Text + "%'";
}
if (txtMemberName.Text != "")
{
strSql += " and a.cnvcMemberName like '%" + txtMemberName.Text + "%'";
}
if (txtPaperNo.Text.Trim().Length > 0)
{
strSql += " and a.cnvcPaperNo like '%" + txtPaperNo.Text + "%'";
}
if (cmbOperName.Text.Trim().Length > 0)
{
strSql += " and b.cnvcOperName like '%" + cmbOperName.Text + "%'";
}
if (chkBeginDate.Checked)
{
strSql += " and b.cndOperDate >= '" + cmbBeginDate.Value.ToString() + "'";
}
if (chkEndDate.Checked)
{
strSql += " and b.cndOperDate <= '" + cmbEndDate.Value.ToString() + "'";
}
if (cmbState.Text.Trim().Length > 0)
{
strSql += " and a.cnvcState = '" + cmbState.Text + "'";
}
if (cmbMemberRight.Text.Trim().Length > 0)
{
strSql += " and a.cnvcMemberRight = '" + cmbMemberRight.Text + "'";
}
if (cmbSales.Text.Trim().Length > 0)
{
strSql += " and b.cnvcSales='" + cmbSales.Text + "'";
}
if (cmbDept.Text.Trim().Length > 0)
{
strSql += " and e.cnvcDeptName='" + cmbDept.Text + "'";
}
if (cmbOperType.Text.Trim().Length > 0)
{
strSql += " and b.cnvcOperFlag='" + cmbOperType.Text + "'";
}
DataTable dtPrepay = Helper.Query(strSql);
this.ultraGrid1.DataSource = null;
this.ultraGrid1.DataSource = dtPrepay;
this.ultraGrid1.DataBind();
ClientHelper.AddGridColumn(this.ultraGrid1, this.oper.cnvcOperName);
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Count, "充值条数：{0}", "cnvcPaperNo");
Helper.AddGridSummary(this.ultraGrid1, SummaryType.Sum, "充值总额：{0}", "cnnPrepay");
//Helper.AddGridSummary(this.ultraGrid1,SummaryType.Sum,"免费场次余额总额：{0}","cnvcFreeBalance");
//Helper.AddGridSummary(this.ultraGrid1,SummaryType.Sum,"缴费总额：{0}","cnnBalance");
//Helper.AddGridSummary(this.ultraGrid1,SummaryType.Sum,"场次总额：{0}","cnvcFree");
//Helper.AddGridSummary(this.ultraGrid1,SummaryType.Sum,"会员费总额：{0}","cnnMemberFee");
}
catch (BusinessException bex)
{
MessageBox.Show(this, bex.Message, bex.Type, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (System.Exception ex)
{
MessageBox.Show(this, ex.Message, "系统错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}