using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Linq; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Threading; using System.Collections; using System.Reflection; using CoreFS.CA06; using Infragistics.Win.UltraWinGrid; using Core.Mes.Client.Comm.Control; namespace Core.StlMes.Client.Sale.UI.Balance.TranMoney { public partial class FrmTransMoneyQueryRpt : Core.StlMes.Client.Sale.Common.FrmStyleBase.FrmUIBase { public FrmTransMoneyQueryRpt() { InitializeComponent(); } private DataSet dsMain = new DataSet(); private void InitDataSource() { dsMain = base.GetDataSetByColumnName(dataStruct(), GetTbColumnNameAndDataType(), GetDataSet2ColumnCaption()); this.ultraGrid1.DataSource = dsMain; } private void FrmTransMoneyQueryRpt_Load(object sender, EventArgs e) { this.dateTimePicker2.Format = DateTimePickerFormat.Custom; this.dateTimePicker2.CustomFormat = "yyyyMM"; this.dateTimePicker1.Format = DateTimePickerFormat.Custom; this.dateTimePicker1.CustomFormat = "yyyyMM"; this.dateTimePicker3.Format = DateTimePickerFormat.Custom; this.dateTimePicker3.CustomFormat = "yyyyMM"; this.dateTimePicker4.Format = DateTimePickerFormat.Custom; this.dateTimePicker4.CustomFormat = "yyyyMM"; InitDataSource(); base.SetGridStyle(ref this.ultraGrid1); InitCellAllowEdit(); this.ultraGrid1.DisplayLayout.Bands[0].Columns[this.ASKPLAN_ID].MergedCellStyle = Infragistics.Win.UltraWinGrid.MergedCellStyle.Always; } public override void Query() { if (rdbCw.Checked) { if (Convert.ToInt64(Convert.ToString(this.dateTimePicker2.Value.ToString("yyyyMM"))) > Convert.ToInt64(Convert.ToString(this.dateTimePicker1.Value.ToString("yyyyMM")))) { Core.StlMes.Client.Sale.Util.MessageUtil.ShowTips("起始月份不能大于截止月份！"); return; } } if (rdbDd.Checked) { if (Convert.ToInt64(Convert.ToString(this.dateTimePicker4.Value.ToString("yyyyMM"))) > Convert.ToInt64(Convert.ToString(this.dateTimePicker3.Value.ToString("yyyyMM")))) { Core.StlMes.Client.Sale.Util.MessageUtil.ShowTips("起始月份不能大于截止月份！"); return; } } DataSet ds = base.ExecuteReader(this.GetSql()); if (!this.UserInfo.GetUserName().Equals("admin")) ds = base.GetDataSetByFilterDeptId(ds); ds = Core.StlMes.Client.Sale.Util.UtilDataSet.GetDsByDescOrderFromSourceDataSet(ds, this.ASKPLAN_ID); Core.StlMes.Client.Sale.Util.UtilDataSet.SetDataSetByMergeSameColumnName(ref this.dsMain, ds); SetDataSetGrid1(); } public override void Export() { Core.StlMes.Client.Sale.Util.UtilUltraGrid.ExportGridDataToExcel(this.Text, this.ultraGrid1); } public override void Add() { this.ultraGrid1.UpdateData(); if (this.ultraGrid1.Rows.Count == 0) return; Infragistics.Win.UltraWinGrid.UltraGridRow ugs = this.ultraGrid1.ActiveRow; if (ugs == null) { Core.StlMes.Client.Sale.Util.MessageUtil.ShowTips("请选择需要更新的行"); return; } try { if (MessageBox.Show("是否确认修改选定行的信息", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) == DialogResult.No) return; foreach (Infragistics.Win.UltraWinGrid.UltraGridRow ugr in this.ultraGrid1.Rows) { if (ugr.Cells[this.SELECT].Value.ToString().ToUpper() == "TRUE") { string strSql = this.GetUpdateSql(ugr.Cells[ASKPLAN_ID].Value.ToString(), ugr.Cells["DISBURASES_BILL"].Value.ToString(), ugr.Cells["MEMO_YF"].Value.ToString(), (ugr.Cells["IS_ASK_P"].Value.ToString().ToUpper() == "TRUE") ? "1" : "0", ugr.Cells["MONEY"].Value.ToString(), ugr.Cells["TRANS_PK"].Value.ToString(), ugr.Cells["TRANS_NO"].Value.ToString(), ugr.Cells["STAMP_DUTY_MONEY"].Value.ToString()); base.ExecuteNoReader(strSql); } } Core.StlMes.Client.Sale.Util.MessageUtil.ShowTips("操作成功"); } catch (Exception ex) { Core.StlMes.Client.Sale.Util.MessageUtil.ShowTips(ex.ToString()); } Query(); } public override void Update() { this.ultraGrid1.UpdateData(); if (this.ultraGrid1.Rows.Count == 0) return; UltraGridRow[] rows = GridHelper.GetRowsWithKey(ultraGrid1, new string[] { "SELECT" }, new string[] { "True" }); if (rows.Length == 0) { Core.StlMes.Client.Sale.Util.MessageUtil.ShowTips("请选择一条数据后，再进行操作！"); return; } else if (rows.Length > 1) { Core.StlMes.Client.Sale.Util.MessageUtil.ShowTips("只能单条复制，请重新操作！"); return; } try { if (MessageBox.Show("是否确认复制选定行的信息", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) == DialogResult.No) return; foreach (Infragistics.Win.UltraWinGrid.UltraGridRow ugr in this.ultraGrid1.Rows) { if (ugr.Cells[this.SELECT].Value.ToString().ToUpper() == "TRUE") { string strSql = this.GetAddSql(ugr.Cells[ASKPLAN_ID].Value.ToString()); base.ExecuteNoReader(strSql); } } Core.StlMes.Client.Sale.Util.MessageUtil.ShowTips("操作成功"); } catch (Exception ex) { Core.StlMes.Client.Sale.Util.MessageUtil.ShowTips(ex.ToString()); } Query(); } private void SetDataSetGrid1() { foreach (DataRow dr in dsMain.Tables[0].Rows) { dr["IS_ASK_P"] = (dr["IS_ASK"].ToString() == "是") ? true : false; dr[this.SELECT] = false; } } protected override System.Collections.Hashtable GetTbColumnNameAndDataType() { System.Collections.Hashtable hs = new System.Collections.Hashtable(); hs.Add("IS_ASK_P", "System.Boolean"); hs.Add(this.SELECT, "System.Boolean"); return hs; } private void InitCellAllowEdit() { foreach (string s in new string[] { this.SELECT, "IS_ASK_P", "MEMO_YF", "DISBURASES_BILL", "MONEY", "STAMP_DUTY_MONEY", "TRANS_NO" }) { this.ultraGrid1.DisplayLayout.Bands[0].Columns[s].CellActivation = Infragistics.Win.UltraWinGrid.Activation.AllowEdit; this.ultraGrid1.DisplayLayout.Bands[0].Columns[s].CellAppearance.BackColor = base.GetEditColor(); } } private string[] dataStruct() { return new string[] { this.SELECT, "IS_ASK_P", "INVOICE_MONTH", "IS_ASK", "ASKPLAN_ID", "DISBURASES_BILL", "TRANS_NO", "ORDER_NO", "PRODUCNAME", "BUYERNAME", "MANAGEMENT_NAME", "FORECORPNAME", "INCEPTCORPCODE", "TRANS_TYPE", "TICKETTYPE", "MONEY", "STAMP_DUTY_MONEY", "STATION_NM", "CARNUM", "ACT_WT", "MEMO_YF", "REG_DTIME", "TRANS_PK", }; } protected override System.Collections.Hashtable GetGridColumnLength() { System.Collections.Hashtable hs = new System.Collections.Hashtable(); hs.Add("IS_ASK", 50); hs.Add("IS_ASK_P", 50); hs.Add(this.SELECT, 50); hs.Add("TRANS_TYPE", 50); hs.Add("TICKETTYPE", 50); hs.Add("CARNUM", 50); return hs; } private Hashtable GetDataSet2ColumnCaption() { Hashtable hs = new Hashtable(); hs.Add(TRANS_PK, "主键"); hs.Add(ASKPLAN_ID, "提单号"); hs.Add(TALLYSHEET_NO, "理货单号"); hs.Add(LOADVEHICLE_NO, "装车单号"); hs.Add(TRAINWAGON, "车号"); hs.Add(BUYERCODE, "结算单位"); hs.Add(BUYERNAME, "结算单位"); hs.Add(FORECORPCODE, "销售组织"); hs.Add(FORECORPNAME, "销售组织"); hs.Add(FST_DRAWEE_UNIT, "首次受票人"); hs.Add(STATIONCODE, "到站"); hs.Add(STATIONNAME, "到站"); hs.Add(LEASEDLINE, "专用线名称"); hs.Add(LEASEDLINENAME, "专用线名称"); hs.Add(INCEPTCORPCODE, "收货单位"); hs.Add(TRANS_TYPE, "运输方式"); hs.Add(MOTORMANNAME, "承运单位"); hs.Add(DOCKNAME, "水运起始与目的港口"); hs.Add(TICKETTYPE, "单据类型"); hs.Add(TICKETNO, "单据号"); hs.Add(MONEY_TYPE, "费用类型"); hs.Add(MONEY, "金额"); hs.Add("STAMP_DUTY_MONEY", "印花税"); hs.Add(LOAD_TIME, "装车时间"); hs.Add(INVOICENO, "结算单号"); hs.Add(INVOICEDATE, "编单时间"); hs.Add(INVOICE_ID, "编单人"); hs.Add(STATUS_CD, "状态区分"); hs.Add(REG_ID, "录入者"); hs.Add(REG_DTIME, "代垫年月"); hs.Add(AFFRIM_ID, "确认人"); hs.Add(AFFRIM_TIME, "确认时间"); hs.Add(SELECT, "选择"); hs.Add("TICKETNUM", "票据数"); hs.Add("ORD_SEQ", "合同号"); //hs.Add("INCEPTCORPCODE", "收货单位"); hs.Add("TRANS_TYPE_NAME", "外运类型"); hs.Add("PLANDATE", " 时间"); hs.Add("WEIGHT", "重量"); hs.Add("SALE_AREA_DESC", "区域"); hs.Add("VSTEL_DESC", "装车地点"); hs.Add("STORAGE_NAME", "出库地点"); hs.Add("CNT", "装车数"); hs.Add("JB", "出库结标"); hs.Add("LISTNUMBER", "批次号"); hs.Add("INVOICE_MONTH", "结算年月"); hs.Add("MANAGEMENT_NAME", "片区"); hs.Add("CARNUM", "车数"); hs.Add("PRODUCNAME", "品种"); hs.Add("DISBURASES_BILL", "代垫单号"); hs.Add("TRANS_NO", "运费发票号"); hs.Add("ACT_WT", "装车重量"); hs.Add("IS_ASK_P", "是否核销"); hs.Add("MEMO_YF", "备注"); hs.Add("IS_ASK", "核销状态"); return hs; } private string SELECT = "SELECT"; private string MONET_TYPE = "MONET_TYPE"; private string TICKETNO = "TICKETNO"; private string OUTSTOCKMNY = "OUTSTOCKMNY"; private string DZFMNY = "DZFMNY"; private string TRANSMNY = "TRANSMNY"; private string TRANSNUM = "TRANSNUM"; private string LOADVEHICLE_NO = "LOADVEHICLE_NO"; private string BILL_NO = "BILL_NO"; private string TALLYSHEET_NO = "TALLYSHEET_NO"; private string LOADVEHICLE_TYPE = "LOADVEHICLE_TYPE"; private string AFFIRM_FLAG = "AFFIRM_FLAG"; private string VEHICLE_NO = "VEHICLE_NO"; private string TRANS_TYPE = "TRANS_TYPE"; private string RECEIV_NO = "RECEIV_NO"; private string RECEIV_NM = "RECEIV_NM"; private string STATION_NO = "STATION_NO"; private string STATION_NM = "STATION_NM"; private string SPCL_LN_NO = "SPCL_LN_NO"; private string SPCL_LN_NM = "SPCL_LN_NM"; private string PORT = "PORT"; private string SHIP_LOT_NO = "SHIP_LOT_NO"; private string PLANT = "PLANT"; private string PLANT_DESC = "PLANT_DESC"; private string VSTEL_CODE = "VSTEL_CODE"; private string VSTEL_DESC = "VSTEL_DESC"; private string CARRIER_NO = "CARRIER_NO"; private string CARRY_COMPANY_CODE = "CARRY_COMPANY_CODE"; private string CARRY_COMPANY_NAME = "CARRY_COMPANY_NAME"; private string STACKING_WT = "STACKING_WT"; private string STACKING_PIECE = "STACKING_PIECE"; private string STACKING_TOTL_LEN = "STACKING_TOTL_LEN"; private string WEIGH_FLAG = "WEIGH_FLAG"; private string PONDER_NO = "PONDER_NO"; private string PONDER_GROSS_WT = "PONDER_GROSS_WT"; private string OUT_STOCK_CODE = "OUT_STOCK_CODE"; private string DELIVY_PLACE_CODE = "DELIVY_PLACE_CODE"; private string DELIVY_PLACE_NAME = "DELIVY_PLACE_NAME"; private string STLMT_UNIT_NO = "STLMT_UNIT_NO"; private string STLMT_UNIT_NM = "STLMT_UNIT_NM"; private string INFACTORY_TIME = "INFACTORY_TIME"; private string LOAD_TIME = "LOAD_TIME"; private string DELIVY_TIME = "DELIVY_TIME"; private string STACKING_PRINTS = "STACKING_PRINTS"; private string THREE_READY_NO = "THREE_READY_NO"; private string HANDHELD_NO = "HANDHELD_NO"; private string HANDHELD_FLAG = "HANDHELD_FLAG"; private string MANAGEMENT_NO = "MANAGEMENT_NO"; private string MANAGEMENT_NAME = "MANAGEMENT_NAME"; private string MEMO = "MEMO"; private string VALIDFLAG = "VALIDFLAG"; private string CREATE_NAME = "CREATE_NAME"; private string CREATE_TIME = "CREATE_TIME"; private string UPDATE_NAME = "UPDATE_NAME"; private string UPDATE_TIME = "UPDATE_TIME"; private string DELETE_NAME = "DELETE_NAME"; private string DELETE_TIME = "DELETE_TIME"; private string TRANS_PK = "TRANS_PK"; private string ASKPLAN_ID = "ASKPLAN_ID"; private string TRAINWAGON = "TRAINWAGON"; private string BUYERCODE = "BUYERCODE"; private string BUYERNAME = "BUYERNAME"; private string FORECORPCODE = "FORECORPCODE"; private string FORECORPNAME = "FORECORPNAME"; private string FST_DRAWEE_UNIT = "FST_DRAWEE_UNIT"; private string STATIONCODE = "STATIONCODE"; private string STATIONNAME = "STATIONNAME"; private string LEASEDLINE = "LEASEDLINE"; private string LEASEDLINENAME = "LEASEDLINENAME"; private string INCEPTCORPCODE = "INCEPTCORPCODE"; private string MOTORMANNAME = "MOTORMANNAME"; private string DOCKNAME = "DOCKNAME"; private string TICKETTYPE = "TICKETTYPE"; private string MONEY_TYPE = "MONEY_TYPE"; private string MONEY = "MONEY"; private string INVOICENO = "INVOICENO"; private string INVOICEDATE = "INVOICEDATE"; private string INVOICE_ID = "INVOICE_ID"; private string STATUS_CD = "STATUS_CD"; private string REG_ID = "REG_ID"; private string REG_DTIME = "REG_DTIME"; private string AFFRIM_ID = "AFFRIM_ID"; private string AFFRIM_TIME = "AFFRIM_TIME"; private string ORDER_NO = "ORDER_NO"; private string ORDER_SEQ = "ORDER_SEQ"; private string DELIVERY_NO = "DELIVERY_NO"; private string ORD_PK = "ORD_PK"; private string ORD_LN_PK = "ORD_LN_PK"; private string ORD_LN_DLY_PK = "ORD_LN_DLY_PK"; private string AGENCY_FL = "AGENCY_FL"; private string RECEIV_ADDR = "RECEIV_ADDR"; private string DELVRY_ADDR = "DELVRY_ADDR"; private string CUSTOMER_NO = "CUSTOMER_NO"; private string CUSTOMER_NM = "CUSTOMER_NM"; private string SALE_ORG = "SALE_ORG"; private string SALE_ORG_DESC = "SALE_ORG_DESC"; private string GetUpdateSql(string _askplan_id, string _disburases_bill, string _meno, string _isask, string _money, string _pk, string no, string stamp_duty_money) { string sql = @" update slm_balance_transmoney t set t.is_ask = '{1}',t.memo_yf = '{2}',t.disburases_bill ='{3}',t.MONEY = '{4}',t.TRANS_NO ='{6}',t.STAMP_DUTY_MONEY ='{7}' where t.askplan_id = '{0}' and t.MONEY_TYPE ='运费' and t.TRANS_PK = '{5}'"; sql = string.Format(sql, _askplan_id, _isask, _meno, _disburases_bill, _money,_pk, no,stamp_duty_money); return sql; } private string GetAddSql(string _askplan_id) { string sql = @" INSERT INTO SLM_BALANCE_TRANSMONEY (TRANS_PK, ASKPLAN_ID, TALLYSHEET_NO, LOADVEHICLE_NO, ORD_PK, ORD_LN_PK, ORD_LN_DLY_PK, AGENCY_FL, RECEIV_ADDR, TICKETNUM, ORDER_NO, ORDER_SEQ, DELIVERY_NO, INDEX_SEQ, DELVRY_ADDR, BUYERCODE, BUYERNAME, FORECORPCODE, FORECORPNAME, FST_DRAWEE_UNIT, INCEPTCORPCODE, TRANS_TYPE, MOTORMANNAME, DOCKNAME, TICKETTYPE, TICKETNO, MONEY_TYPE, MONEY, INVOICENO, INVOICEDATE, INVOICE_ID, STATUS_CD, REG_ID, REG_DTIME, AFFRIM_ID, AFFRIM_TIME, WEIGHT, INVOICENO_ORG, INVOICEDATE_ORG, INVOICEPERSON_ORG, REALINVOICENO, REALINVOICENO_ORG, APPLY_PERSON, APPLY_DATE, APPLY_NO, RPY_APPLY_NO, LISTNUMBER, TICKETSTATUS, TICKETDATE, TICKETPERSON, STATUS_CD_ORG, STATUS_CD_ORG_PER, STATUS_CD_ORG_DATE, APPLY_DATE_STS, REALINVOICE_DATE, REALINVOICE_ORG_DATE, INVOICE_MONTH, INVOICE_STS, DISBURASES_BILL, IS_ASK, MEMO_YF, BYUNF_LOG) SELECT CONCAT(SUBSTR(TRANS_PK,1,32),LPAD(TO_CHAR(TO_NUMBER( NVL(SUBSTR(( SELECT TRANS_PK FROM (SELECT * FROM ( SELECT TRANS_PK, REG_DTIME FROM SLM_BALANCE_TRANSMONEY WHERE ASKPLAN_ID = '{0}' AND MONEY_TYPE = '运费') ORDER BY TRANS_PK DESC) WHERE ROWNUM = 1 ),33,3),1))+1), 3, '0')) , ASKPLAN_ID, TALLYSHEET_NO, LOADVEHICLE_NO, ORD_PK, ORD_LN_PK, ORD_LN_DLY_PK, AGENCY_FL, RECEIV_ADDR, TICKETNUM, ORDER_NO, ORDER_SEQ, DELIVERY_NO, INDEX_SEQ, DELVRY_ADDR, BUYERCODE, BUYERNAME, FORECORPCODE, FORECORPNAME, FST_DRAWEE_UNIT, INCEPTCORPCODE, TRANS_TYPE, MOTORMANNAME, DOCKNAME, TICKETTYPE, TICKETNO, MONEY_TYPE, 0, INVOICENO, INVOICEDATE, INVOICE_ID, STATUS_CD, REG_ID, SYSDATE, AFFRIM_ID, AFFRIM_TIME, WEIGHT, INVOICENO_ORG, INVOICEDATE_ORG, INVOICEPERSON_ORG, REALINVOICENO, REALINVOICENO_ORG, APPLY_PERSON, APPLY_DATE, APPLY_NO, RPY_APPLY_NO, LISTNUMBER, TICKETSTATUS, TICKETDATE, TICKETPERSON, STATUS_CD_ORG, STATUS_CD_ORG_PER, STATUS_CD_ORG_DATE, APPLY_DATE_STS, REALINVOICE_DATE, REALINVOICE_ORG_DATE, INVOICE_MONTH, INVOICE_STS, '', '0', '', BYUNF_LOG FROM SLM_BALANCE_TRANSMONEY WHERE ASKPLAN_ID = '{0}' AND MONEY_TYPE ='运费' AND ROWNUM = 1 "; sql = string.Format(sql, _askplan_id); return sql; } private string GetSql() { String sql = @"SELECT A.ASKPLAN_ID, DECODE(A.IS_ASK,'1','是','否') IS_ASK, A.MEMO_YF, A.BUYERNAME, A.DISBURASES_BILL, A.TRANS_NO, A.FORECORPNAME, A.INCEPTCORPCODE, A.TRANS_TYPE, A.TICKETTYPE, A.MONEY, B.CREATE_DEPT, A.INVOICE_MONTH, TO_CHAR(A.REG_DTIME,'YYYYMM') REG_DTIME, B.ORDER_NO, B.STATION_NM, B.MANAGEMENT_NAME, (SELECT COUNT(DISTINCT LOADVEHICLE_NO ) FROM YDM_ZC_LOADVEHICLE T WHERE T.BILL_NO = A.ASKPLAN_ID) CARNUM, B.PRODUCNAME, (SELECT SUM(W.WEIGHT) FROM SLM_BALANCEINFO W WHERE W.ASKPLAN_ID = A.ASKPLAN_ID ) ACT_WT, A.TRANS_PK,a.STAMP_DUTY_MONEY FROM SLM_BALANCE_TRANSMONEY A, (SELECT MIN(C.MANAGEMENT_NO) CREATE_DEPT, C.BILL_NO, MIN(C.CREATE_TIME) CREATE_TIME, MIN(C.ORDER_NO||'/'||ORDER_SEQ) ORDER_NO, MIN(C.STATION_NM) STATION_NM, MIN(C.MANAGEMENT_NAME) MANAGEMENT_NAME, MIN(C.PRODUCNAME) PRODUCNAME FROM YDM_ZC_BILL_M C GROUP BY BILL_NO) B WHERE A.ASKPLAN_ID = B.BILL_NO(+) AND A.MONEY_TYPE = '运费' {0} "; string str = ""; if (rdbCw.Checked) { string invoiceMonth = this.dateTimePicker2.Value.ToString("yyyyMM"); string invoiceMonth1 = this.dateTimePicker1.Value.ToString("yyyyMM"); str = string.Format(" and (A.invoice_month >='{0}' and A.invoice_month <='{1}') ", invoiceMonth, invoiceMonth1); } if (rdbDd.Checked) { string regMonth2 = this.dateTimePicker4.Value.ToString("yyyyMM"); string regMonth3 = this.dateTimePicker3.Value.ToString("yyyyMM"); str = string.Format(" and (TO_CHAR(A.REG_DTIME,'YYYYMM') >='{0}' and TO_CHAR(A.REG_DTIME,'YYYYMM') <='{1}') ", regMonth2, regMonth3); } if (this.textBox7.Text.Length > 0) { str = string.Format(" and b.bill_no like '%'||'{0}'||'%' ", this.textBox7.Text); } if (this.textBox1.Text.Length > 0) { str = string.Format(" and A.DISBURASES_BILL like '%'||'{0}'||'%' ", this.textBox1.Text); } if (this.textBox2.Text.Length > 0) { str = string.Format(" and A.TRANS_NO like '%'||'{0}'||'%' ", this.textBox2.Text); } sql = string.Format(sql, str); return sql; } private void textBox7_KeyDown(object sender, KeyEventArgs e) { if (e.KeyValue == 13) { Query(); } } } }