using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Runtime.InteropServices; using gregn6Lib; using DBUtility; using SQLHelper; namespace PayM { public partial class Pay_MonthWriteBillReport : Form //简单报表－直接有一个存储过程＋简单过滤，返回生成报表 { public Pay_MonthWriteBillReport() { InitializeComponent(); } public string ModName = ""; public string ModCaption = "充值统计表"; Pub_Class.ClsGridListSum oSumGrid = new Pub_Class.ClsGridListSum(); #region 固定代码 private void initGrid() { DBUtility.Xt_BaseBillFun.initGridRpt(grdMain, this.Name); } //得到对应列 private Int32 Fun_GetCol(string sCol) { return DBUtility.Xt_BaseBillFun.Fun_GetCol(sCol, grdMain); } private void tc_Click(object sender, EventArgs e) { this.Close(); } public Pay_MonthWriteBillReportDlg oDlg = new Pay_MonthWriteBillReportDlg(); private void cx_Click(object sender, EventArgs e) { oDlg.ShowDialog(); if (oDlg.IsOk == 1) { timer1.Enabled = true; } } //设置报表名称 private void SetFormCaption() { this.Text = ModCaption; lblCaption.Text = this.Text; initGrid();//网格标题初始化 initHeadCaption();//表头初始化 } private void Frm_EasyReport_Load(object sender, EventArgs e) { cmbYear.Items.Clear(); cmbYear.Items.Add(DateTime.Today.Year - 4); cmbYear.Items.Add(DateTime.Today.Year - 3); cmbYear.Items.Add(DateTime.Today.Year - 2); cmbYear.Items.Add(DateTime.Today.Year - 1); cmbYear.Items.Add(DateTime.Today.Year); cmbYear.SelectedIndex = 4; cmbPeriod.Text = DateTime.Today.Month.ToString(); SetFormCaption(); oSumGrid.ogrdMain = grdMain; //初始化 new oSumGrid.oGridsum = grdSum; } private void timer1_Tick(object sender, EventArgs e) { timer1.Enabled = false; if (Fun_AllowQuery()) { initGrid(); Xt_Wait oFrm = new Xt_Wait(); oFrm.Show(); System.Windows.Forms.Application.DoEvents(); Display(); System.Windows.Forms.Application.DoEvents(); oFrm.Close(); oFrm.Dispose(); } } private void Pay_MonthWriteBillReport_KeyUp(object sender, KeyEventArgs e) { if (e.KeyValue == 13) timer1.Enabled = true; } private void mrlk_Click(object sender, EventArgs e) { DBUtility.Xt_BaseBillFun.DefaultGridView(grdMain, this.Name); } private void bclk_Click(object sender, EventArgs e) { //保存列宽 DBUtility.Xt_BaseBillFun.SaveGrid(grdMain, this.Name); } //打印 private void dy_Click(object sender, EventArgs e) { Sub_SetReport(); Report.Print(true); } //预览 private void view_Click(object sender, EventArgs e) { Sub_SetReport(); Report.PrintPreview(true); } #endregion #region 界面处理 //初始化表头显示 private void initHeadCaption() { } private bool Fun_AllowQuery()//审核查询条件 { //if (txtHBDeptID.Text.Trim().Length == 0) //{ // MessageBox.Show(this, "请选择开始部门！", "提示"); // return false; //} return true; } private void Display() { SQLHelper.ClsCN SubCn = new SQLHelper.ClsCN(); DataSet DSet = new DataSet(); //显示报表头 标题 //过滤条件Dlg string sDlgWhere = ""; sDlgWhere = sDlgWhere + ",'" + oDlg.cmbYear.Text + "'"; sDlgWhere = sDlgWhere + "," + oDlg.cmbPeriod.Text.Trim(); sDlgWhere = sDlgWhere + ",'" + oDlg.txtHDeptID.Text + "'"; sDlgWhere = sDlgWhere + ",'" + oDlg.txtHEDeptID.Text + "'"; //sDlgWhere = sDlgWhere + ",'" + oDlg.txtHGroup.Text + "'"; //sDlgWhere = sDlgWhere + ",'" + oDlg.txtHEGroup.Text + "'"; if (sDlgWhere.Trim().Length > 0) { sDlgWhere = sDlgWhere.Substring(1, sDlgWhere.Length - 1); } //过滤条件 //string sWhere = ""; //sWhere = sWhere + ",'" + this.dtpHBDate.Value.ToShortDateString() + "'"; //sWhere = sWhere + ",'" + this.dtpHEDate.Value.ToShortDateString() + "'"; //sWhere = sWhere + ",1,3"; ////sWhere = sWhere + ",'" + frmQuery.dtpHBakEndDate.Value.ToShortDateString() + "'"; ////sWhere = sWhere + "," + frmQuery.txtHCusID.Tag.ToString().Trim(); //if (sWhere.Trim().Length > 0) //{ // sWhere = sWhere.Substring(1, sWhere.Length - 1); //} // DSet = SubCn.RunProcReturn("exec h_p_Pay_MonthWriteBillReport " + sDlgWhere, "Gy_Czygl", ref ClsPub.sExeReturnInfo); //生成首行标题 if (DSet == null) { MessageBox.Show("没有返回任何结果,尝试再次查询！" + DBUtility.ClsPub.sExeReturnInfo); DBUtility.ClsPub.sExeReturnInfo = ""; return; } // grdMain.DataSource = DSet.Tables[0].DefaultView; //设置合计列 string sTotalCol = ""; sTotalCol = DBUtility.Gy_BaseFun.GetTotalCols(DSet); string[] sT; sT = sTotalCol.Split(Convert.ToChar(",")); oSumGrid.BuildTotalCols(sT); // DBUtility.Xt_BaseBillFun.DisplayGrid(grdMain, this.Name, "是", 0); // DBUtility.Xt_BaseBillFun.GetGrid(grdMain, this.Name); Total(); } #endregion #region 打印设置 //打印设置 GridppReport Report; //设置打印模版路径 private void Sub_SetReport() { Report = new GridppReport(); Report.LoadFromFile(ClsPub.AppPath + @"\Report\应收款明细表.grf"); //here . Report.BeforePostRecord += new _IGridppReportEvents_BeforePostRecordEventHandler(ReportBeforePostRecord); Report.FetchRecord += new _IGridppReportEvents_FetchRecordEventHandler(ReportFetchRecordByDataTable); } //填入单据表头信息 private void ReportBeforePostRecord()//your report?kao { //Report.FieldByName("HDateItemID").AsString = lblHDateItemID.Text; //Report.FieldByName("CusNumber").AsString = lblHCusNumber.Text; //Report.FieldByName("CusName").AsString = lblHCusName.Text; //Report.FieldByName("BeginBalance").AsString = lblHBeginBalance.Text; } //填入单据明细信息 private void ReportFetchRecordByDataTable() { //BLL.Utility.FillRecordToReport(Report, grdMain, false); } #endregion #region 数据导出 private void yc_Click(object sender, EventArgs e) { DBUtility.Gy_BaseFun.DataToExcel(this.Text, grdMain); } #endregion private void timer2_Tick(object sender, EventArgs e) { timer2.Enabled = false; oDlg.ShowDialog(); if (oDlg.IsOk == 1) { timer1.Enabled = true; } } private void Total() { if (grdMain.Rows.Count > 0 && grdMain.ColumnCount > 0) { oSumGrid.SetGridsum(); oSumGrid.Total(); } } private void grdMain_CellMouseUp(object sender, DataGridViewCellMouseEventArgs e) { Total(); } private void grdMain_ColumnWidthChanged(object sender, DataGridViewColumnEventArgs e) { Total(); } private void grdMain_Paint(object sender, PaintEventArgs e) { DBUtility.Xt_BaseBillFun.GraphicsGrid(grdMain); } private void button1_Click(object sender, EventArgs e) { int HYear = ClsPub.isInt(cmbYear.Text); int HPeriod = ClsPub.isInt(cmbPeriod.Text); //月初日期 DateTime KSRQ = ClsPub.isDate(HYear + "-" + HPeriod + "-01"); //最后日期 DateTime ZHRQ = ClsPub.isDate(HYear + "-" + HPeriod + "-01").AddMonths(1); SQLHelper.ClsCN oCn1 = new SQLHelper.ClsCN(); DataSet DSEmp = new DataSet();//职员 DataSet DSXFFlag = new DataSet();//结账标记 SQLHelper.ClsCNK32 oCn2 = new SQLHelper.ClsCNK32(); DataSet DSet2 = new DataSet();//月末余额 DataSet DSet3 = new DataSet();//月初余额 if (MessageBox.Show("是否计算" + HYear + "年" + HPeriod + "月充值金额？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK) { DSXFFlag = oCn1.RunProcReturn("select HXFEndFlag from Xt_AccountPeriod where HYear=" + HYear + " and HPeriod=" + HPeriod, "Xt_AccountPeriod"); if (ClsPub.isInt(DSXFFlag.Tables[0].Rows[0][0]) == 0) { //删除本月月度充值汇总表 oCn1.RunProc("delete Pay_MonthWriteBill where hyear=" + HYear + " and hperiod=" + HPeriod); //删除本月 扣补项目费用单子表 //oCn1.RunProc("delete b from Pay_DuSubsidyItemBillMain a inner join Pay_DuSubsidyItemBillSub b on a.hinterid=b.hinterid where a.hyear=" + HYear + " and a.hperiod=" + HPeriod + " and b.HDuSubsidyItemID=2"); DSEmp = oCn1.RunProcReturn("select e.hitemid,e.hnumber,e.hname,isnull(b.hmoney,0) hmoney from h_v_K3_Employee e left join " + "(select HEmpCode,HEmpName,sum(HMoney) HMoney from " + " Pay_MonthSubsidyEntryBill Where HYear=" + HYear.ToString() + " and HPeriod=" + HPeriod + " Group by HEmpCode,HEmpName) b on e.hnumber=b.hempcode ", "h_v_K3_Employee"); if (DSEmp.Tables[0].Rows.Count > 0) { p1.Maximum = DSEmp.Tables[0].Rows.Count; p1.Value = 1; double HSubsidy = 0; //补助 double HMoney = 0; //充值金额 long HEmpID = 0; //职员ID string HEmpNumber = ""; //职员代码 string HEmpName = ""; //职员名称 double HKSYuE = 0; //月初余额 double HYuE = 0; //余额 double HChaJia = 0; //差价 for (int i = 0; i < DSEmp.Tables[0].Rows.Count; i++) { HEmpID = ClsPub.isLong(DSEmp.Tables[0].Rows[i]["hitemid"]); HSubsidy = ClsPub.isDoule(DSEmp.Tables[0].Rows[i]["hmoney"]) * 100; HEmpNumber = ClsPub.isStrNull(DSEmp.Tables[0].Rows[i]["hnumber"]); HEmpName = ClsPub.isStrNull(DSEmp.Tables[0].Rows[i]["hname"]); //月末余额 //DSet2 = oCn2.RunProcReturn("select b.xh,b.save_money from dt_user a inner join xf_mx b on a.user_serial=b.user_serial where user_no='" + HEmpNumber + "' and jl_sj<'" + ZHRQ + "' order by b.xh desc", "dt_ac_link"); DSet2 = oCn2.RunProcReturn("h_p_Pay_GetCardLeftMoney_End '" + HEmpNumber + "', '" + HEmpName + "','" + ZHRQ + "'", "h_p_Pay_GetCardLeftMoney"); //月初余额 //DSet3 = oCn2.RunProcReturn("select b.xh,b.save_money from dt_user a inner join xf_mx b on a.user_serial=b.user_serial where user_no='" + HEmpNumber + "' and jl_sj<'" + KSRQ + "' order by b.xh desc", "dt_ac_link"); DSet3 = oCn2.RunProcReturn("h_p_Pay_GetCardLeftMoney '" + HEmpNumber + "', '" + HEmpName + "','" + KSRQ + "'", "h_p_Pay_GetCardLeftMoney"); if (DSet2.Tables[0].Rows.Count > 0) { if (DSet3.Tables[0].Rows.Count > 0) { HKSYuE = ClsPub.isDoule(DSet3.Tables[0].Rows[0]["save_money"]); } HYuE = ClsPub.isDoule(DSet2.Tables[0].Rows[0]["save_money"]); if (HYuE + HSubsidy >= 100000) { HMoney = HSubsidy; //月度充值汇总表新增记录 oCn1.RunProc("insert into Pay_MonthWriteBill (HYear,HPeriod,HDeptCode,HDate" + ",HMaker,HMakeDate,HRemark,HEmpCode,HExpenseType,HQty,HPrice,HMoney,HLeftMoney,HSubsidy,HDeuct,HPreLeftMoney)" + " values(" + HYear + "," + HPeriod + ",'','" + DateTime.Today + "'" + ",'" + DBUtility.ClsPub.CurUserName + "','" + DateTime.Today + "','','" + HEmpNumber + "','补助',1," + HMoney + "," + HMoney + "," + HYuE + "," + HSubsidy + ",0," + HKSYuE + ")"); } else { HChaJia = 100000 - HYuE - HSubsidy; HMoney = HSubsidy + HChaJia; // //得到mainid Int64 HInterID = DBUtility.ClsPub.CreateBillID("2233", ref DBUtility.ClsPub.sExeReturnInfo); string HBillNo = ClsPub.CreateBillCode("2233", ref ClsPub.sExeReturnInfo, true);//得到新单号 //扣补项目费用单 主表 //oCn1.RunProc("Insert Into Pay_DuSubsidyItemBillMain " + //"(HBillType,HBillSubType,HInterID,HBillNo,HDate" + //",HYear,HPeriod,HRemark,HMaker,HMakeDate" + //",HDeptID,HGroupID,HExplanation,HInnerBillNo,HAutoSaveFlag" + //") " + //" values('2233','2233'," + HInterID.ToString() + ",'" + HBillNo + "',getdate()" + //", " + HYear.ToString() + "," + HPeriod.ToString() + ",'','" + DBUtility.ClsPub.CurUserName + "',getdate()" + //", 0,0,'','',0" + //") "); ////扣补项目费用单 子表 //oCn1.RunProc("Insert into Pay_DuSubsidyItemBillSub " + // " (HInterID,HEntryID,HCloseMan,HEntryCloseDate,HCloseType,HRemark" + // ",HSourceInterID,HSourceEntryID,HSourceBillNo,HSourceBillType,HRelationQty,HRelationMoney" + // ",HEmpID,HDuSubsidyItemID,HQty,HPrice,HMoney" + // ") values(" // + HInterID.ToString() + ",0,'','',0,''" + // ",0,0,'','',0,0" + // "," + HEmpID.ToString() + ",2,1," + HChaJia.ToString() + "," + HChaJia.ToString() + // ") "); // //月度充值汇总表新增记录 oCn1.RunProc("insert into Pay_MonthWriteBill (HYear,HPeriod,HDeptCode,HDate" + ",HMaker,HMakeDate,HRemark,HEmpCode,HExpenseType,HQty,HPrice,HMoney,HLeftMoney,HSubsidy,HDeuct,HPreLeftMoney)" + " values(" + HYear + "," + HPeriod + ",'','" + DateTime.Today + "'" + ",'" + DBUtility.ClsPub.CurUserName + "','" + DateTime.Today + "','','" + HEmpNumber + "','补助',1," + HMoney + "," + HMoney + "," + HYuE + "," + HSubsidy + "," + HChaJia + "," + HKSYuE + ")"); } } System.Windows.Forms.Application.DoEvents(); if (p1.Value == p1.Maximum) { p1.Value = p1.Value; } else { p1.Value = p1.Value + 1; } System.Windows.Forms.Application.DoEvents(); } MessageBox.Show("月度充值完毕！"); Display(); } } else { MessageBox.Show("本月消费已结账，不能月度充值！"); } } } private void button2_Click(object sender, EventArgs e) { int HYear = ClsPub.isInt(cmbYear.Text); int HPeriod = ClsPub.isInt(cmbPeriod.Text); SQLHelper.ClsCN oCn1 = new SQLHelper.ClsCN(); DataSet DSEmp = new DataSet();//职员 DataSet DSXFFlag = new DataSet();//结账标记 SQLHelper.ClsCNK32 oCn2 = new SQLHelper.ClsCNK32(); DataSet DSet2 = new DataSet();//余额 if (MessageBox.Show("是否充值" + HYear + "年" + HPeriod + "月金额？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK) { DSXFFlag = oCn1.RunProcReturn("select HXFEndFlag from Xt_AccountPeriod where HYear=" + HYear + " and HPeriod=" + HPeriod, "Xt_AccountPeriod"); if (ClsPub.isInt(DSXFFlag.Tables[0].Rows[0][0]) == 0) { DSEmp = oCn1.RunProcReturn("select hitemid,HEmpCode,HMoney from Pay_MonthWriteBill where hyear=" + HYear + " and hperiod=" + HPeriod + " and hwriteflag=0", "Pay_MonthWriteBill"); if (DSEmp.Tables[0].Rows.Count > 0) { p1.Maximum = DSEmp.Tables[0].Rows.Count; p1.Value = 1; long HItemid = 0; string HEmpNumber = ""; double HMoney = 0; for (int i = 0; i < DSEmp.Tables[0].Rows.Count; i++) { HItemid = ClsPub.isLong(DSEmp.Tables[0].Rows[i]["hitemid"]); HEmpNumber = ClsPub.isStrNull(DSEmp.Tables[0].Rows[i]["HEmpCode"]); HMoney = ClsPub.isDoule(DSEmp.Tables[0].Rows[i]["HMoney"]); if (HMoney > 0) { //充值 oCn2.RunProc("h_p_Pay_MonthWriteBill '" + HEmpNumber + "'," + HMoney); } //打上充值标记 oCn1.RunProc("update Pay_MonthWriteBill set HWriteFlag=1 where HItemid=" + HItemid); System.Windows.Forms.Application.DoEvents(); if (p1.Value == p1.Maximum) { p1.Value = p1.Value; } else { p1.Value = p1.Value + 1; } System.Windows.Forms.Application.DoEvents(); } MessageBox.Show("卡充值完毕！"); Display(); } } else { MessageBox.Show("本月消费已结账，不能卡充值！"); } } } private void mx_Click(object sender, EventArgs e) { int HYear = 0; int HPeriod = 0; string HEmpNumber = ""; if (grdMain.CurrentRow == null) return; HYear = DBUtility.ClsPub.isInt(grdMain.Rows[grdMain.CurrentRow.Index].Cells[Fun_GetCol("年")].Value); HPeriod = DBUtility.ClsPub.isInt(grdMain.Rows[grdMain.CurrentRow.Index].Cells[Fun_GetCol("月")].Value); HEmpNumber = DBUtility.ClsPub.isStrNull(grdMain.Rows[grdMain.CurrentRow.Index].Cells[Fun_GetCol("员工代码")].Value); if (HEmpNumber == "") return; Pay_MonthWriteBillReportMX oMX = new Pay_MonthWriteBillReportMX(); oMX.HYear = HYear; oMX.HPeriod = HPeriod; oMX.HEmpNumber = HEmpNumber; oMX.ShowDialog(); } private void bt_Click(object sender, EventArgs e) { int HYear = 0; int HPeriod = 0; string HEmpNumber = ""; if (grdMain.CurrentRow == null) return; HYear = DBUtility.ClsPub.isInt(grdMain.Rows[grdMain.CurrentRow.Index].Cells[Fun_GetCol("年")].Value); HPeriod = DBUtility.ClsPub.isInt(grdMain.Rows[grdMain.CurrentRow.Index].Cells[Fun_GetCol("月")].Value); HEmpNumber = DBUtility.ClsPub.isStrNull(grdMain.Rows[grdMain.CurrentRow.Index].Cells[Fun_GetCol("员工代码")].Value); if (HEmpNumber == "") return; Pay_MonthSubsidyEntryBillReportMX oMX = new Pay_MonthSubsidyEntryBillReportMX(); oMX.HYear = HYear; oMX.HPeriod = HPeriod; oMX.HEmpNumber = HEmpNumber; oMX.ShowDialog(); } private void czh_Click(object sender, EventArgs e) { string HEmpID = ""; if (grdMain.CurrentRow == null) return; HEmpID = DBUtility.ClsPub.isStrNull(grdMain.Rows[grdMain.CurrentRow.Index].Cells[Fun_GetCol("员工代码")].Value); if (HEmpID == "") return; int HYear = ClsPub.isInt(cmbYear.Text); int HPeriod = ClsPub.isInt(cmbPeriod.Text); SQLHelper.ClsCN oCn1 = new SQLHelper.ClsCN(); DataSet DSEmp = new DataSet();//职员 DataSet DSXFFlag = new DataSet();//结账标记 SQLHelper.ClsCNK32 oCn2 = new SQLHelper.ClsCNK32(); DataSet DSet2 = new DataSet();//余额 if (MessageBox.Show("是否充值" + HYear + "年" + HPeriod + "月，职员代码：" + HEmpID + " 金额？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK) { DSXFFlag = oCn1.RunProcReturn("select HXFEndFlag from Xt_AccountPeriod where HYear=" + HYear + " and HPeriod=" + HPeriod, "Xt_AccountPeriod"); if (ClsPub.isInt(DSXFFlag.Tables[0].Rows[0][0]) == 0) { DSEmp = oCn1.RunProcReturn("select hitemid,HEmpCode,HMoney from Pay_MonthWriteBill where hyear=" + HYear + " and hperiod=" + HPeriod + " and hwriteflag=0 and hempcode='" + HEmpID + "'", "Pay_MonthWriteBill"); if (DSEmp.Tables[0].Rows.Count > 0) { long HItemid = 0; string HEmpNumber = ""; double HMoney = 0; for (int i = 0; i < DSEmp.Tables[0].Rows.Count; i++) { HItemid = ClsPub.isLong(DSEmp.Tables[0].Rows[i]["hitemid"]); HEmpNumber = ClsPub.isStrNull(DSEmp.Tables[0].Rows[i]["HEmpCode"]); HMoney = ClsPub.isDoule(DSEmp.Tables[0].Rows[i]["HMoney"]); //充值 oCn2.RunProc("h_p_Pay_MonthWriteBill '" + HEmpNumber + "'," + HMoney); //打上充值标记 oCn1.RunProc("update Pay_MonthWriteBill set HWriteFlag=1 where HItemid=" + HItemid); } MessageBox.Show("卡充值完毕！"); Display(); } else { MessageBox.Show(HYear + "年" + HPeriod + "月，职员代码：" + HEmpID + " 没有未充值记录？"); } } else { MessageBox.Show("本月消费已结账，不能卡充值！"); } } } private void cs_Click(object sender, EventArgs e) { string HEmpID = ""; if (grdMain.CurrentRow == null) return; HEmpID = DBUtility.ClsPub.isStrNull(grdMain.Rows[grdMain.CurrentRow.Index].Cells[Fun_GetCol("员工代码")].Value); if (HEmpID == "") return; int HYear = ClsPub.isInt(cmbYear.Text); int HPeriod = ClsPub.isInt(cmbPeriod.Text); //月初日期 DateTime KSRQ = ClsPub.isDate(HYear + "-" + HPeriod + "-01"); //最后日期 DateTime ZHRQ = ClsPub.isDate(HYear + "-" + HPeriod + "-01").AddMonths(1); SQLHelper.ClsCN oCn1 = new SQLHelper.ClsCN(); DataSet DSEmp = new DataSet();//职员 DataSet DSXFFlag = new DataSet();//结账标记 DataSet DSWriteFlag = new DataSet();//充值标记 SQLHelper.ClsCNK32 oCn2 = new SQLHelper.ClsCNK32(); DataSet DSet2 = new DataSet();//月末余额 DataSet DSet3 = new DataSet();//月初余额 if (MessageBox.Show("是否重新计算" + HYear + "年" + HPeriod + "月，职员代码：" + HEmpID + " 充值金额？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK) { DSXFFlag = oCn1.RunProcReturn("select HXFEndFlag from Xt_AccountPeriod where HYear=" + HYear + " and HPeriod=" + HPeriod, "Xt_AccountPeriod"); if (ClsPub.isInt(DSXFFlag.Tables[0].Rows[0][0]) == 0) { DSWriteFlag = oCn1.RunProcReturn("select hwriteflag from Pay_MonthWriteBill where hyear=" + HYear + " and hperiod=" + HPeriod + " and hempcode='" + HEmpID + "'", "Pay_MonthWriteBill"); { if (DSWriteFlag.Tables[0].Rows.Count > 0) { if (ClsPub.isInt(DSWriteFlag.Tables[0].Rows[0][0]) == 0) { //删除本月月度充值汇总表 oCn1.RunProc("delete Pay_MonthWriteBill where hyear=" + HYear + " and hperiod=" + HPeriod + " and hempcode='" + HEmpID + "'"); DSEmp = oCn1.RunProcReturn("select e.hitemid,e.hnumber,e.hname,isnull(b.hmoney,0) hmoney from h_v_K3_Employee e left join " + "(select HEmpCode,HEmpName,sum(HMoney) HMoney from " + " Pay_MonthSubsidyEntryBill Where HYear=" + HYear.ToString() + " and HPeriod=" + HPeriod + " and hempcode='" + HEmpID + "' Group by HEmpCode,HEmpName) b on e.hnumber=b.hempcode where e.hnumber='" + HEmpID + "'", "h_v_K3_Employee"); if (DSEmp.Tables[0].Rows.Count > 0) { double HSubsidy = 0; //补助 double HMoney = 0; //充值金额 string HEmpNumber = ""; //职员代码 string HEmpName = ""; //职员名称 double HKSYuE = 0; //月初余额 double HYuE = 0; //余额 double HChaJia = 0; //差价 for (int i = 0; i < DSEmp.Tables[0].Rows.Count; i++) { HSubsidy = ClsPub.isDoule(DSEmp.Tables[0].Rows[i]["hmoney"]) * 100; HEmpNumber = ClsPub.isStrNull(DSEmp.Tables[0].Rows[i]["hnumber"]); HEmpName = ClsPub.isStrNull(DSEmp.Tables[0].Rows[i]["hname"]); //获取 月初余额 //DSet2 = oCn2.RunProcReturn("select b.xh,b.save_money from dt_user a inner join xf_mx b on a.user_serial=b.user_serial where user_no='" + HEmpNumber + "' and jl_sj<'" + ZHRQ + "' order by b.xh desc", "dt_ac_link"); DSet2 = oCn2.RunProcReturn("h_p_Pay_GetCardLeftMoney '" + HEmpNumber + "', '" + HEmpName + "','" + ZHRQ + "'", "h_p_Pay_GetCardLeftMoney"); //获取 月底余额 //DSet3 = oCn2.RunProcReturn("select b.xh,b.save_money from dt_user a inner join xf_mx b on a.user_serial=b.user_serial where user_no='" + HEmpNumber + "' and jl_sj<'" + KSRQ + "' order by b.xh desc", "dt_ac_link"); DSet3 = oCn2.RunProcReturn("h_p_Pay_GetCardLeftMoney '" + HEmpNumber + "', '" + HEmpName + "','" + KSRQ + "'", "h_p_Pay_GetCardLeftMoney"); if (DSet2.Tables[0].Rows.Count > 0) { if (DSet3.Tables[0].Rows.Count > 0) { HKSYuE = ClsPub.isDoule(DSet3.Tables[0].Rows[0]["save_money"]); } HYuE = ClsPub.isDoule(DSet2.Tables[0].Rows[0]["save_money"]); if (HYuE + HSubsidy >= 100000) { HMoney = HSubsidy; //月度充值汇总表新增记录 oCn1.RunProc("insert into Pay_MonthWriteBill (HYear,HPeriod,HDeptCode,HDate" + ",HMaker,HMakeDate,HRemark,HEmpCode,HExpenseType,HQty,HPrice,HMoney,HLeftMoney,HSubsidy,HDeuct,HPreLeftMoney)" + " values(" + HYear + "," + HPeriod + ",'','" + DateTime.Today + "'" + ",'" + DBUtility.ClsPub.CurUserName + "','" + DateTime.Today + "','','" + HEmpNumber + "','补助',1," + HMoney + "," + HMoney + "," + HYuE + "," + HSubsidy + ",0," + HKSYuE + ")"); } else { HChaJia = 100000 - HYuE - HSubsidy; HMoney = HSubsidy + HChaJia; // //月度充值汇总表新增记录 oCn1.RunProc("insert into Pay_MonthWriteBill (HYear,HPeriod,HDeptCode,HDate" + ",HMaker,HMakeDate,HRemark,HEmpCode,HExpenseType,HQty,HPrice,HMoney,HLeftMoney,HSubsidy,HDeuct,HPreLeftMoney)" + " values(" + HYear + "," + HPeriod + ",'','" + DateTime.Today + "'" + ",'" + DBUtility.ClsPub.CurUserName + "','" + DateTime.Today + "','','" + HEmpNumber + "','补助',1," + HMoney + "," + HMoney + "," + HYuE + "," + HSubsidy + "," + HChaJia + "," + HKSYuE + ")"); } } } MessageBox.Show("计算完毕！"); Display(); } } else { MessageBox.Show("本条记录已充值，不能重新计算！"); } } else { MessageBox.Show(HYear + "年" + HPeriod + "月，职员代码：" + HEmpID + " 没有记录！", "提示"); } } } else { MessageBox.Show("本月消费已结账，不能重新计算！"); } } } private void Tool_ItemClicked(object sender, ToolStripItemClickedEventArgs e) { } private void zh_Click(object sender, EventArgs e) { int HYear = 0; int HPeriod = 0; string HEmpNumber = ""; if (grdMain.CurrentRow == null) return; HYear = DBUtility.ClsPub.isInt(grdMain.Rows[grdMain.CurrentRow.Index].Cells[Fun_GetCol("年")].Value); HPeriod = DBUtility.ClsPub.isInt(grdMain.Rows[grdMain.CurrentRow.Index].Cells[Fun_GetCol("月")].Value); HEmpNumber = DBUtility.ClsPub.isStrNull(grdMain.Rows[grdMain.CurrentRow.Index].Cells[Fun_GetCol("员工代码")].Value); if (HEmpNumber == "") return; Pay_MonthWriteBillReportMX2 oMX = new Pay_MonthWriteBillReportMX2(); oMX.HYear = HYear; oMX.HPeriod = HPeriod; oMX.HEmpNumber = HEmpNumber; oMX.ShowDialog(); } private void button3_Click(object sender, EventArgs e) { int HYear = ClsPub.isInt(cmbYear.Text); int HPeriod = ClsPub.isInt(cmbPeriod.Text); SQLHelper.ClsCN oCn1 = new SQLHelper.ClsCN(); DataSet DSEmp = new DataSet();//职员 DataSet DSXFFlag = new DataSet();//结账标记 SQLHelper.ClsCNK32 oCn2 = new SQLHelper.ClsCNK32(); DataSet DSet2 = new DataSet();//余额 if (MessageBox.Show("是否扣除" + HYear + "年" + HPeriod + "月金额？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK) { DSXFFlag = oCn1.RunProcReturn("select HXFEndFlag from Xt_AccountPeriod where HYear=" + HYear + " and HPeriod=" + HPeriod, "Xt_AccountPeriod"); if (ClsPub.isInt(DSXFFlag.Tables[0].Rows[0][0]) == 0) { DSEmp = oCn1.RunProcReturn("select hitemid,HEmpCode,HMoney from Pay_MonthWriteBill where hyear=" + HYear + " and hperiod=" + HPeriod + " and hwriteflag<>0", "Pay_MonthWriteBill"); if (DSEmp.Tables[0].Rows.Count > 0) { p1.Maximum = DSEmp.Tables[0].Rows.Count; p1.Value = 1; long HItemid = 0; string HEmpNumber = ""; double HMoney = 0; for (int i = 0; i < DSEmp.Tables[0].Rows.Count; i++) { HItemid = ClsPub.isLong(DSEmp.Tables[0].Rows[i]["hitemid"]); HEmpNumber = ClsPub.isStrNull(DSEmp.Tables[0].Rows[i]["HEmpCode"]); HMoney = ClsPub.isDoule(DSEmp.Tables[0].Rows[i]["HMoney"]); if (HMoney > 0 ) { //扣款 //oCn2.RunProc("h_p_Pay_MonthWriteBill '" + HEmpNumber + "'," + HMoney); oCn2.RunProc("h_p_Pay_YearToCashBill '" + HEmpNumber + "'," + HMoney); } //取消充值标记 oCn1.RunProc("update Pay_MonthWriteBill set HWriteFlag=0 where HItemid=" + HItemid); System.Windows.Forms.Application.DoEvents(); if (p1.Value == p1.Maximum) { p1.Value = p1.Value; } else { p1.Value = p1.Value + 1; } System.Windows.Forms.Application.DoEvents(); } MessageBox.Show("卡扣款完毕！"); Display(); } } else { MessageBox.Show("本月消费已结账，不能卡扣款！"); } } } //private void grdMain_DblClick(object sender, EventArgs e) //{ // if (Fun_AllowRow(grdMain.Row)) // { // //判断汇总类型 // if (oDlg.chkHSumFlag.Checked == false) //明细 // { // //显示单据 // Kf_SellOutBillList oBillList = new Kf_SellOutBillList(); // oBillList.timer2.Enabled = false; // oBillList.sCondition = ""; // oBillList.sCondition = oBillList.sCondition + " And 日期>='" + oDlg.dtpHBDate.Value.ToShortDateString() +"'"; // oBillList.sCondition = oBillList.sCondition + " And 日期<='" + oDlg.dtpHEDate.Value.ToShortDateString() +"'"; // oBillList.sCondition = oBillList.sCondition + " And 物料代码='" + ClsPub.isStrNull(grdMain.get_TextMatrix(grdMain.Row, Fun_GetCol("产品代码")))+"'"; // oBillList.sCondition = oBillList.sCondition + " And 客户代码='" + ClsPub.isStrNull(grdMain.get_TextMatrix(grdMain.Row, Fun_GetCol("客户代码"))) +"'"; // oBillList.MdiParent = this.MdiParent; // oBillList.Show(); // oBillList.timer1.Enabled = true; // oBillList.Focus(); // return; // } // else // { // switch (oDlg.cmbHSumType.Text.Trim()) // { // case "产品": // //过滤框赋值 // oDlg.txtHBMaterID.Text = ClsPub.isStrNull(grdMain.get_TextMatrix(grdMain.Row, Fun_GetCol("产品代码"))); // oDlg.txtHEMaterID.Text = ClsPub.isStrNull(grdMain.get_TextMatrix(grdMain.Row, Fun_GetCol("产品代码"))); // oDlg.chkHSumFlag.Checked = false; // break; // case "客户": // //过滤框赋值 // oDlg.txtHBCusID.Text = ClsPub.isStrNull(grdMain.get_TextMatrix(grdMain.Row, Fun_GetCol("客户代码"))); // oDlg.txtHECusID.Text = ClsPub.isStrNull(grdMain.get_TextMatrix(grdMain.Row, Fun_GetCol("客户代码"))); // oDlg.chkHSumFlag.Checked = false; // break; // default: // return; // } // } // //刷新 // timer1.Enabled = true; // } //} } }