using System; using System.Data; using System.Data.Common; using System.Collections; using System.Collections.Generic; using System.Text; using DSWeb.Areas.Account.Models.Chfee_Payapplication; using DSWeb.Areas.Account.Models.Chfee_Settlement; using DSWeb.Areas.Account.Models.Chfee_do_detail; using DSWeb.Areas.Account.Models.MsOpBill; using DSWeb.Areas.Account.Models.BillChfeeDetail; using Microsoft.Practices.EnterpriseLibrary.Data; using DSWeb.Areas.Account.Models.Chfee_Exrate; using DSWeb.Areas.Account.Models.Chfee_do_Sum; using DSWeb.Areas.Import.Models.CWAdvancePayment; using DSWeb.Areas.Account.DAL.Chfee_Payapplication; using DSWeb.Areas.Account.DAL.Chfee_Recvapplication; using DSWeb.Areas.CommMng.Models; using DSWeb.EntityDA; using HcUtility.Comm; using HcUtility.Core; using DSWeb.Areas.CommMng.DAL; using DSWeb.Areas.Account.Models.Chfee_Invoice; using DSWeb.Areas.Account.Models.Chfee_Invoicehexiao; using DSWeb.Areas.Account.Models.ChfeeDetail; using DSWeb.Areas.Account.Models.MsCwVouchersGl3; using DSWeb.MvcShipping.Models.MsCwVouchersGl; using DSWeb.MvcShipping.Models.MsCwVouitemsGl; using DSWeb.MvcShipping.DAL.MsBaseInfoDAL; using DSWeb.MvcShipping.DAL.MsInfoClient; using DSWeb.CW; using DSWeb.Models; using System.Data.SqlClient; namespace DSWeb.Areas.Account.DAL.Chfee_Hexiao { public partial class ChHexiaoDAL { #region 核销单列表 static public List GetHexiaoDataList(string strCondition, int start, int limit, out int totel, string userid = "", string usercode = "", string orgcode = "", string sort = null, string isload = "0") { var rangstr = ""; rangstr = GetRangDAListStr("modPaySettlementList", userid, usercode, orgcode); if (!string.IsNullOrEmpty(rangstr)) { if (!string.IsNullOrEmpty(strCondition)) { strCondition = strCondition + " and " + rangstr; } else { strCondition = rangstr; } } var strSql = new StringBuilder(); if (isload == "1") strSql.Append(" SELECT top 100 GID,CM.BILLNO,BILLDATE,CUSTOMERNAME "); else strSql.Append(" SELECT GID,cm.BILLNO,BILLDATE,CUSTOMERNAME "); strSql.Append(" ,AUDITUSER,AUDITTIME,CREATEUSER,CREATETIME"); strSql.Append(" ,BILLSTATUS,REMARK,COMPANYID,VOUNO"); strSql.Append(" ,(SELECT EXPVOUNO FROM cw_vouchers WHERE VOUALLNO=cm.VOUNO) EXPVOUNO"); strSql.Append(",(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=97006 and EnumValueID=cm.BILLSTATUS) as BILLSTATUSREF"); strSql.Append(",(select ShowName from [user] where GID=cm.CREATEUSER) as CREATEUSERREF,D.STLRMBDR,D.STLUSDDR,D.STLOTDR,D.STLRMBCR,D.STLUSDCR,D.STLOTCR "); strSql.Append(" FROM ch_fee_hexiao cm "); strSql.Append(" left join v_fee_do_hexiao_SUM D ON (D.HXBILLNO=cm.BILLNO) "); strSql.Append(" where 1=1 "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" and " + strCondition); } var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { if (sortstring.IndexOf("CREATETIME") >= 0) strSql.Append(" order by " + sortstring); else strSql.Append(" order by " + sortstring + ",CREATETIME DESC"); } else { strSql.Append(" order by CREATETIME DESC,CM.BILLNO DESC"); } strSql = PagerHelper.PageSQL(strSql.ToString(), start, limit, out totel); return SetHexiaoData(strSql); } static public string GetHexiaoDataListStr(string strCondition, string userid = "", string usercode = "", string orgcode = "", string sort = null, int billtype = 0) { var rangstr = ""; if (billtype == 2) rangstr = GetRangDAListStr("modPaySettlementList", userid, usercode, orgcode); else if (billtype == 1) rangstr = GetRangDAListStr("modRecvSettleList", userid, usercode, orgcode); if (!string.IsNullOrEmpty(rangstr)) { if (!string.IsNullOrEmpty(strCondition)) { strCondition = strCondition + " and " + rangstr; } else { strCondition = rangstr; } } var strSql = new StringBuilder(); strSql.Append(" SELECT GID,cm.BILLNO,BILLDATE,CUSTOMERNAME "); strSql.Append(" ,AUDITUSER,AUDITTIME,CREATEUSER,CREATETIME"); strSql.Append(" ,BILLSTATUS,REMARK,COMPANYID,VOUNO"); strSql.Append(" ,(SELECT EXPVOUNO FROM cw_vouchers WHERE VOUALLNO=cm.VOUNO) EXPVOUNO"); strSql.Append(",(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=97006 and EnumValueID=cm.BILLSTATUS) as BILLSTATUSREF"); strSql.Append(",(select ShowName from [user] where GID=cm.CREATEUSER) as CREATEUSERREF,D.STLRMBDR,D.STLUSDDR,D.STLOTDR,D.STLRMBCR,D.STLUSDCR,D.STLOTCR "); strSql.Append(" FROM ch_fee_hexiao cm "); strSql.Append(" left join v_fee_do_hexiao_SUM D ON (D.HXBILLNO=cm.BILLNO) "); strSql.Append(" where 1=1 "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" and " + strCondition); } var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { if (sortstring.IndexOf("CREATETIME") >= 0) strSql.Append(" order by " + sortstring); else strSql.Append(" order by " + sortstring + ",CREATETIME DESC"); } else { strSql.Append(" order by CREATETIME DESC,CM.BILLNO DESC"); } return strSql.ToString(); } static public ChfeeHexiao GetHexiaoData(string condition, string userid = "", string usercode = "", string orgcode = "") { int total = 0; var list = GetHexiaoDataList(condition, 0, 1, out total, userid, usercode, orgcode); if (list.Count > 0) return list[0]; return new ChfeeHexiao(); } private static List SetHexiaoData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { ChfeeHexiao data = new ChfeeHexiao(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.BILLNO = Convert.ToString(reader["BILLNO"]); if (reader["BILLDATE"] != DBNull.Value) data.BILLDATE = Convert.ToDateTime(reader["BILLDATE"]).ToString("yyyy-MM-dd"); data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]); data.CREATEUSER = Convert.ToString(reader["CREATEUSER"]); data.CREATEUSERREF = Convert.ToString(reader["CREATEUSERREF"]); if (reader["CREATETIME"] != DBNull.Value) data.CREATETIME = Convert.ToDateTime(reader["CREATETIME"]).ToString("yyyy-MM-dd"); if (reader["AUDITTIME"] != DBNull.Value) data.AUDITTIME = Convert.ToDateTime(reader["AUDITTIME"]); data.AUDITUSER = Convert.ToString(reader["AUDITUSER"]); data.BILLSTATUS = Convert.ToInt32(reader["BILLSTATUS"]); data.BILLSTATUSREF = Convert.ToString(reader["BILLSTATUSREF"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.COMPANYID = Convert.ToString(reader["COMPANYID"]); data.VOUNO = Convert.ToString(reader["VOUNO"]); data.EXPVOUNO = Convert.ToString(reader["EXPVOUNO"]); //data.INVOICECATEGORY = Convert.ToString(reader["INVOICECATEGORY"]); //data.INVOICECATEGORYREF = Convert.ToString(reader["INVOICECATEGORYREF"]); if (reader["STLRMBDR"] != DBNull.Value) data.STLRMBDR =Math.Round(Convert.ToDecimal(reader["STLRMBDR"]),2, MidpointRounding.AwayFromZero); if (reader["STLOTDR"] != DBNull.Value) data.STLOTDR= Math.Round(Convert.ToDecimal(reader["STLOTDR"]),2, MidpointRounding.AwayFromZero); if (reader["STLUSDDR"] != DBNull.Value) data.STLUSDDR = Math.Round(Convert.ToDecimal(reader["STLUSDDR"]),2, MidpointRounding.AwayFromZero); if (reader["STLRMBCR"] != DBNull.Value) data.STLRMBCR = Math.Round(Convert.ToDecimal(reader["STLRMBCR"]),2, MidpointRounding.AwayFromZero); if (reader["STLOTCR"] != DBNull.Value) data.STLOTCR = Math.Round(Convert.ToDecimal(reader["STLOTCR"]),2, MidpointRounding.AwayFromZero); if (reader["STLUSDCR"] != DBNull.Value) data.STLUSDCR = Math.Round(Convert.ToDecimal(reader["STLUSDCR"]),2, MidpointRounding.AwayFromZero); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 核销单费用明细 static public List GetBodyList(string strCondition, string sort = null) { var strSql = new StringBuilder(); strSql.Append(" SELECT c.GID,c.BSNO,c.CUSTOMERNAME FEEOBJNAME,c.FEEID,c.FEENAME,c.CURRENCY,c.AMOUNT,c.DOAMOUNT,c.ORIGCURRENCY,c.ORIGAMOUNT,c.EXCHANGERATE "); strSql.Append(",c.FEETYPE,(CASE C.FEETYPE WHEN 1 THEN '收' ELSE '付' END) AS FEETYPEREF,(SELECT EXCHANGERATE from ch_fee where GID=c.FEEID) ORIGEXCHANGERATE"); strSql.Append(" ,b.OPLBNAME,b.BSSTATUS,b.ACCDATE,b.MBLNO,b.HBLNO,b.CUSTNO"); strSql.Append(",b.CUSTOMERNAME,b.CUSTOMNO"); strSql.Append(",b.VESSEL,b.VOYNO,b.ETD,b.PORTLOAD"); strSql.Append(",b.PORTDISCHARGE,b.INPUTBY,b.OP,b.CUSTSERVICE,B.OPTYPE"); strSql.Append(",b.DOC,b.SALE,b.ENTERP,(select TOP 1 INVOICENO from ch_fee_invoice where BILLNO IN (SELECT BILLNO FROM CH_FEE_DO WHERE FEEID=c.FEEID AND CATEGORY=7)) INVOICENO "); strSql.Append(" ,f.remark Remark,f.INPUTMODE "); strSql.Append(" ,(select name from company where gid=b.corpid) COMPANYNAME"); strSql.Append(" FROM ch_fee_do_hexiao c"); strSql.Append(" left join v_op_bill b on (b.bsno=c.bsno)"); strSql.Append(" left join ch_fee f on (f.gid=c.feeid) "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where (c.ISDELETED=0 or c.ISDELETED IS NULL) and " + strCondition); } var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by c.CREATETIME "); } return BodySetData(strSql); } private static List BodySetData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { Chfee_do_detail data = new Chfee_do_detail(); #region Set DB data to Object data.BSNO = Convert.ToString(reader["BSNO"]); data.FEEDOID = Convert.ToString(reader["GID"]); data.FEEOBJNAME = Convert.ToString(reader["FEEOBJNAME"]); data.FEEID = Convert.ToString(reader["FEEID"]); data.FEENAME = Convert.ToString(reader["FEENAME"]); data.FEETYPE = Convert.ToInt16(reader["FEETYPE"]); data.FEETYPEREF = Convert.ToString(reader["FEETYPEREF"]); data.CURRENCY = Convert.ToString(reader["CURRENCY"]); data.AMOUNT = Convert.ToDecimal(reader["AMOUNT"]); data.DOAMOUNT = Convert.ToDecimal(reader["DOAMOUNT"]); data.ORIGCURRENCY = Convert.ToString(reader["ORIGCURRENCY"]); if (reader["ORIGAMOUNT"] != DBNull.Value) data.ORIGAMOUNT = Convert.ToDecimal(reader["ORIGAMOUNT"]); if (reader["EXCHANGERATE"] != DBNull.Value) data.EXCHANGERATE = Convert.ToDecimal(reader["EXCHANGERATE"]); if (reader["ETD"] != DBNull.Value) data.ETD = Convert.ToString(reader["ETD"]); data.ACCDATE = Convert.ToString(reader["ACCDATE"]); data.OPLBNAME = Convert.ToString(reader["OPLBNAME"]); data.MBLNO = Convert.ToString(reader["MBLNO"]); data.HBLNO = Convert.ToString(reader["HBLNO"]); data.CUSTNO = Convert.ToString(reader["CUSTNO"]); data.CUSTOMNO = Convert.ToString(reader["CUSTOMNO"]); data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]); data.VESSEL = Convert.ToString(reader["VESSEL"]); data.VOYNO = Convert.ToString(reader["VOYNO"]); data.PORTLOAD = Convert.ToString(reader["PORTLOAD"]); data.PORTDISCHARGE = Convert.ToString(reader["PORTDISCHARGE"]); data.INPUTBY = Convert.ToString(reader["INPUTBY"]); data.OP = Convert.ToString(reader["OP"]); data.CUSTSERVICE = Convert.ToString(reader["CUSTSERVICE"]); data.DOC = Convert.ToString(reader["DOC"]); data.SALE = Convert.ToString(reader["SALE"]); data.ENTERP = Convert.ToString(reader["ENTERP"]); data.INVOICENO = Convert.ToString(reader["INVOICENO"]); data.BSSTATUS = Convert.ToBoolean(reader["BSSTATUS"]); data.Remark = Convert.ToString(reader["Remark"]); data.COMPANYNAME = Convert.ToString(reader["COMPANYNAME"]); data.OPTYPE = Convert.ToString(reader["OPTYPE"]); if (reader["ORIGEXCHANGERATE"] != DBNull.Value) data.ORIGEXCHANGERATE = Convert.ToDecimal(reader["ORIGEXCHANGERATE"]); data.INPUTMODE = Convert.ToString(reader["INPUTMODE"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 明细金额币别合计 static public List GetBodySumList(string strCondition) { var strSql = new StringBuilder(); strSql.Append(" SELECT c.FEETYPE,(CASE C.FEETYPE WHEN 1 THEN '收' ELSE '付' END) AS FEETYPEREF,c.ORIGCURRENCY CURRENCY,Sum(c.ORIGAMOUNT) AMOUNT "); strSql.Append(" FROM ch_fee_do_hexiao c"); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where (c.ISDELETED=0 or c.ISDELETED IS NULL) and " + strCondition); } strSql.Append(" Group by c.FEETYPE,c.ORIGCURRENCY"); return BodySumSetData(strSql); } private static List BodySumSetData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { Chfee_do_Sum data = new Chfee_do_Sum(); #region Set DB data to Object data.FEETYPE = Convert.ToInt16(reader["FEETYPE"]); data.FEETYPEREF = Convert.ToString(reader["FEETYPEREF"]); data.CURRENCY = Convert.ToString(reader["CURRENCY"]); if (reader["AMOUNT"] != DBNull.Value) data.AMOUNT = Convert.ToDecimal(reader["AMOUNT"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region Inquery FeeDataList static public List GetFeeDataList(string strCondition, string userid, string usercode, string orgcode, string sort,int billtype) { var rangstr =""; rangstr = GetRangDAStr("modRecvSettleSearch", userid, usercode, orgcode); if (!string.IsNullOrEmpty(rangstr)) { if (!string.IsNullOrEmpty(strCondition)) { strCondition = strCondition + " and " + rangstr; } else { strCondition = rangstr; } } var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append("b.BSNO,b.OPLB,b.OPLBNAME,b.BSSTATUS,b.ACCDATE,b.MBLNO,b.HBLNO,b.CUSTNO"); strSql.Append(",b.CUSTOMERNAME,b.CUSTOMNO"); strSql.Append(",b.VESSEL,b.VOYNO,b.ETD,b.PORTLOAD"); strSql.Append(",b.PORTDISCHARGE,b.INPUTBY,b.OP,b.CUSTSERVICE"); strSql.Append(",b.DOC,b.SALE,b.ENTERP"); strSql.Append(",f.GID AS CH_ID,f.FeeType"); strSql.Append(",(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99020 and EnumValueID=f.FeeType) as FeeType_Ref"); strSql.Append(",f.feeName,f.customerName as FeeObjName,f.amount,f.amount-isnull(f.SETTLEMENT,0) as balamount,f.currency,f.exChangerate,isnull(f.invoice,0) as invoice"); strSql.Append(" from ch_fee f "); strSql.Append(" left join v_op_bill b on (b.bsno=f.bsno)"); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); } var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by b.BsNo"); } return SetFeeData(strSql); } private static List SetFeeData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { BillChfeeDetail data = new BillChfeeDetail(); #region Set DB data to Object data.BSNO = Convert.ToString(reader["BSNO"]); if (reader["ETD"] != DBNull.Value) data.ETD = Convert.ToString(reader["ETD"]); data.ACCDATE = Convert.ToString(reader["ACCDATE"]); data.OPLB = Convert.ToString(reader["OPLB"]); data.OPLBNAME = Convert.ToString(reader["OPLBNAME"]); data.MBLNO = Convert.ToString(reader["MBLNO"]); data.HBLNO = Convert.ToString(reader["HBLNO"]); data.CUSTNO = Convert.ToString(reader["CUSTNO"]); data.CUSTOMNO = Convert.ToString(reader["CUSTOMNO"]); data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]); data.VESSEL = Convert.ToString(reader["VESSEL"]); data.VOYNO = Convert.ToString(reader["VOYNO"]); data.PORTLOAD = Convert.ToString(reader["PORTLOAD"]); data.PORTDISCHARGE = Convert.ToString(reader["PORTDISCHARGE"]); data.INPUTBY = Convert.ToString(reader["INPUTBY"]); data.OP = Convert.ToString(reader["OP"]); data.CUSTSERVICE = Convert.ToString(reader["CUSTSERVICE"]); data.DOC = Convert.ToString(reader["DOC"]); data.SALE = Convert.ToString(reader["SALE"]); data.ENTERP = Convert.ToString(reader["ENTERP"]); if (reader["BSSTATUS"] != DBNull.Value) data.BSSTATUS = Convert.ToBoolean(reader["BSSTATUS"]); data.CH_ID = Convert.ToString(reader["CH_ID"]); data.FeeType = Convert.ToInt16(reader["FeeType"]); data.FeeType_Ref = Convert.ToString(reader["FeeType_Ref"]); data.FeeName = Convert.ToString(reader["feeName"]); data.FeeObjName = Convert.ToString(reader["FeeObjName"]); data.Amount = Convert.ToDecimal(reader["Amount"]); if (reader["BalAmount"] != DBNull.Value) data.BalAmount = Convert.ToDecimal(reader["BalAmount"]); if (reader["BalAmount"] != DBNull.Value) data.StlAmount = Convert.ToDecimal(reader["BalAmount"]); data.Currency = Convert.ToString(reader["Currency"]); if (reader["exChangerate"] != DBNull.Value) data.ExChangerate = Convert.ToDecimal(reader["exChangerate"]); data.Invoice = Convert.ToDecimal(reader["invoice"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 添加列表 static public List GetAddBillList(string strCondition, string userid, string usercode, string orgcode, string sort, int billtype) { var rangstr = ""; rangstr = GetRangDAStr("modRecvSettleSearch", userid, usercode, orgcode); if (!string.IsNullOrEmpty(rangstr)) { if (!string.IsNullOrEmpty(strCondition)) { strCondition = strCondition + " and " + rangstr; } else { strCondition = rangstr; } } var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append("b.BSNO,b.OPLB,b.OPLBNAME,b.BSSTATUS,b.ACCDATE,b.MBLNO,b.HBLNO,b.CUSTNO"); strSql.Append(",b.CUSTOMERNAME"); strSql.Append(",b.VESSEL,b.VOYNO,b.ETD,b.PORTLOAD"); strSql.Append(",b.PORTDISCHARGE,b.INPUTBY,b.OP,b.CUSTSERVICE,b.OPTYPE"); strSql.Append(",b.DOC,b.SALE,b.ENTERP,b.CUSTOMNO"); strSql.Append(",F.CUSTOMERNAME AS CUSTNAME "); strSql.Append(",isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS RMBDR,"); strSql.Append("isnull(SUM(CASE FEETYPE WHEN 2 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS RMBCR,"); strSql.Append("isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN f.AMOUNT-isnull(f.SETTLEMENT,0) ELSE 0 END) ELSE 0 END),0) AS BALRMBDR,"); strSql.Append("isnull(SUM(CASE FEETYPE WHEN 2 THEN (CASE CURRENCY WHEN 'RMB' THEN f.AMOUNT-isnull(f.SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS BALRMBCR,"); strSql.Append("isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS USDDR,"); strSql.Append("isnull(SUM(CASE FEETYPE WHEN 2 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS USDCR,"); strSql.Append("isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN f.AMOUNT-isnull(f.SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDDR,"); strSql.Append("isnull(SUM(CASE FEETYPE WHEN 2 THEN (CASE CURRENCY WHEN 'USD' THEN f.AMOUNT-isnull(f.SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDCR,"); strSql.Append("isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END),0) AS OTDR,"); strSql.Append("isnull(SUM(CASE FEETYPE WHEN 2 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END),0) AS OTCR,"); strSql.Append("isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE f.AMOUNT-isnull(f.SETTLEMENT,0) END) END) ELSE 0 END),0) AS BALOTDR,"); strSql.Append("isnull(SUM(CASE FEETYPE WHEN 2 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE f.AMOUNT-isnull(f.SETTLEMENT,0) END) END) ELSE 0 END),0) AS BALOTCR,"); strSql.Append("isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN f.AMOUNT-ISNULL(INVOICE,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDINVDR,"); strSql.Append("isnull(SUM(CASE FEETYPE WHEN 2 THEN (CASE CURRENCY WHEN 'USD' THEN f.AMOUNT-ISNULL(INVOICE,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDINVCR,"); strSql.Append("isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN f.AMOUNT-ISNULL(INVOICE,0) ELSE 0 END)ELSE 0 END),0) AS BALRMBINVDR,"); strSql.Append("isnull(SUM(CASE FEETYPE WHEN 2 THEN (CASE CURRENCY WHEN 'RMB' THEN f.AMOUNT-ISNULL(INVOICE,0) ELSE 0 END)ELSE 0 END),0) AS BALRMBINVCR"); strSql.Append(" ,(select name from company where gid=b.corpid ) COMPANYNAME "); strSql.Append(" FROM CH_FEE f "); strSql.Append(" INNER JOIN V_OP_BILL B ON (F.BSNO=B.BSNO) "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where (f.AMOUNT-isnull(f.SETTLEMENT,0))<>0 and " + strCondition); } strSql.Append("GROUP BY b.BSNO,b.OPLB,b.OPLBNAME,b.BSSTATUS,b.ACCDATE,b.MBLNO,b.HBLNO,b.CUSTNO"); strSql.Append(",b.CUSTOMERNAME"); strSql.Append(",b.VESSEL,b.VOYNO,b.ETD,b.PORTLOAD"); strSql.Append(",b.PORTDISCHARGE,b.INPUTBY,b.OP,b.CUSTSERVICE,b.OPTYPE"); strSql.Append(",b.DOC,b.SALE,b.ENTERP,b.CUSTOMNO,f.CUSTOMERNAME,B.CORPID"); var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { sortstring = sortstring.Replace("FEEOBJNAME", "F.CUSTOMERNAME"); strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by b.BsNo"); } return SetAddBillData(strSql); } static public List GetShenAddBillList(string strCondition, string ShenNo,string userid, string usercode, string orgcode, string sort, int billtype) { var rangstr = ""; rangstr = GetRangDAStr("modRecvSettleSearch", userid, usercode, orgcode); if (!string.IsNullOrEmpty(rangstr)) { if (!string.IsNullOrEmpty(strCondition)) { strCondition = strCondition + " and " + rangstr; } else { strCondition = rangstr; } } var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append("b.BSNO,b.OPLB,b.OPLBNAME,b.BSSTATUS,b.ACCDATE,b.MBLNO,b.HBLNO,b.CUSTNO"); strSql.Append(",b.CUSTOMERNAME"); strSql.Append(",b.VESSEL,b.VOYNO,b.ETD,b.PORTLOAD"); strSql.Append(",b.PORTDISCHARGE,b.INPUTBY,b.OP,b.CUSTSERVICE,b.OPTYPE"); strSql.Append(",b.DOC,b.SALE,b.ENTERP,b.CUSTOMNO"); strSql.Append(",F.CUSTOMERNAME AS CUSTNAME "); strSql.Append(",isnull(SUM(CASE F.FEETYPE WHEN 1 THEN (CASE F.CURRENCY WHEN 'RMB' THEN ISNULL(F.AMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS RMBDR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 2 THEN (CASE F.CURRENCY WHEN 'RMB' THEN ISNULL(F.AMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS RMBCR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 1 THEN (CASE F.CURRENCY WHEN 'RMB' THEN D.ORIGAMOUNT-isnull(ORIGSTLAMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS BALRMBDR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 2 THEN (CASE F.CURRENCY WHEN 'RMB' THEN D.ORIGAMOUNT-isnull(ORIGSTLAMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS BALRMBCR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 1 THEN (CASE F.CURRENCY WHEN 'USD' THEN ISNULL(F.AMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS USDDR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 2 THEN (CASE F.CURRENCY WHEN 'USD' THEN ISNULL(F.AMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS USDCR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 1 THEN (CASE F.CURRENCY WHEN 'USD' THEN D.ORIGAMOUNT-isnull(ORIGSTLAMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDDR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 2 THEN (CASE F.CURRENCY WHEN 'USD' THEN D.ORIGAMOUNT-isnull(ORIGSTLAMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDCR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 1 THEN (CASE F.CURRENCY WHEN 'USD' THEN 0 ELSE (CASE F.CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(F.AMOUNT,0) END) END)ELSE 0 END),0) AS OTDR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 2 THEN (CASE F.CURRENCY WHEN 'USD' THEN 0 ELSE (CASE F.CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(F.AMOUNT,0) END) END)ELSE 0 END),0) AS OTCR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 1 THEN (CASE F.CURRENCY WHEN 'USD' THEN 0 ELSE (CASE F.CURRENCY WHEN 'RMB' THEN 0 ELSE D.ORIGAMOUNT-isnull(ORIGSTLAMOUNT,0) END) END) ELSE 0 END),0) AS BALOTDR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 2 THEN (CASE F.CURRENCY WHEN 'USD' THEN 0 ELSE (CASE F.CURRENCY WHEN 'RMB' THEN 0 ELSE D.ORIGAMOUNT-isnull(ORIGSTLAMOUNT,0) END) END) ELSE 0 END),0) AS BALOTCR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 1 THEN (CASE F.CURRENCY WHEN 'USD' THEN f.AMOUNT-ISNULL(F.INVOICE,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDINVDR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 2 THEN (CASE F.CURRENCY WHEN 'USD' THEN f.AMOUNT-ISNULL(F.INVOICE,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDINVCR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 1 THEN (CASE F.CURRENCY WHEN 'RMB' THEN f.AMOUNT-ISNULL(F.INVOICE,0) ELSE 0 END)ELSE 0 END),0) AS BALRMBINVDR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 2 THEN (CASE F.CURRENCY WHEN 'RMB' THEN f.AMOUNT-ISNULL(F.INVOICE,0) ELSE 0 END)ELSE 0 END),0) AS BALRMBINVCR"); strSql.Append(" ,(select name from company where gid=b.corpid ) COMPANYNAME "); strSql.Append(" FROM CH_FEE_DO d "); strSql.Append(" left join CH_FEE f on (f.GID=d.FEEID)"); // strSql.Append(" left join (SELECT BILLNO,FEEID,SUM(ORIGAMOUNT) ORIGAMOUNT FROM CH_FEE_DO where ISNULL(ORIGSTLAMOUNT,0)<>ORIGAMOUNT and BILLNO='" + ShenNo + "' and CATEGORY=4 GROUP BY BILLNO,FEEID ) D ON (D.FEEID=F.GID) "); strSql.Append(" left join ch_fee_payapplication p on (p.BILLNO=d.BILLNO) "); strSql.Append(" INNER JOIN V_OP_BILL B ON (F.BSNO=B.BSNO) WHERE D.BILLNO='" + ShenNo + "' and p.BILLSTATUS IN (0,4,5) and d.AMOUNT<>D.DOAMOUNT AND (((D.ORIGAMOUNT-isnull(d.ORIGSTLAMOUNT,0))>=0 AND f.AMOUNT-isnull(f.SETTLEMENT,0)>=(D.ORIGAMOUNT-isnull(d.ORIGSTLAMOUNT,0))) OR ((D.ORIGAMOUNT-isnull(d.ORIGSTLAMOUNT,0))<=0 AND f.AMOUNT-isnull(f.SETTLEMENT,0)<=(D.ORIGAMOUNT-isnull(d.ORIGSTLAMOUNT,0))) ) "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" AND (f.AMOUNT-isnull(f.SETTLEMENT,0))<>0 and " + strCondition); } strSql.Append("GROUP BY b.BSNO,b.OPLB,b.OPLBNAME,b.BSSTATUS,b.ACCDATE,b.MBLNO,b.HBLNO,b.CUSTNO"); strSql.Append(",b.CUSTOMERNAME"); strSql.Append(",b.VESSEL,b.VOYNO,b.ETD,b.PORTLOAD"); strSql.Append(",b.PORTDISCHARGE,b.INPUTBY,b.OP,b.CUSTSERVICE,b.OPTYPE"); strSql.Append(",b.DOC,b.SALE,b.ENTERP,b.CUSTOMNO,f.CUSTOMERNAME,B.CORPID"); var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { sortstring = sortstring.Replace("FEEOBJNAME", "F.CUSTOMERNAME"); strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by b.BsNo"); } return SetAddBillData(strSql); } static public List GetInvAddBillList(string strCondition, string ShenNo, string userid, string usercode, string orgcode, string sort, int billtype) { var rangstr = ""; rangstr = GetRangDAStr("modRecvSettleSearch", userid, usercode, orgcode); if (!string.IsNullOrEmpty(rangstr)) { if (!string.IsNullOrEmpty(strCondition)) { strCondition = strCondition + " and " + rangstr; } else { strCondition = rangstr; } } var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append("b.BSNO,b.OPLB,b.OPLBNAME,b.BSSTATUS,b.ACCDATE,b.MBLNO,b.HBLNO,b.CUSTNO"); strSql.Append(",b.CUSTOMERNAME"); strSql.Append(",b.VESSEL,b.VOYNO,b.ETD,b.PORTLOAD"); strSql.Append(",b.PORTDISCHARGE,b.INPUTBY,b.OP,b.CUSTSERVICE,b.OPTYPE"); strSql.Append(",b.DOC,b.SALE,b.ENTERP,b.CUSTOMNO"); strSql.Append(",F.CUSTOMERNAME AS CUSTNAME "); strSql.Append(",isnull(SUM(CASE F.FEETYPE WHEN 1 THEN (CASE F.CURRENCY WHEN 'RMB' THEN ISNULL(F.AMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS RMBDR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 2 THEN (CASE F.CURRENCY WHEN 'RMB' THEN ISNULL(F.AMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS RMBCR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 1 THEN (CASE F.CURRENCY WHEN 'RMB' THEN D.ORIGAMOUNT-isnull(ORIGSTLAMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS BALRMBDR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 2 THEN (CASE F.CURRENCY WHEN 'RMB' THEN D.ORIGAMOUNT-isnull(ORIGSTLAMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS BALRMBCR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 1 THEN (CASE F.CURRENCY WHEN 'USD' THEN ISNULL(F.AMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS USDDR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 2 THEN (CASE F.CURRENCY WHEN 'USD' THEN ISNULL(F.AMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS USDCR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 1 THEN (CASE F.CURRENCY WHEN 'USD' THEN D.ORIGAMOUNT-isnull(ORIGSTLAMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDDR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 2 THEN (CASE F.CURRENCY WHEN 'USD' THEN D.ORIGAMOUNT-isnull(ORIGSTLAMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDCR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 1 THEN (CASE F.CURRENCY WHEN 'USD' THEN 0 ELSE (CASE F.CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(F.AMOUNT,0) END) END)ELSE 0 END),0) AS OTDR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 2 THEN (CASE F.CURRENCY WHEN 'USD' THEN 0 ELSE (CASE F.CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(F.AMOUNT,0) END) END)ELSE 0 END),0) AS OTCR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 1 THEN (CASE F.CURRENCY WHEN 'USD' THEN 0 ELSE (CASE F.CURRENCY WHEN 'RMB' THEN 0 ELSE D.ORIGAMOUNT-isnull(ORIGSTLAMOUNT,0) END) END) ELSE 0 END),0) AS BALOTDR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 2 THEN (CASE F.CURRENCY WHEN 'USD' THEN 0 ELSE (CASE F.CURRENCY WHEN 'RMB' THEN 0 ELSE D.ORIGAMOUNT-isnull(ORIGSTLAMOUNT,0) END) END) ELSE 0 END),0) AS BALOTCR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 1 THEN (CASE F.CURRENCY WHEN 'USD' THEN f.AMOUNT-ISNULL(F.INVOICE,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDINVDR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 2 THEN (CASE F.CURRENCY WHEN 'USD' THEN f.AMOUNT-ISNULL(F.INVOICE,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDINVCR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 1 THEN (CASE F.CURRENCY WHEN 'RMB' THEN f.AMOUNT-ISNULL(F.INVOICE,0) ELSE 0 END)ELSE 0 END),0) AS BALRMBINVDR,"); strSql.Append("isnull(SUM(CASE F.FEETYPE WHEN 2 THEN (CASE F.CURRENCY WHEN 'RMB' THEN f.AMOUNT-ISNULL(F.INVOICE,0) ELSE 0 END)ELSE 0 END),0) AS BALRMBINVCR"); strSql.Append(" ,(select name from company where gid=b.corpid ) COMPANYNAME "); strSql.Append(" FROM CH_FEE_DO d "); strSql.Append(" left join CH_FEE f on (f.GID=d.FEEID)"); // strSql.Append(" left join (SELECT BILLNO,FEEID,SUM(ORIGAMOUNT) ORIGAMOUNT FROM CH_FEE_DO where ISNULL(ORIGSTLAMOUNT,0)<>ORIGAMOUNT and BILLNO='" + ShenNo + "' and CATEGORY=4 GROUP BY BILLNO,FEEID ) D ON (D.FEEID=F.GID) "); strSql.Append(" left join ch_fee_invoice p on (p.BILLNO=d.BILLNO) "); strSql.Append(" INNER JOIN V_OP_BILL B ON (F.BSNO=B.BSNO) WHERE D.INVOICENO='" + ShenNo + "' and d.AMOUNT<>d.DOAMOUNT "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" AND (f.AMOUNT-isnull(f.SETTLEMENT,0))<>0 and " + strCondition); } strSql.Append("GROUP BY b.BSNO,b.OPLB,b.OPLBNAME,b.BSSTATUS,b.ACCDATE,b.MBLNO,b.HBLNO,b.CUSTNO"); strSql.Append(",b.CUSTOMERNAME"); strSql.Append(",b.VESSEL,b.VOYNO,b.ETD,b.PORTLOAD"); strSql.Append(",b.PORTDISCHARGE,b.INPUTBY,b.OP,b.CUSTSERVICE,b.OPTYPE"); strSql.Append(",b.DOC,b.SALE,b.ENTERP,b.CUSTOMNO,f.CUSTOMERNAME,B.CORPID"); var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { sortstring = sortstring.Replace("FEEOBJNAME", "F.CUSTOMERNAME"); strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by b.BsNo"); } return SetAddBillData(strSql); } private static List SetAddBillData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = strSql.ToString(); cmd.CommandTimeout = 120000; //要加这一句 using (IDataReader reader = db.ExecuteReader(cmd)) { while (reader.Read()) { BillCustDetail data = new BillCustDetail(); #region Set DB data to Object data.BSNO = Convert.ToString(reader["BSNO"]); if (reader["ETD"] != DBNull.Value) data.ETD = Convert.ToString(reader["ETD"]); data.ACCDATE = Convert.ToString(reader["ACCDATE"]); data.OPLB = Convert.ToString(reader["OPLB"]); data.OPLBNAME = Convert.ToString(reader["OPLBNAME"]); data.MBLNO = Convert.ToString(reader["MBLNO"]); data.HBLNO = Convert.ToString(reader["HBLNO"]); data.CUSTNO = Convert.ToString(reader["CUSTNO"]); data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]); data.VESSEL = Convert.ToString(reader["VESSEL"]); data.VOYNO = Convert.ToString(reader["VOYNO"]); data.PORTLOAD = Convert.ToString(reader["PORTLOAD"]); data.PORTDISCHARGE = Convert.ToString(reader["PORTDISCHARGE"]); data.INPUTBY = Convert.ToString(reader["INPUTBY"]); data.OP = Convert.ToString(reader["OP"]); data.CUSTSERVICE = Convert.ToString(reader["CUSTSERVICE"]); data.DOC = Convert.ToString(reader["DOC"]); data.SALE = Convert.ToString(reader["SALE"]); data.CUSTOMNO = Convert.ToString(reader["CUSTOMNO"]); data.ENTERP = Convert.ToString(reader["ENTERP"]); data.BSSTATUS = Convert.ToBoolean(reader["BSSTATUS"]); data.FEEOBJNAME = Convert.ToString(reader["CUSTNAME"]); data.OPTYPE = Convert.ToString(reader["OPTYPE"]); if (reader["RMBDR"] != DBNull.Value) data.RMBDrAmount = Convert.ToDecimal(reader["RMBDR"]); if (reader["BALRMBDR"] != DBNull.Value) data.RMBDrBalAmount = Convert.ToDecimal(reader["BALRMBDR"]); if (reader["BALRMBDR"] != DBNull.Value) data.RMBDrDoAmount = Convert.ToDecimal(reader["BALRMBDR"]); if (reader["RMBCR"] != DBNull.Value) data.RMBCrAmount = Convert.ToDecimal(reader["RMBCR"]); if (reader["BALRMBCR"] != DBNull.Value) data.RMBCrBalAmount = Convert.ToDecimal(reader["BALRMBCR"]); if (reader["BALRMBCR"] != DBNull.Value) data.RMBCrDoAmount = Convert.ToDecimal(reader["BALRMBCR"]); if (reader["USDDR"] != DBNull.Value) data.USDDrAmount = Convert.ToDecimal(reader["USDDR"]); if (reader["BALUSDDR"] != DBNull.Value) data.USDDrBalAmount = Convert.ToDecimal(reader["BALUSDDR"]); if (reader["BALUSDDR"] != DBNull.Value) data.USDDrDoAmount = Convert.ToDecimal(reader["BALUSDDR"]); if (reader["USDCR"] != DBNull.Value) data.USDCrAmount = Convert.ToDecimal(reader["USDCR"]); if (reader["BALUSDCR"] != DBNull.Value) data.USDCrBalAmount = Convert.ToDecimal(reader["BALUSDCR"]); if (reader["BALUSDCR"] != DBNull.Value) data.USDCrDoAmount = Convert.ToDecimal(reader["BALUSDCR"]); if (reader["OTDR"] != DBNull.Value) data.OTDrAmount = Convert.ToDecimal(reader["OTDR"]); if (reader["BALOTDR"] != DBNull.Value) data.OTDrBalAmount = Convert.ToDecimal(reader["BALOTDR"]); if (reader["BALOTDR"] != DBNull.Value) data.OTDrDoAmount = Convert.ToDecimal(reader["BALOTDR"]); if (reader["OTCR"] != DBNull.Value) data.OTCrAmount = Convert.ToDecimal(reader["OTCR"]); if (reader["BALOTCR"] != DBNull.Value) data.OTCrBalAmount = Convert.ToDecimal(reader["BALOTCR"]); if (reader["BALOTCR"] != DBNull.Value) data.OTCrDoAmount = Convert.ToDecimal(reader["BALOTCR"]); if (reader["BALRMBINVDR"] != DBNull.Value) data.BALRMBINVDR = Convert.ToDecimal(reader["BALRMBINVDR"]); if (reader["BALRMBINVCR"] != DBNull.Value) data.BALRMBINVCR = Convert.ToDecimal(reader["BALRMBINVCR"]); if (reader["BALUSDINVDR"] != DBNull.Value) data.BALUSDINVDR = Convert.ToDecimal(reader["BALUSDINVDR"]); if (reader["BALUSDINVCR"] != DBNull.Value) data.BALUSDINVCR = Convert.ToDecimal(reader["BALUSDINVCR"]); data.COMPANYNAME = Convert.ToString(reader["COMPANYNAME"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion static public List GetAddSum(string strCondition, string userid, string usercode, string orgcode, int billtype) { var rangstr = ""; if (!string.IsNullOrEmpty(rangstr)) { if (!string.IsNullOrEmpty(strCondition)) { strCondition = strCondition + " and " + rangstr; } else { strCondition = rangstr; } } var strSql = new StringBuilder(); strSql.Append(" SELECT f.FEETYPE,(CASE f.FEETYPE WHEN 1 THEN '收' ELSE '付' END) AS FEETYPEREF,f.CURRENCY,Sum(f.amount-isnull(f.SETTLEMENT,0))) AMOUNT "); strSql.Append(" from ch_fee f "); strSql.Append(" left join v_op_bill b on (b.bsno=f.bsno)"); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); } strSql.Append(" Group by f.FEETYPE,f.CURRENCY"); return BodySumSetData(strSql); } #region 20160404 增加连接通用子表 GetChildDataList static public List GetChildDataList(string strCondition) { var strSql = new StringBuilder(); strSql.Append(" select GID,BSNO,CHILDNAME,CHILDNO,REMARK from tSys_child "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); } return ChildSetData(strSql); } private static List ChildSetData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { SysChildmb data = new SysChildmb(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.BSNO = Convert.ToString(reader["BSNO"]); data.CHILDNAME = Convert.ToString(reader["CHILDNAME"]); data.CHILDNO = Convert.ToString(reader["CHILDNO"]); data.REMARK = Convert.ToString(reader["REMARK"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 费用列表明细 static public List GetFeeDetailList(string strCondition, string sort = null) { var strSql = new StringBuilder(); strSql.Append("SELECT * from (select "); strSql.Append("f.BSNO,f.GID AS CH_ID,'' FEEDOID,FeeType"); strSql.Append(",(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99020 and EnumValueID=f.FeeType) as FeeType_Ref"); strSql.Append(",f.feeName,f.customerName,f.amount,f.amount-isnull(f.SETTLEMENT,0) as balamount"); strSql.Append(",f.currency,f.exChangerate,f.INVOICE,f.Remark,f.INPUTMODE,f.currency ORIGCURRENCY,f.amount-isnull(f.SETTLEMENT,0) ORIGAMOUNT,f.exChangerate ORIGEXCHANGERATE"); strSql.Append(" from ch_fee f "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); } strSql.Append(" ) AS VF "); var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { sortstring = sortstring.Replace("StlAmount", "balamount"); strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by feeName"); } return SetFeeDetailData(strSql); } static public List GetShenFeeDetailList(string strCondition, string ShenNo, string sort = null) { var strSql = new StringBuilder(); strSql.Append("SELECT * from (select "); strSql.Append("f.BSNO,f.GID AS CH_ID,d.GID FEEDOID,f.FeeType"); strSql.Append(",(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99020 and EnumValueID=f.FeeType) as FeeType_Ref"); strSql.Append(",f.feeName,f.customerName,d.amount,d.amount-d.doamount as balamount"); strSql.Append(",d.currency,d.exChangerate,f.INVOICE,f.Remark,f.INPUTMODE,d.ORIGCURRENCY,d.ORIGAMOUNT,f.exChangerate ORIGEXCHANGERATE"); strSql.Append(" from ch_fee_do d "); strSql.Append(" left join ch_fee f on (f.GID=d.FEEID) "); // strSql.Append(" left join (SELECT GID FEEDOID,BILLNO,FEEID,ORIGAMOUNT FROM CH_FEE_DO where ORIGSTLAMOUNT<>ORIGAMOUNT and BILLNO='" + ShenNo + "' and CATEGORY=4 ) d on (d.feeid=f.gid) "); strSql.Append(" left join ch_fee_payapplication p on (p.BILLNO=d.BILLNO) "); strSql.Append(" where d.BILLNO='" + ShenNo + "' and d.AMOUNT<>D.DOAMOUNT AND (((D.ORIGAMOUNT-isnull(d.ORIGSTLAMOUNT,0))>=0 AND f.AMOUNT-isnull(f.SETTLEMENT,0)>=(D.ORIGAMOUNT-isnull(d.ORIGSTLAMOUNT,0))) OR ((D.ORIGAMOUNT-isnull(d.ORIGSTLAMOUNT,0))<0 AND f.AMOUNT-isnull(f.SETTLEMENT,0)<=(D.ORIGAMOUNT-isnull(d.ORIGSTLAMOUNT,0))) ) "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" AND " + strCondition); } strSql.Append(" ) AS VF "); var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { sortstring = sortstring.Replace("StlAmount", "balamount"); strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by feeName"); } return SetFeeDetailData(strSql); } private static List SetFeeDetailData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { ChFeeDetail data = new ChFeeDetail(); #region Set DB data to Object data.BSNO = Convert.ToString(reader["BSNO"]); data.CH_ID = Convert.ToString(reader["CH_ID"]); data.FEEDOID = Convert.ToString(reader["FEEDOID"]); data.FeeType = Convert.ToInt16(reader["FeeType"]); data.FeeType_Ref = Convert.ToString(reader["FeeType_Ref"]); data.FeeName = Convert.ToString(reader["feeName"]); data.CustomerName = Convert.ToString(reader["customerName"]); data.Amount = Convert.ToDecimal(reader["Amount"]); if (reader["BalAmount"] != DBNull.Value) data.BalAmount = Convert.ToDecimal(reader["BalAmount"]); if (reader["BalAmount"] != DBNull.Value) data.StlAmount = Convert.ToDecimal(reader["BalAmount"]); data.Currency = Convert.ToString(reader["Currency"]); data.ExChangerate = Convert.ToDecimal(reader["exChangerate"]); if (reader["INVOICE"] != DBNull.Value) data.Invoice = Convert.ToDecimal(reader["INVOICE"]); data.Remark = Convert.ToString(reader["Remark"]); data.INPUTMODE = Convert.ToString(reader["INPUTMODE"]); data.ORIGCURRENCY = Convert.ToString(reader["ORIGCURRENCY"]); if (reader["ORIGAMOUNT"] != DBNull.Value) data.ORIGAMOUNT = Convert.ToDecimal(reader["ORIGAMOUNT"]); if (reader["ORIGEXCHANGERATE"] != DBNull.Value) data.ORIGEXCHANGERATE = Convert.ToDecimal(reader["ORIGEXCHANGERATE"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion static public List GetCurrExrateData(string billcust, string billcurr, string feesql, bool custgroup = false) { var strSql = new StringBuilder(); strSql.Append(" SELECT DISTINCT CURRENCY,EXCHANGERATE "); if (!custgroup) strSql.Append(" FROM ch_fee f where CURRENCY<>'" + billcurr + "' AND CUSTOMERNAME='" + billcust + "'"); else strSql.Append(" FROM ch_fee f where CURRENCY<>'" + billcurr + "'"); if (!string.IsNullOrEmpty(feesql)) { strSql.Append(" and " + feesql); } return SetCurrExrateData(strSql); } private static List SetCurrExrateData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { ChFeeExrate data = new ChFeeExrate(); #region Set DB data to Object data.CURR = Convert.ToString(reader["CURRENCY"]); data.EXRATE = 0; if (reader["EXCHANGERATE"] != DBNull.Value) data.DFEXRATE = Convert.ToDecimal(reader["EXCHANGERATE"]); #endregion headList.Add(data); } reader.Close(); } return headList; } public static DBResult AddDetail(string billno, List bodyList, string curr, string companyid) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdInsert = db.GetSqlStringCommand( @"insert into ch_fee_do (GID,BILLNO,BSNO,MBLNO,HBLNO,CUSTOMERNAME,BSTYPE,FEEID,FEENAME,CURRENCY ,AMOUNT,DOAMOUNT,FEETYPE,CATEGORY,BILLSTATUS,ISDELETED,CREATETIME ,EXCHANGERATE,ORIGCURRENCY,ORIGAMOUNT,INVOICESETTLENO,COMPANYID) values (@GID,@BILLNO,@BSNO,@MBLNO,@HBLNO,@CUSTOMERNAME,@BSTYPE,@FEEID,@FEENAME,@CURRENCY ,@AMOUNT,@DOAMOUNT,@FEETYPE,@CATEGORY,@BILLSTATUS,@ISDELETED,@CREATETIME ,@EXCHANGERATE,@ORIGCURRENCY,@ORIGAMOUNT,@INVOICESETTLENO,@COMPANYID) "); var cmdUpdate = db.GetSqlStringCommand( @"UPDATE CH_FEE SET SETTLEMENT=ISNULL(SETTLEMENT,0)+@SETTLEMENT,FEESTATUS=(case when AMOUNT<>0 AND AMOUNT=(SETTLEMENT+@SETTLEMENT2) then 9 WHEN AMOUNT<>0 AND AMOUNT<>(SETTLEMENT+@SETTLEMENT3) AND (SETTLEMENT+@SETTLEMENT4)<>0 THEN 8 ELSE FEESTATUS end) WHERE GID=@GID "); if (bodyList != null) { foreach (var enumValue in bodyList) { cmdInsert.Parameters.Clear(); db.AddInParameter(cmdInsert, "@GID", DbType.String, Guid.NewGuid().ToString()); db.AddInParameter(cmdInsert, "@BILLNO", DbType.String, billno); db.AddInParameter(cmdInsert, "@BSNO", DbType.String, enumValue.BSNO); db.AddInParameter(cmdInsert, "@MBLNO", DbType.String, enumValue.MBLNO); db.AddInParameter(cmdInsert, "@HBLNO", DbType.String, enumValue.HBLNO); db.AddInParameter(cmdInsert, "@CUSTOMERNAME", DbType.String, enumValue.FeeObjName); var tops = enumValue.BSNO.Substring(0, 7); if (tops == "topseae") db.AddInParameter(cmdInsert, "@BSTYPE", DbType.Boolean, true); else db.AddInParameter(cmdInsert, "@BSTYPE", DbType.Boolean, false); db.AddInParameter(cmdInsert, "@FEEID", DbType.String, enumValue.CH_ID); db.AddInParameter(cmdInsert, "@FEENAME", DbType.String, enumValue.FeeName); db.AddInParameter(cmdInsert, "@CURRENCY", DbType.String, curr); db.AddInParameter(cmdInsert, "@AMOUNT", DbType.Decimal, enumValue.DoAmount); db.AddInParameter(cmdInsert, "@DOAMOUNT", DbType.Decimal, 0); db.AddInParameter(cmdInsert, "@FEETYPE", DbType.Int16, enumValue.FeeType); db.AddInParameter(cmdInsert, "@CATEGORY", DbType.Int16, 8); db.AddInParameter(cmdInsert, "@BILLSTATUS", DbType.Int16, 1); db.AddInParameter(cmdInsert, "@ISDELETED", DbType.Boolean, false); db.AddInParameter(cmdInsert, "@CREATETIME", DbType.DateTime, DateTime.Now); db.AddInParameter(cmdInsert, "@EXCHANGERATE", DbType.Decimal, enumValue.EXRATE); db.AddInParameter(cmdInsert, "@ORIGCURRENCY", DbType.String, enumValue.Currency); db.AddInParameter(cmdInsert, "@ORIGAMOUNT", DbType.Decimal, enumValue.StlAmount); db.AddInParameter(cmdInsert, "@INVOICESETTLENO", DbType.String, ""); db.AddInParameter(cmdInsert, "@COMPANYID", DbType.String, companyid); db.ExecuteNonQuery(cmdInsert, tran); cmdUpdate.Parameters.Clear(); db.AddInParameter(cmdUpdate, "@GID", DbType.String, enumValue.CH_ID); db.AddInParameter(cmdUpdate, "@SETTLEMENT", DbType.Decimal, enumValue.StlAmount); db.AddInParameter(cmdUpdate, "@SETTLEMENT2", DbType.Decimal, enumValue.StlAmount); db.AddInParameter(cmdUpdate, "@SETTLEMENT3", DbType.Decimal, enumValue.StlAmount); db.AddInParameter(cmdUpdate, "@SETTLEMENT4", DbType.Decimal, enumValue.StlAmount); db.ExecuteNonQuery(cmdUpdate, tran); } } tran.Commit(); result.Success = true; result.Message = "保存成功" + result.Message; return result; } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "保存出现错误,请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "保存成功" + result.Message; return result; } public static DBResult AddBill(string billno, string billcust, string billcurr, string feesql, List exratelist, string companyid,bool custgroup=false) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { if (!custgroup) feesql = feesql + " AND CUSTOMERNAME='" + billcust + "' "; var feelist = GetFeeDetailList(feesql); var cmdInsert = db.GetSqlStringCommand( @"insert into ch_fee_do (GID,BILLNO,BSNO,MBLNO,HBLNO,CUSTOMERNAME,BSTYPE,FEEID,FEENAME,CURRENCY ,AMOUNT,DOAMOUNT,FEETYPE,CATEGORY,BILLSTATUS,ISDELETED,CREATETIME ,EXCHANGERATE,ORIGCURRENCY,ORIGAMOUNT,INVOICESETTLENO,COMPANYID) values (@GID,@BILLNO,@BSNO,@MBLNO,@HBLNO,@CUSTOMERNAME,@BSTYPE,@FEEID,@FEENAME,@CURRENCY ,@AMOUNT,@DOAMOUNT,@FEETYPE,@CATEGORY,@BILLSTATUS,@ISDELETED,@CREATETIME ,@EXCHANGERATE,@ORIGCURRENCY,@ORIGAMOUNT,@INVOICESETTLENO,@COMPANYID) "); var cmdUpdate = db.GetSqlStringCommand( @"UPDATE CH_FEE SET SETTLEMENT=ISNULL(SETTLEMENT,0)+@SETTLEMENT,FEESTATUS=(case when AMOUNT<>0 AND AMOUNT=(SETTLEMENT+@SETTLEMENT2) then 9 WHEN AMOUNT<>0 AND AMOUNT<>(SETTLEMENT+@SETTLEMENT3) AND (SETTLEMENT+@SETTLEMENT4)<>0 THEN 8 ELSE FEESTATUS end) WHERE GID=@GID "); if (feelist != null) { foreach (var enumValue in feelist) { cmdInsert.Parameters.Clear(); db.AddInParameter(cmdInsert, "@GID", DbType.String, Guid.NewGuid().ToString()); db.AddInParameter(cmdInsert, "@BILLNO", DbType.String, billno); db.AddInParameter(cmdInsert, "@BSNO", DbType.String, enumValue.BSNO); db.AddInParameter(cmdInsert, "@MBLNO", DbType.String, ""); db.AddInParameter(cmdInsert, "@HBLNO", DbType.String, ""); db.AddInParameter(cmdInsert, "@CUSTOMERNAME", DbType.String, enumValue.CustomerName); var tops = enumValue.BSNO.Substring(0, 7); if (tops == "topseae") db.AddInParameter(cmdInsert, "@BSTYPE", DbType.Boolean, true); else db.AddInParameter(cmdInsert, "@BSTYPE", DbType.Boolean, false); db.AddInParameter(cmdInsert, "@FEEID", DbType.String, enumValue.CH_ID); db.AddInParameter(cmdInsert, "@FEENAME", DbType.String, enumValue.FeeName); db.AddInParameter(cmdInsert, "@CURRENCY", DbType.String, billcurr); if (billcurr == enumValue.Currency) { db.AddInParameter(cmdInsert, "@AMOUNT", DbType.Decimal, enumValue.StlAmount); db.AddInParameter(cmdInsert, "@DOAMOUNT", DbType.Decimal, 0); db.AddInParameter(cmdInsert, "@EXCHANGERATE", DbType.Decimal, 1); } else { var UExrate = GetExrate(enumValue.Currency, exratelist); db.AddInParameter(cmdInsert, "@AMOUNT", DbType.Decimal, enumValue.StlAmount * UExrate); db.AddInParameter(cmdInsert, "@DOAMOUNT", DbType.Decimal, 0); db.AddInParameter(cmdInsert, "@EXCHANGERATE", DbType.Decimal, UExrate); } db.AddInParameter(cmdInsert, "@FEETYPE", DbType.Int16, enumValue.FeeType); db.AddInParameter(cmdInsert, "@ORIGCURRENCY", DbType.String, enumValue.Currency); db.AddInParameter(cmdInsert, "@ORIGAMOUNT", DbType.Decimal, enumValue.StlAmount); db.AddInParameter(cmdInsert, "@CATEGORY", DbType.Int16, 8); db.AddInParameter(cmdInsert, "@BILLSTATUS", DbType.Int16, 1); db.AddInParameter(cmdInsert, "@ISDELETED", DbType.Boolean, false); db.AddInParameter(cmdInsert, "@CREATETIME", DbType.DateTime, DateTime.Now); db.AddInParameter(cmdInsert, "@INVOICESETTLENO", DbType.String, ""); db.AddInParameter(cmdInsert, "@COMPANYID", DbType.String, companyid); db.ExecuteNonQuery(cmdInsert, tran); cmdUpdate.Parameters.Clear(); db.AddInParameter(cmdUpdate, "@GID", DbType.String, enumValue.CH_ID); db.AddInParameter(cmdUpdate, "@SETTLEMENT", DbType.Decimal, enumValue.StlAmount); db.AddInParameter(cmdUpdate, "@SETTLEMENT2", DbType.Decimal, enumValue.StlAmount); db.AddInParameter(cmdUpdate, "@SETTLEMENT3", DbType.Decimal, enumValue.StlAmount); db.AddInParameter(cmdUpdate, "@SETTLEMENT4", DbType.Decimal, enumValue.StlAmount); db.ExecuteNonQuery(cmdUpdate, tran); } } result = new DBResult(); result.Success = true; result.Message = "成功"; tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "保存出现错误,请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "保存成功"; return result; } public static Decimal GetExrate(string curr, List exratelist) { decimal exrate = 0; if (exratelist != null) { foreach (var enumValue in exratelist) { if (enumValue.CURR == curr) { exrate = enumValue.EXRATE; } } } return exrate; } public static DBResult DelFeeDo(List boday) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); //var tran = conn.BeginTransaction(); try { if (boday != null) { // var cmddelete = db.GetSqlStringCommand("delete from ch_fee_do where GID=@FEEDOID"); // var cmdupdate = db.GetSqlStringCommand("update ch_fee set SETTLEMENT=SETTLEMENT-@SETTLEMENT,FEESTATUS=(case when AMOUNT<>0 AND AMOUNT=(SETTLEMENT-@SETTLEMENT2) then 9 WHEN AMOUNT<>0 AND AMOUNT<>(SETTLEMENT-@SETTLEMENT3) AND (SETTLEMENT-@SETTLEMENT4)<>0 THEN 8 ELSE 0 end) where GID=@GID"); // var cmdupdate = db.GetSqlStringCommand("update ch_fee set SETTLEMENT=SETTLEMENT-@SETTLEMENT,FEESTATUS=(case when AMOUNT<>0 AND AMOUNT=(SETTLEMENT-@SETTLEMENT2) then 9 WHEN AMOUNT<>0 AND AMOUNT<>(SETTLEMENT-@SETTLEMENT3) AND (SETTLEMENT-@SETTLEMENT4)<>0 THEN 8 ELSE 0 end) where GID=@GID"); var cmdupdatefeedo = db.GetSqlStringCommand("update ch_fee_do set DOAMOUNT=DOAMOUNT-ISNULL((SELECT SUM(PAYDOAMOUNT) FROM ch_fee_do_payapplication WHERE FEEDOID=@FEEDOID),0),ORIGSTLAMOUNT=ORIGSTLAMOUNT-@ORIGSTLAMOUNT where GID=(SELECT TOP 1 PAY_FEEDOID FROM ch_fee_do_payapplication WHERE FEEDOID=@PAYFEEDOID)"); var cmddeleteappfeedo = db.GetSqlStringCommand("delete from ch_fee_do_payapplication where FEEDOID=@FEEDOID"); foreach (var enumValue in boday) { cmdupdatefeedo.Parameters.Clear(); db.AddInParameter(cmdupdatefeedo, "@FEEDOID", DbType.String, enumValue.FEEDOID); db.AddInParameter(cmdupdatefeedo, "@ORIGSTLAMOUNT", DbType.Decimal, enumValue.ORIGAMOUNT); db.AddInParameter(cmdupdatefeedo, "@PAYFEEDOID", DbType.String, enumValue.FEEDOID); db.ExecuteNonQuery(cmdupdatefeedo); var cmd = db.GetStoredProcCommand("p_del_stlfeedo"); db.AddInParameter(cmd, "@feedoid", DbType.String, enumValue.FEEDOID); db.AddInParameter(cmd, "@feeid", DbType.String, enumValue.FEEID); db.ExecuteNonQuery(cmd); cmddeleteappfeedo.Parameters.Clear(); db.AddInParameter(cmddeleteappfeedo, "@FEEDOID", DbType.String, enumValue.FEEDOID); db.ExecuteNonQuery(cmddeleteappfeedo); //cmddelete.Parameters.Clear(); //db.AddInParameter(cmddelete, "@FEEDOID", DbType.String, enumValue.FEEDOID); //db.ExecuteNonQuery(cmddelete, tran); //cmdupdate.Parameters.Clear(); //db.AddInParameter(cmdupdate, "@SETTLEMENT", DbType.Decimal, enumValue.ORIGAMOUNT); //db.AddInParameter(cmdupdate, "@SETTLEMENT2", DbType.Decimal, enumValue.ORIGAMOUNT); //db.AddInParameter(cmdupdate, "@SETTLEMENT3", DbType.Decimal, enumValue.ORIGAMOUNT); //db.AddInParameter(cmdupdate, "@SETTLEMENT4", DbType.Decimal, enumValue.ORIGAMOUNT); //db.AddInParameter(cmdupdate, "@GID", DbType.String, enumValue.FEEID); //db.ExecuteNonQuery(cmdupdate, tran); } } result = new DBResult(); result.Success = true; result.Message = "成功"; //tran.Commit(); } catch (Exception e) { //tran.Rollback(); result.Success = false; result.Message = "删除出现错误("+e.Message+"),请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "删除成功"; return result; } public static int p_update_Amount(string billno,int billtype) { Database db = DatabaseFactory.CreateDatabase(); var cmd = db.GetStoredProcCommand("p_update_settlement"); db.AddInParameter(cmd, "@con_no", DbType.String, billno); db.AddInParameter(cmd, "@billtype", DbType.Int16, billtype); db.ExecuteNonQuery(cmd); return 0; } public static List GetFeeCurrList(string sCondition) { var strSql = new StringBuilder(); strSql.Append("Select CODENAME,DEFAULTRATE "); strSql.Append(" from code_currency "); if (!string.IsNullOrEmpty(sCondition)) { strSql.Append(" where " + sCondition); } strSql.Append(" order by CODENAME"); Database db = DatabaseFactory.CreateDatabase(); var evList = new List(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { var evData = new MsFeeCurr(); evData.CURR = Convert.ToString(reader["CODENAME"]); evData.DEFRATE = Convert.ToDecimal(reader["DEFAULTRATE"]); evList.Add(evData); } reader.Close(); } return evList; } #region 提交审核和撤销审核 public static DBResult Lock(string bill, string remark) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdupdate = db.GetSqlStringCommand("update ch_fee_hexiao set BILLSTATUS=1,REMARK=@REMARK where BILLNO=@BILLNO"); cmdupdate.Parameters.Clear(); db.AddInParameter(cmdupdate, "@REMARK", DbType.String, remark); db.AddInParameter(cmdupdate, "@BILLNO", DbType.String, bill); db.ExecuteNonQuery(cmdupdate, tran); var cmdupdate2 = db.GetSqlStringCommand("update ch_fee_settlement set BILLSTATUS=1 where HXBILLNO=@BILLNO"); cmdupdate2.Parameters.Clear(); db.AddInParameter(cmdupdate2, "@BILLNO", DbType.String, bill); db.ExecuteNonQuery(cmdupdate2, tran); result = new DBResult(); result.Success = true; result.Message = "锁定成功"; tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "锁定错误,请重试或联系系统管理员"; return result; } } return result; } public static DBResult LockList(String bills) { var result = new DBResult(); var billList = bills.Split(','); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { foreach (var bill in billList) { var cmdupdate = db.GetSqlStringCommand("update ch_fee_hexiao set BILLSTATUS=1 where BILLNO=@BILLNO"); cmdupdate.Parameters.Clear(); db.AddInParameter(cmdupdate, "@BILLNO", DbType.String, bill); db.ExecuteNonQuery(cmdupdate, tran); var cmdupdate2 = db.GetSqlStringCommand("update ch_fee_settlement set BILLSTATUS=1 where HXBILLNO=@BILLNO"); cmdupdate2.Parameters.Clear(); db.AddInParameter(cmdupdate2, "@BILLNO", DbType.String, bill); db.ExecuteNonQuery(cmdupdate2, tran); result = new DBResult(); result.Success = true; result.Message = "锁定成功"; } tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "锁定错误,请重试或联系系统管理员"; return result; } } return result; } static public int GetRdCount(string strCondition) { var strSql = new StringBuilder(); strSql.Append("SELECT Count(GID) AS CT from ch_fee_hexiao"); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } var ct = 0; Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { ct = Convert.ToInt16(reader["CT"]); } reader.Close(); } return ct; } public static DBResult UnLock(String bill) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdupdate = db.GetSqlStringCommand("update ch_fee_hexiao set BILLSTATUS=2 where BILLNO=@BILLNO"); cmdupdate.Parameters.Clear(); db.AddInParameter(cmdupdate, "@BILLNO", DbType.String, bill); db.ExecuteNonQuery(cmdupdate, tran); var cmdupdate2 = db.GetSqlStringCommand("update ch_fee_settlement set BILLSTATUS=2 where HXBILLNO=@BILLNO"); cmdupdate2.Parameters.Clear(); db.AddInParameter(cmdupdate2, "@BILLNO", DbType.String, bill); db.ExecuteNonQuery(cmdupdate2, tran); result = new DBResult(); result.Success = true; result.Message = "撤销锁定成功"; tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "撤销锁定错误,请重试或联系系统管理员"; return result; } } return result; } public static DBResult UnLockList(String bills) { var result = new DBResult(); var billList = bills.Split(','); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { foreach (var bill in billList) { var cmdupdate = db.GetSqlStringCommand("update ch_fee_hexiao set BILLSTATUS=2 where BILLNO=@BILLNO"); cmdupdate.Parameters.Clear(); db.AddInParameter(cmdupdate, "@BILLNO", DbType.String, bill); db.ExecuteNonQuery(cmdupdate, tran); var cmdupdate2 = db.GetSqlStringCommand("update ch_fee_settlement set BILLSTATUS=2 where HXBILLNO=@BILLNO"); cmdupdate2.Parameters.Clear(); db.AddInParameter(cmdupdate2, "@BILLNO", DbType.String, bill); db.ExecuteNonQuery(cmdupdate2, tran); result = new DBResult(); result.Success = true; result.Message = "撤销锁定成功"; } tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "撤销锁定错误,请重试或联系系统管理员"; return result; } } return result; } #endregion #region 权限范围 public static string GetRangDAStr(string tb, string userid, string username, string companyid) { string str = ""; var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append(" VISIBLERANGE,OPERATERANGE,AUTHORITYID "); strSql.Append(" from VW_User_Authority "); strSql.Append(" where [NAME]='" + tb + "' and USERID='" + userid + "' and ISDELETE=0"); string visiblerange = "4"; string operaterange = "4"; string AUTHORITYID = ""; Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { visiblerange = Convert.ToString(reader["VISIBLERANGE"]); operaterange = Convert.ToString(reader["OPERATERANGE"]); AUTHORITYID = Convert.ToString(reader["AUTHORITYID"]); break; } reader.Close(); } if (visiblerange == "4") { str = " (OP='" + username + "' OR INPUTBY='" + username + "' OR SALE='" + username + "' OR CUSTSERVICE='" + username + "')"; } else if (visiblerange == "3") { str = " (OP='" + username + "' OR INPUTBY='" + username + "' OR SALE='" + username + "' OR CUSTSERVICE='" + username + "')"; } else if (visiblerange == "2") { var rangeDa = new RangeDA(); var deptid = rangeDa.GetDEPTGID(userid); //str = " (OP in (select showname from vw_user where deptgid='" + deptid + "') OR SALE in (select showname from vw_user where deptgid='" + deptid + "') OR CUSTSERVICE in (select showname from vw_user where deptgid='" + deptid + "') " //+ " OR FRCUSTSERVICE in (select showname from vw_user where deptgid='" + deptid + "') OR INPUTBY in (select showname from vw_user where deptgid='" + deptid + "'))"; str = " exists( select 1 from vw_user where deptgid='" + deptid + "' and(showname=OP OR showname=SALE OR showname=CUSTSERVICE OR showname=FRCUSTSERVICE OR showname=INPUTBY)) "; } else if (visiblerange == "1") { //str = " (OP in (select showname from vw_user where companyid='" + companyid + "') OR SALE in (select showname from vw_user where companyid='" + companyid + "') OR CUSTSERVICE in (select showname from vw_user where companyid='" + companyid + "') " //+ " OR FRCUSTSERVICE in (select showname from vw_user where companyid='" + companyid + "') OR INPUTBY in (select showname from vw_user where companyid='" + companyid + "'))"; str = " exists( select 1 from vw_user where companyid='" + companyid + "' and(showname=OP OR showname=SALE OR showname=CUSTSERVICE OR showname=v_op_bill.FRCUSTSERVICE OR showname=v_op_bill.INPUTBY)) "; } else if (visiblerange == "5") { var userstr = new StringBuilder(); userstr.Append(" select COMPANYID from user_authority_range_company where userid='" + userid + "' and AUTHORITYID='" + AUTHORITYID + "' and VISIBLERANGE=1"); Database userdb = DatabaseFactory.CreateDatabase(); using (IDataReader reader = userdb.ExecuteReader(CommandType.Text, userstr.ToString())) { str = ""; while (reader.Read()) { if (str == "") { str = " (b.Corpid='" + Convert.ToString(reader["COMPANYID"]) + "'"; } else { str = str + " or b.Corpid='" + Convert.ToString(reader["COMPANYID"]) + "'"; }; } str = str + ")"; reader.Close(); } } else if (visiblerange == "6") { var userstr = new StringBuilder(); userstr.Append(" select OPID,(select SHOWNAME from [user] where GID=user_authority_range_op.OPID) SHOWNAME from user_authority_range_op where userid='" + userid + "' and AUTHORITYID='" + AUTHORITYID + "' and VISIBLERANGE=1"); Database userdb = DatabaseFactory.CreateDatabase(); using (IDataReader reader = userdb.ExecuteReader(CommandType.Text, userstr.ToString())) { str = ""; while (reader.Read()) { if (str == "") { str = " (b.OP='" + Convert.ToString(reader["SHOWNAME"]) + "' OR b.SALE='" + Convert.ToString(reader["SHOWNAME"]) + "' OR b.CUSTSERVICE='" + Convert.ToString(reader["SHOWNAME"]) + "' OR b.INPUTBY='" + Convert.ToString(reader["SHOWNAME"]) + "' "; } else { str = str + " or b.OP='" + Convert.ToString(reader["SHOWNAME"]) + "' OR b.SALE='" + Convert.ToString(reader["SHOWNAME"]) + "' OR b.CUSTSERVICE='" + Convert.ToString(reader["SHOWNAME"]) + "' OR b.INPUTBY='" + Convert.ToString(reader["SHOWNAME"]) + "' "; }; } str = str + ")"; reader.Close(); } } else if (visiblerange == "0") { str = " 1=1 "; } return str; } public static string GetRangDAListStr(string tb, string userid, string username, string companyid) { string str = ""; var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append(" VISIBLERANGE,OPERATERANGE,AUTHORITYID "); strSql.Append(" from VW_User_Authority "); strSql.Append(" where [NAME]='"+tb+"' and USERID='" + userid + "' and ISDELETE=0"); string visiblerange = "4"; string operaterange = "4"; string AUTHORITYID = ""; Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { visiblerange = Convert.ToString(reader["VISIBLERANGE"]); operaterange = Convert.ToString(reader["OPERATERANGE"]); AUTHORITYID = Convert.ToString(reader["AUTHORITYID"]); break; } reader.Close(); } if (visiblerange == "4") { str = " (CREATEUSER='" + userid + "')"; } else if (visiblerange == "3") { str = " (CREATEUSER='" + userid + "')"; } else if (visiblerange == "2") { var rangeDa = new RangeDA(); var deptid = rangeDa.GetDEPTGID(userid); str = " CREATEUSER in (select USERID from vw_user where deptid='" + deptid + "') "; } else if (visiblerange == "1") { str = " CREATEUSER in (select USERID from vw_user where COMPANYID='" + companyid + "')"; } else if (visiblerange == "5") { var userstr = new StringBuilder(); userstr.Append(" select COMPANYID from user_authority_range_company where userid='" + userid + "' and AUTHORITYID='" + AUTHORITYID + "' and VISIBLERANGE=1"); Database userdb = DatabaseFactory.CreateDatabase(); using (IDataReader reader = userdb.ExecuteReader(CommandType.Text, userstr.ToString())) { str = ""; while (reader.Read()) { if (str == "") { str = " (COMPANYID='" + Convert.ToString(reader["COMPANYID"]) + "'"; } else { str = str + " or COMPANYID='" + Convert.ToString(reader["COMPANYID"]) + "'"; }; } str = str + ")"; reader.Close(); } } else if (visiblerange == "6") { var userstr = new StringBuilder(); userstr.Append(" select OPID,(select SHOWNAME from [user] where GID=user_authority_range_op.OPID) SHOWNAME from user_authority_range_op where userid='" + userid + "' and AUTHORITYID='" + AUTHORITYID + "' and VISIBLERANGE=1"); Database userdb = DatabaseFactory.CreateDatabase(); using (IDataReader reader = userdb.ExecuteReader(CommandType.Text, userstr.ToString())) { str = ""; while (reader.Read()) { if (str == "") { str = " (CREATEUSER='" + Convert.ToString(reader["OPID"]) + "'"; } else { str = str + " or CREATEUSER='" + Convert.ToString(reader["OPID"]) + "'"; }; } str = str + ")"; reader.Close(); } } else if (visiblerange == "0") { str = " 1=1 "; } return str; } #endregion #region 生成接口凭证 public static DBResult onAddCwVouchersJK(string strGIDS, MsCwVouchersGl3 headData, List bodyList, string strUserID, string strSHOWNAME, string companyid) { var result = new DBResult(); T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA(); string sSQL = ""; int iResult = 0; string zt = ""; string sError = "操作完成!"; // if (strGIDS.Trim() != "") { //帐套设置 sSQL = "SELECT top 1 * from cw_design"; DataSet dsCwDesign = T_ALL_DA.GetAllSQL(sSQL); if (dsCwDesign == null) { result.Success = false; result.Message = "请先设置帐套信息!" + result.Message; return result; } // string sGids = strGIDS.Trim().Replace(",", "','"); sGids = "'" + sGids.Trim() + "'"; //合并生成结算凭证 CwVouchersAddingDo CwVouchersAddingDo = new CW.CwVouchersAddingDo(); string[] items = sGids.Trim().Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries); if (items.Length > 0) { for (int i = 0; i < items.Length; i++) { DBResult result2 = GetIsVoucherJK(items[i].ToString().Trim().Replace("'", "")); if (!result2.Success) { iResult = -1; } else { var BLCOUNT = T_ALL_DA.GetStrSQL("BLCOUNT", "select count(*) BLCOUNT from ch_fee_settlement WHERE HXBILLNO='" + items[i].ToString().Trim().Replace("'", "") + "' AND (ISNULL(AMOUNTRMB,0)<>0 OR ISNULL(AMOUNTUSD,0)<>0 OR ISNULL(FINANCIAL_MONEY,0)<>0 OR ISNULL(ADVANCE_MONEY,0)<>0) "); if (BLCOUNT == "0") { iResult = 1; } else iResult = getSC(dsCwDesign, items[i].ToString().Trim(), bodyList, headData.VOUDATE.ToString(), headData.PREPARED.ToString(), strUserID, companyid); } zt += "," + iResult; } } // if (zt.IndexOf("-") > 0) { result.Success = false; result.Message = "部分凭证生成出错!" + result.Message; return result; } } // result.Success = true; result.Message = sError;//"操作完成!"; return result; } protected static String getgid() { string strgid = Guid.NewGuid().ToString(); strgid = strgid.Replace("-", ""); strgid = "VOU" + strgid; return strgid; } public static int getSC(DataSet dsCwDesign, string sGids,List bodyList, string sVOUDATE, string sPREPARED, string userid, string strCompanyID)//, string strEXPLAN, string strDEPTID, string strEMPLID, string strSALE) { #region 基本变量 //全部 string sSQL = ""; string sSQL_Y = ""; string sSQL_Z = ""; string strEXPLAN = ""; int iResult = 0; int n = 0; Decimal dEXCHANGERATE = 0; //应收应付账款 Decimal iAMTDR = 0; Decimal iAMTCR = 0; //差额主营业务收入 Decimal dRMBS = 0; Decimal dRMBF = 0; Decimal dUSDS = 0; Decimal dUSDF = 0; //总合计 Decimal strJie = 0; Decimal strDai = 0; string ls = ""; string strBPVW = dsCwDesign.Tables[0].Rows[0]["TVW"].ToString().Trim(); #endregion T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA(); #region 其他常用科目设置 //其他常用科目设置 应收RMB sSQL = "SELECT top 1 * from cw_accitems where ACCID='" + dsCwDesign.Tables[0].Rows[0]["AR"].ToString().Trim() + "'"; DataSet dsAR = T_ALL_DA.GetAllSQL(sSQL); //其他常用科目设置 应收USD sSQL = "SELECT top 1 * from cw_accitems where ACCID='" + dsCwDesign.Tables[0].Rows[0]["ARFC"].ToString().Trim() + "'"; DataSet dsARFC = T_ALL_DA.GetAllSQL(sSQL); //其他常用科目设置 应付RMB sSQL = "SELECT top 1 * from cw_accitems where ACCID='" + dsCwDesign.Tables[0].Rows[0]["AP"].ToString().Trim() + "'"; DataSet dsAP = T_ALL_DA.GetAllSQL(sSQL); //其他常用科目设置 应付USD sSQL = "SELECT top 1 * from cw_accitems where ACCID='" + dsCwDesign.Tables[0].Rows[0]["APFC"].ToString().Trim() + "'"; DataSet dsAPFC = T_ALL_DA.GetAllSQL(sSQL); //其他常用科目设置 付费结算借方默认科目代码 sSQL = "SELECT top 1 * from cw_accitems where ACCID='" + dsCwDesign.Tables[0].Rows[0]["APMBSPENDING"].ToString().Trim() + "'"; DataSet dsAPMBSPENDING = T_ALL_DA.GetAllSQL(sSQL); //其他常用科目设置 付费结算外币借方默认科目代码 sSQL = "SELECT top 1 * from cw_accitems where ACCID='" + dsCwDesign.Tables[0].Rows[0]["APTMSFC"].ToString().Trim() + "'"; DataSet dsAPTMSFC = T_ALL_DA.GetAllSQL(sSQL); //其他常用科目设置 汇兑损益 sSQL = "SELECT top 1 * from cw_accitems where ACCID='" + dsCwDesign.Tables[0].Rows[0]["EGOL"].ToString().Trim() + "'"; DataSet dsEGOL = T_ALL_DA.GetAllSQL(sSQL); //财务费用 sSQL = "SELECT top 1 * from cw_accitems where ACCID='" + dsCwDesign.Tables[0].Rows[0]["FC"].ToString().Trim() + "'"; DataSet dsFC= T_ALL_DA.GetAllSQL(sSQL); #endregion #region 摘要 //ISRPCLIENTNAME 是否收付客户名称 //ISRPCOTCODE 是否收付业务编号 //ISRPETD 是否收付航期 //ISRPMBLNO 是否收付主提单号 //ISRPCUSTNO 是否收付委托编号 //ISRPVESSEL 是否收付船名 //ISRPVOYNO 是否收付航次 string strISEXPLAN = ""; if (dsCwDesign.Tables[0].Rows[0]["ISRPEXPLANMODEL"].ToString().Trim() == "True") { string sRPEXPLANAR = dsCwDesign.Tables[0].Rows[0]["RPEXPLANAR"].ToString().Trim(); string sRPEXPLANAP = dsCwDesign.Tables[0].Rows[0]["RPEXPLANAP"].ToString().Trim(); //if (sRPEXPLANAR.IndexOf("《结算单位》") > -1 || sRPEXPLANAP.IndexOf("《结算单位》") > -1) //{ // strISEXPLAN += ",CUSTOMERNAME"; //} if (sRPEXPLANAR.IndexOf("《委托编号》") > -1 || sRPEXPLANAP.IndexOf("《委托编号》") > -1) { strISEXPLAN += ",CUSTNO"; } if (sRPEXPLANAR.IndexOf("《主提单号》") > -1 || sRPEXPLANAP.IndexOf("《主提单号》") > -1) { strISEXPLAN += ",MBLNO"; } if (sRPEXPLANAR.IndexOf("《船名》") > -1 || sRPEXPLANAP.IndexOf("《船名》") > -1) { strISEXPLAN += ",VESSEL"; } if (sRPEXPLANAR.IndexOf("《航次》") > -1 || sRPEXPLANAP.IndexOf("《航次》") > -1) { strISEXPLAN += ",VOYNO"; } if (sRPEXPLANAR.IndexOf("《申请单号》") > -1 || sRPEXPLANAP.IndexOf("《申请单号》") > -1) { strISEXPLAN += ",BILLNO_ACAD"; } if (sRPEXPLANAR.IndexOf("《委托单位》") > -1 || sRPEXPLANAP.IndexOf("《委托单位》") > -1) { strISEXPLAN += ",BS_CUSTOMERNAME"; } } else { if (dsCwDesign.Tables[0].Rows[0]["ISRPCUSTNO"].ToString().Trim() == "True") { strISEXPLAN += ",CUSTNO"; } if (dsCwDesign.Tables[0].Rows[0]["ISRPMBLNO"].ToString().Trim() == "True") { strISEXPLAN += ",MBLNO"; } if (dsCwDesign.Tables[0].Rows[0]["ISRPVESSEL"].ToString().Trim() == "True") { strISEXPLAN += ",VESSEL"; } if (dsCwDesign.Tables[0].Rows[0]["ISRPVOYNO"].ToString().Trim() == "True") { strISEXPLAN += ",VOYNO"; } if (dsCwDesign.Tables[0].Rows[0]["ISRPBILLNO"].ToString().Trim() == "True") { strISEXPLAN += ",BILLNO_ACAD"; } if (dsCwDesign.Tables[0].Rows[0]["ISRPCUSTOMERNAME"].ToString().Trim() == "True") { strISEXPLAN += ",BS_CUSTOMERNAME"; } } #endregion #region 基本数据集 var tbRATE = " CASE WHEN CURR='RMB' THEN 1 ELSE "; if (bodyList != null) { if (bodyList.Count > 0) { tbRATE += " CASE CURR"; for (int i = 0; i < bodyList.Count; i++) { tbRATE += " WHEN '" + bodyList[i].CURR.ToString().Trim() + "' THEN " + bodyList[i].FCYEXRATE.ToString().Trim()+" "; } tbRATE += " ELSE 1 END "; } } tbRATE += " END "; string strORDNO = getgid(); CwVouchersDA CwVouchersDA = new CwVouchersDA(); IList CwVouitemsEntities = new List(); // //(CASE WHEN FEETYPE = 1 THEN '应收账款' ELSE '应付账款' END) as 类型 //,CUSTOMERNAME 客户名称 //,EXCHANGERATE 汇率 //,CURRENCY 币别` //,ISADVANCEDPAY 是否垫付 //,SUM(AMOUNT) AS 金额 //,SUM(AMOUNT*EXCHANGERATE) AS 合计金额 //是否按照系统录入汇率 #region 应收应付 sSQL = "SELECT BILLNO,(CASE WHEN FEETYPE = 1 THEN '应收账款' WHEN FEETYPE=2 THEN '应付账款' ELSE '' END) as FEETYPE" + ",CUSTOMERNAME,origexchangerate" + ",SUM(CASE WHEN ORIGCURRENCY='RMB' THEN ORIGAMOUNT ELSE ORIGAMOUNT*ORIGEXCHANGERATE END) AS hj"// + strISEXPLAN + " From v_fee_do_settlement_bs WHERE 1=1 " + " and HXBILLNO=" + sGids.Trim() + " " + " GROUP BY BILLNO,FEETYPE,CUSTOMERNAME,origexchangerate" + strISEXPLAN + " ORDER BY FEETYPE desc";//,CURRENCY #endregion //对抵结算是否生成银行账款 #region 现金、账户合计 sSQL_Z = "select BILLNO,BILLTYPE,SETTLETYPE,CUSTOMERNAME,CURR,isnull((CASE WHEN CURR='RMB' THEN AMOUNTRMB ELSE AMOUNTUSD END),0) AMOUNT, ACCOUNTRMB,ACCOUNTUSD,isnull(FINANCIAL_MONEY,0) FINANCIAL_MONEY,isnull(ADVANCE_MONEY,0) ADVANCE_MONEY"//,EXCHANGERATE + ",isnull((select top 1 ORIGEXCHANGERATE from v_fee_do_settlement_bs where BILLNO = ch_fee_settlement.BILLNO),0) as ORIGEXCHANGERATE" + ",isnull((CASE WHEN CURR='RMB' THEN AMOUNTRMB+isnull(FINANCIAL_MONEY,0)+isnull(ADVANCE_MONEY,0) ELSE AMOUNTUSD+isnull(FINANCIAL_MONEY,0)+isnull(ADVANCE_MONEY,0) END),0)* " + tbRATE + " as hj," + tbRATE +" as EXRATE " + " ,isnull((select SUM(CASE WHEN ORIGCURRENCY='RMB' THEN CASE WHEN FEETYPE=BILLTYPE THEN ORIGAMOUNT ELSE -ORIGAMOUNT END ELSE CASE WHEN FEETYPE=BILLTYPE THEN ORIGAMOUNT*ORIGEXCHANGERATE ELSE -ORIGAMOUNT*ORIGEXCHANGERATE END END) " //+ " ,isnull((select SUM(CASE WHEN ORIGCURRENCY='RMB' THEN CASE WHEN FEETYPE=1 THEN ORIGAMOUNT ELSE -ORIGAMOUNT END ELSE CASE WHEN FEETYPE=1 THEN ORIGAMOUNT*ORIGEXCHANGERATE ELSE -ORIGAMOUNT*ORIGEXCHANGERATE END END) " + " FROM v_fee_do_settlement_bs WHERE BILLNO=ch_fee_settlement.BILLNO ),0) as stlttl " + " From ch_fee_settlement WHERE 1=1 " + " and HXBILLNO=" + sGids.Trim() + " " + " ORDER BY BILLTYPE,CURR";// #endregion DataSet dsVWChFeeSettlementDo = T_ALL_DA.GetAllSQL(sSQL); DataSet dsVWChFeeSettlementDo_Z = T_ALL_DA.GetAllSQL(sSQL_Z); #endregion if (dsVWChFeeSettlementDo != null) { /////////////////////////////////////////////////////// #region 插入明细表 应收账款、应付账款 for (int i = 0; i < dsVWChFeeSettlementDo.Tables[0].Rows.Count; i++) { CwVouitemsEntity CwVouitemsEntity = new CwVouitemsEntity(); //CwVouitemsEntity.GID 唯一编码 newid() CwVouitemsEntity.ORDNO = strORDNO.Trim(); CwVouitemsEntity.ITEMNO = n;// 行号 CwVouitemsEntity.PACCID = "";//父级科目贷码 CwVouitemsEntity.PROPERTY = 0;//科目属性 CwVouitemsEntity.GRADE = 0;//科目级别 // Decimal dl = Decimal.Parse(dsVWChFeeSettlementDo.Tables[0].Rows[i]["hj"].ToString().Trim()); if (dl != 0) { n = n + 1; if (dsVWChFeeSettlementDo.Tables[0].Rows[i]["FEETYPE"].ToString().Trim() == "应收账款") { #region 应收账款 if (dsAR != null) { CwVouitemsEntity.ACCID = dsCwDesign.Tables[0].Rows[0]["AR"].ToString().Trim();//应收账款科目贷码 if (dsAR.Tables[0].Rows[0]["DC"].ToString().Trim() == "贷") { CwVouitemsEntity.AMTDR = Math.Round(Decimal.Parse(dsVWChFeeSettlementDo.Tables[0].Rows[i]["hj"].ToString().Trim()),2);//本位币借方金额(互斥) CwVouitemsEntity.AMTCR = 0;//本位币贷方金额(互斥) CwVouitemsEntity.FCYDR = 0;//外币借方金额 CwVouitemsEntity.FCYCR = 0;//外币贷方金额 CwVouitemsEntity.DC = "D";//借方向 iAMTDR = iAMTDR + dl;//预收支资料_金额 strJie +=Math.Round( Decimal.Parse(dsVWChFeeSettlementDo.Tables[0].Rows[i]["hj"].ToString().Trim()),2); } else { CwVouitemsEntity.AMTDR = 0;//本位币贷方金额(互斥) CwVouitemsEntity.AMTCR = Math.Round(Decimal.Parse(dsVWChFeeSettlementDo.Tables[0].Rows[i]["hj"].ToString().Trim()),2);//本位币借方金额(互斥) CwVouitemsEntity.FCYDR = 0;//外币借方金额 CwVouitemsEntity.FCYCR = 0;//外币贷方金额 CwVouitemsEntity.DC = "C";//贷方向 iAMTCR += dl;//预收支资料_金额 strDai += Math.Round(Decimal.Parse(dsVWChFeeSettlementDo.Tables[0].Rows[i]["hj"].ToString().Trim()),2); } CwVouitemsEntity.ISFCY = false;//是否外币 CwVouitemsEntity.FCYNO = "RMB";//外币编号usd dRMBS += Math.Round(Decimal.Parse(dsVWChFeeSettlementDo.Tables[0].Rows[i]["hj"].ToString().Trim()),2);//本位币借方金额(互斥) // CwVouitemsEntity.ACCNAME = dsAR.Tables[0].Rows[0]["ACCNAME"].ToString().Trim();//科目名称 CwVouitemsEntity.ISDEPTACC = Boolean.Parse(dsAR.Tables[0].Rows[0]["ISDEPTACC"].ToString().Trim());//核算部门,按规则设置生成 CwVouitemsEntity.ISEMPLACC = Boolean.Parse(dsAR.Tables[0].Rows[0]["ISEMPLACC"].ToString().Trim());//核算人员,按规则设置生成 CwVouitemsEntity.ISCORPACC = Boolean.Parse(dsAR.Tables[0].Rows[0]["ISCORPACC"].ToString().Trim());//核算客户,按规则设置生成 CwVouitemsEntity.ISITEMACC = Boolean.Parse(dsAR.Tables[0].Rows[0]["ISITEMACC"].ToString().Trim());//核算项目,按规则设置生成 } else { continue; } CwVouitemsEntity.CORPID ="";//客户(系统) CwVouitemsEntity.CUSTOMERNAME = dsVWChFeeSettlementDo.Tables[0].Rows[i]["CUSTOMERNAME"].ToString().Trim();//客户(系统) #endregion strEXPLAN = "收"; strEXPLAN += "/"+dsVWChFeeSettlementDo.Tables[0].Rows[i]["CUSTOMERNAME"].ToString().Trim(); if (dsCwDesign.Tables[0].Rows[0]["ISRPCUSTNO"].ToString().Trim() == "True") strEXPLAN += "/" + dsVWChFeeSettlementDo.Tables[0].Rows[i]["CUSTNO"].ToString().Trim(); if (dsCwDesign.Tables[0].Rows[0]["ISRPMBLNO"].ToString().Trim() == "True") strEXPLAN += "/" + dsVWChFeeSettlementDo.Tables[0].Rows[i]["MBLNO"].ToString().Trim(); } else if (dsVWChFeeSettlementDo.Tables[0].Rows[i]["FEETYPE"].ToString().Trim() == "应付账款") { #region 应付账款 if (dsAP != null) { if (dsCwDesign.Tables[0].Rows[0]["ISAPMBSPENDING"].ToString().Trim() == "True") { CwVouitemsEntity.ACCID = dsCwDesign.Tables[0].Rows[0]["APMBSPENDING"].ToString().Trim();//应付账款科目贷码 } else { CwVouitemsEntity.ACCID = dsCwDesign.Tables[0].Rows[0]["AP"].ToString().Trim();//应付账款科目贷码 } if (dsAP.Tables[0].Rows[0]["DC"].ToString().Trim() == "贷") { CwVouitemsEntity.AMTDR = Math.Round(Decimal.Parse(dsVWChFeeSettlementDo.Tables[0].Rows[i]["hj"].ToString().Trim()),2);//本位币贷方金额(互斥) CwVouitemsEntity.AMTCR = 0;//本位币借方金额(互斥) CwVouitemsEntity.FCYDR = 0;//外币借方金额 CwVouitemsEntity.FCYCR = 0;//外币贷方金额 // CwVouitemsEntity.FCYCR = Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["AMOUNT"].ToString().Trim()); CwVouitemsEntity.DC = "D";//借方向 iAMTDR = iAMTDR + dl; strJie += Math.Round(Decimal.Parse(dsVWChFeeSettlementDo.Tables[0].Rows[i]["hj"].ToString().Trim()),2); } else { CwVouitemsEntity.AMTDR = 0;//本位币借方金额(互斥) CwVouitemsEntity.AMTCR = Math.Round(Decimal.Parse(dsVWChFeeSettlementDo.Tables[0].Rows[i]["hj"].ToString().Trim()),2);//本位币贷方金额(互斥) CwVouitemsEntity.FCYDR = 0;//外币借方金额 //CwVouitemsEntity.FCYDR = Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["AMOUNT"].ToString().Trim()); CwVouitemsEntity.FCYCR = 0;//外币贷方金额 CwVouitemsEntity.DC = "C";//贷方向 iAMTCR = iAMTCR + dl; strDai += Math.Round(Decimal.Parse(dsVWChFeeSettlementDo.Tables[0].Rows[i]["hj"].ToString().Trim()),2); } CwVouitemsEntity.ISFCY = false;//是否外币 CwVouitemsEntity.FCYNO = "RMB";//外币编号usd dRMBF += Math.Round(Decimal.Parse(dsVWChFeeSettlementDo.Tables[0].Rows[i]["hj"].ToString().Trim()),2);//本位币贷方金额(互斥) if (dsCwDesign.Tables[0].Rows[0]["ISAPMBSPENDING"].ToString().Trim() == "True") { if (dsAPMBSPENDING == null) { CwVouitemsEntity.ACCNAME = "";//科目名称 } else { CwVouitemsEntity.ACCNAME = dsAPMBSPENDING.Tables[0].Rows[0]["ACCNAME"].ToString().Trim();//科目名称 } CwVouitemsEntity.CORPID = "";//客户(系统) CwVouitemsEntity.CUSTOMERNAME = "";//客户(系统) CwVouitemsEntity.ISCORPACC = false;//核算客户,按规则设置生成 } else { CwVouitemsEntity.ACCNAME = dsAP.Tables[0].Rows[0]["ACCNAME"].ToString().Trim();//科目名称 CwVouitemsEntity.CORPID ="";//客户(系统) CwVouitemsEntity.CUSTOMERNAME = dsVWChFeeSettlementDo.Tables[0].Rows[i]["CUSTOMERNAME"].ToString().Trim();//客户(系统) CwVouitemsEntity.ISCORPACC = Boolean.Parse(dsAP.Tables[0].Rows[0]["ISCORPACC"].ToString().Trim());//核算客户,按规则设置生成 } CwVouitemsEntity.ISDEPTACC = Boolean.Parse(dsAP.Tables[0].Rows[0]["ISDEPTACC"].ToString().Trim());//核算部门,按规则设置生成 CwVouitemsEntity.ISEMPLACC = Boolean.Parse(dsAP.Tables[0].Rows[0]["ISEMPLACC"].ToString().Trim());//核算人员,按规则设置生成 CwVouitemsEntity.ISITEMACC = Boolean.Parse(dsAP.Tables[0].Rows[0]["ISITEMACC"].ToString().Trim());//核算项目,按规则设置生成 } else { continue; } #endregion strEXPLAN = "付"; strEXPLAN += "/" + dsVWChFeeSettlementDo.Tables[0].Rows[i]["CUSTOMERNAME"].ToString().Trim(); if (dsCwDesign.Tables[0].Rows[0]["ISRPCUSTNO"].ToString().Trim() == "True") strEXPLAN += "/" + dsVWChFeeSettlementDo.Tables[0].Rows[i]["CUSTNO"].ToString().Trim(); if (dsCwDesign.Tables[0].Rows[0]["ISRPMBLNO"].ToString().Trim() == "True") strEXPLAN += "/" + dsVWChFeeSettlementDo.Tables[0].Rows[i]["MBLNO"].ToString().Trim(); } } else { continue; } // CwVouitemsEntity.FCYEXRATE = Decimal.Parse(dsVWChFeeSettlementDo.Tables[0].Rows[i]["origexchangerate"].ToString().Trim());//汇率 CwVouitemsEntity.FCYEXRATE =1;//汇率 //dEXCHANGERATE = Decimal.Parse(dsVWChFeeSettlementDo.Tables[0].Rows[i]["ACCOUNTS_RATE"].ToString().Trim());//汇率 CwVouitemsEntity.QTYUNIT = "";//数量包装单位 CwVouitemsEntity.QTYDR = 0;//借方数量 CwVouitemsEntity.QTYCR = 0;//贷方数量 CwVouitemsEntity.FSETTLCODE = "";//结算方式 // CwVouitemsEntity.EXPLAN = strEXPLAN;//摘要,规则生成 //strEXPLANALL = strEXPLAN; CwVouitemsEntity.DEPTID = "";//strDEPTID;//部门(系统) CwVouitemsEntity.EMPLID = "";//strEMPLID;//人员(系统) CwVouitemsEntity.SALE = "";//strSALE;//人员名称 CwVouitemsEntity.PITEMID = 0;//父级项目编号 CwVouitemsEntity.ITEMID = "0";//项目编号 CwVouitemsEntity.FIELD1 = "";//预留字段1 CwVouitemsEntity.FIELD2 = "";//预留字段2 CwVouitemsEntity.FIELD3 = "";//预留字段3 CwVouitemsEntity.FIELD4 = "";//预留字段4 CwVouitemsEntity.FIELD5 = "";//预留字段5 CwVouitemsEntity.FIELD6 = "";//预留字段6 CwVouitemsEntity.FIELD7 = "";//预留字段7 CwVouitemsEntity.FIELD8 = "";//预留字段8 CwVouitemsEntity.FIELD9 = "";//预留字段9 CwVouitemsEntity.FIELD10 = "";//预留字段10 if (dsCwDesign.Tables[0].Rows[0]["ISVBM"].ToString().Trim() == "True") { CwVouitemsEntity.ISSYS = false;//是否手动录入 } else { CwVouitemsEntity.ISSYS = true;//是否手动录入 } CwVouitemsEntity.FSETTLENO = "";//财务辅助编号 CwVouitemsEntity.MODIFIEDUSER = userid;//最后一次更新操作人 CwVouitemsEntity.BLNO = "";//BILLNO if (dsCwDesign.Tables[0].Rows[0]["ISRPMBLNO"].ToString().Trim() == "True") { CwVouitemsEntity.BLNO = dsVWChFeeSettlementDo.Tables[0].Rows[i]["mblno"].ToString().Trim();// } CwVouitemsEntities.Add(CwVouitemsEntity); } #endregion /////////////////////////////////////////////////////// //end /////////////////////////////////////////////////////// #region 插入明细表 实付支资料 账户,收入\成本 生成 全额\差额 for (int i = 0; i < dsVWChFeeSettlementDo_Z.Tables[0].Rows.Count; i++) { CwVouitemsEntity CwVouitemsEntity2 = new CwVouitemsEntity(); //CwVouitemsEntity2.GID 唯一编码 newid() CwVouitemsEntity2.ORDNO = strORDNO.Trim(); CwVouitemsEntity2.ITEMNO = n;// 行号 CwVouitemsEntity2.PACCID = "";//父级科目贷码 CwVouitemsEntity2.PROPERTY = 0;//科目属性 CwVouitemsEntity2.GRADE = 0;//科目级别 #region 2018年2月12日10:16:50 李进举 凭证生成汇率 // CwVouitemsEntity2.FCYEXRATE = decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ORIGEXCHANGERATE"].ToString()==""?"0":dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ORIGEXCHANGERATE"].ToString().Trim()); CwVouitemsEntity2.FCYEXRATE = decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["EXRATE"].ToString() == "" ? "0" : dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["EXRATE"].ToString().Trim()); #endregion Decimal dl = Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["AMOUNT"].ToString().Trim())+ Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["FINANCIAL_MONEY"].ToString().Trim()) + Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ADVANCE_MONEY"].ToString().Trim()); if (dl != 0) { if (dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["BILLTYPE"].ToString().Trim() == "1") { #region 实收支资料 if (dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["CURR"].ToString().Trim() == "RMB") { ls = "select FINANCESOFTCODE from sys_bank where gid='" + dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ACCOUNTRMB"].ToString().Trim() + "'"; string strACCID = T_ALL_DA.GetStrSQL("FINANCESOFTCODE", ls); if (strACCID.Trim() == "") { strACCID = "1001"; continue; } CwVouitemsEntity2.ACCID = strACCID.Trim();//科目贷码 ls = "select BANKNAME from sys_bank where gid='" + dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ACCOUNTRMB"].ToString().Trim() + "'"; string strACCNAME = T_ALL_DA.GetStrSQL("BANKNAME", ls); if (strACCNAME.Trim() == "") { strACCNAME = "现金"; continue; } CwVouitemsEntity2.ACCNAME = strACCNAME.Trim();//科目名称 // CwVouitemsEntity2.AMTDR = dl;//本位币贷方金额(互斥) CwVouitemsEntity2.AMTCR = 0;//本位币借方金额(互斥) CwVouitemsEntity2.FCYDR = 0;//外币借方金额 CwVouitemsEntity2.FCYCR = 0;//外币贷方金额 strJie += dl; // CwVouitemsEntity2.ISFCY = false;//是否外币 CwVouitemsEntity2.FCYNO = "RMB";//外币编号usd CwVouitemsEntity2.FCYEXRATE =1; CwVouitemsEntity2.ISDEPTACC = false;//核算部门,按规则设置生成 CwVouitemsEntity2.ISEMPLACC = false;//核算人员,按规则设置生成 CwVouitemsEntity2.ISCORPACC = false;//核算客户,按规则设置生成 CwVouitemsEntity2.ISITEMACC = false;//核算项目,按规则设置生成 CwVouitemsEntity2.DC = "D";//借方向 } else if (dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["CURR"].ToString().Trim() != "RMB") { ls = "select FINANCESOFTCODE from sys_bank where gid='" + dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ACCOUNTUSD"].ToString().Trim() + "'"; string strACCID = T_ALL_DA.GetStrSQL("FINANCESOFTCODE", ls); if (strACCID.Trim() == "") { strACCID = "1001"; continue; } CwVouitemsEntity2.ACCID = strACCID.Trim();//科目贷码 ls = "select BANKNAME from sys_bank where gid='" + dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ACCOUNTUSD"].ToString().Trim() + "'"; string strACCNAME = T_ALL_DA.GetStrSQL("BANKNAME", ls); if (strACCNAME.Trim() == "") { strACCNAME = "现金"; continue; } CwVouitemsEntity2.ACCNAME = strACCNAME.Trim();//科目名称 // CwVouitemsEntity2.AMTDR = Math.Round(dl * CwVouitemsEntity2.FCYEXRATE, 2);//本位币贷方金额(互斥) CwVouitemsEntity2.AMTCR = 0;//本位币借方金额(互斥) CwVouitemsEntity2.FCYDR = dl;//外币借方金额 CwVouitemsEntity2.FCYCR = 0;//外币贷方金额 strJie += Math.Round(dl * CwVouitemsEntity2.FCYEXRATE, 2); // CwVouitemsEntity2.ISFCY = true;//是否外币 CwVouitemsEntity2.FCYNO = "USD";//外币编号usd CwVouitemsEntity2.ISDEPTACC = false;//核算部门,按规则设置生成 CwVouitemsEntity2.ISEMPLACC = false;//核算人员,按规则设置生成 CwVouitemsEntity2.ISCORPACC = false;//核算客户,按规则设置生成 CwVouitemsEntity2.ISITEMACC = false;//核算项目,按规则设置生成 CwVouitemsEntity2.DC = "D";//借方向 } //CwVouitemsEntity2.CORPID = dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["FARCODE"].ToString().Trim();//客户(系统) //CwVouitemsEntity2.CUSTOMERNAME = dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["CUSTOMERNAME"].ToString().Trim();//客户(系统) CwVouitemsEntity2.CORPID = "";//客户(系统) CwVouitemsEntity2.CUSTOMERNAME = "";//客户(系统) strBPVW = dsCwDesign.Tables[0].Rows[0]["BRTW"].ToString().Trim(); #endregion strEXPLAN = "收" + dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["CUSTOMERNAME"].ToString().Trim(); } else if (dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["BILLTYPE"].ToString().Trim() == "2") { #region 实付支资料 if (dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["CURR"].ToString().Trim() == "RMB") { ls = "select FINANCESOFTCODE from sys_bank where gid='" + dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ACCOUNTRMB"].ToString().Trim() + "'"; string strACCID = T_ALL_DA.GetStrSQL("FINANCESOFTCODE", ls); if (strACCID.Trim() == "") { strACCID = "1001"; continue; } CwVouitemsEntity2.ACCID = strACCID.Trim();//科目贷码 ls = "select BANKNAME from sys_bank where gid='" + dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ACCOUNTRMB"].ToString().Trim() + "'"; string strACCNAME = T_ALL_DA.GetStrSQL("BANKNAME", ls); if (strACCNAME.Trim() == "") { strACCNAME = "现金"; continue; } CwVouitemsEntity2.ACCNAME = strACCNAME.Trim();//科目名称 // CwVouitemsEntity2.AMTDR = 0;//本位币贷方金额(互斥) CwVouitemsEntity2.AMTCR = dl;//本位币借方金额(互斥) CwVouitemsEntity2.FCYDR = 0;//外币借方金额 CwVouitemsEntity2.FCYCR = 0;//外币贷方金额 strDai +=dl; // CwVouitemsEntity2.ISFCY = false;//是否外币 CwVouitemsEntity2.FCYNO = "RMB";//外币编号usd CwVouitemsEntity2.FCYEXRATE = 1; CwVouitemsEntity2.ISDEPTACC = false;//核算部门,按规则设置生成 CwVouitemsEntity2.ISEMPLACC = false;//核算人员,按规则设置生成 CwVouitemsEntity2.ISCORPACC = false;//核算客户,按规则设置生成 CwVouitemsEntity2.ISITEMACC = false;//核算项目,按规则设置生成 CwVouitemsEntity2.DC = "C";//贷方向 } else if (dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["CURR"].ToString().Trim() != "RMB") { ls = "select FINANCESOFTCODE from sys_bank where gid='" + dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ACCOUNTUSD"].ToString().Trim() + "'"; string strACCID = T_ALL_DA.GetStrSQL("FINANCESOFTCODE", ls); if (strACCID.Trim() == "") { strACCID = "1001"; continue; } CwVouitemsEntity2.ACCID = strACCID.Trim();//科目贷码 ls = "select BANKNAME from sys_bank where gid='" + dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ACCOUNTUSD"].ToString().Trim() + "'"; string strACCNAME = T_ALL_DA.GetStrSQL("BANKNAME", ls); if (strACCNAME.Trim() == "") { strACCNAME = "现金"; continue; } CwVouitemsEntity2.ACCNAME = strACCNAME.Trim();//科目名称 // CwVouitemsEntity2.AMTDR = 0;//本位币贷方金额(互斥) CwVouitemsEntity2.AMTCR = Math.Round(dl * CwVouitemsEntity2.FCYEXRATE, 2);//本位币借方金额(互斥) CwVouitemsEntity2.FCYDR = 0;//外币借方金额 CwVouitemsEntity2.FCYCR = dl;//外币贷方金额 strDai += Math.Round(dl * CwVouitemsEntity2.FCYEXRATE, 2); // CwVouitemsEntity2.ISFCY = true;//是否外币 CwVouitemsEntity2.FCYNO = "USD";//外币编号usd CwVouitemsEntity2.ISDEPTACC = false;//核算部门,按规则设置生成 CwVouitemsEntity2.ISEMPLACC = false;//核算人员,按规则设置生成 CwVouitemsEntity2.ISCORPACC = false;//核算客户,按规则设置生成 CwVouitemsEntity2.ISITEMACC = false;//核算项目,按规则设置生成 CwVouitemsEntity2.DC = "C";//贷方向 } //CwVouitemsEntity2.CORPID = dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["FAPCODE"].ToString().Trim();//客户(系统) //CwVouitemsEntity2.CUSTOMERNAME = dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["CUSTOMERNAME"].ToString().Trim();//客户(系统) CwVouitemsEntity2.CORPID = "";//客户(系统) CwVouitemsEntity2.CUSTOMERNAME = "";//客户(系统) strBPVW = dsCwDesign.Tables[0].Rows[0]["BPVW"].ToString().Trim(); #endregion strEXPLAN = "付" + dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["CUSTOMERNAME"].ToString().Trim(); } } else { continue; } n = n + 1; CwVouitemsEntity2.QTYUNIT = "";//数量包装单位 CwVouitemsEntity2.QTYDR = 0;//借方数量 CwVouitemsEntity2.QTYCR = 0;//贷方数量 // CwVouitemsEntity2.EXPLAN = strEXPLAN;//摘要,规则生成ALL CwVouitemsEntity2.DEPTID = "";//strDEPTID;//部门(系统) CwVouitemsEntity2.EMPLID = "";//strEMPLID;//人员(系统) CwVouitemsEntity2.SALE = "";//strSALE;//人员名称 CwVouitemsEntity2.PITEMID = 0;//父级项目编号 CwVouitemsEntity2.ITEMID = "0";//项目编号 CwVouitemsEntity2.FIELD1 = "";//预留字段1 CwVouitemsEntity2.FIELD2 = "";//预留字段2 CwVouitemsEntity2.FIELD3 = "";//预留字段3 CwVouitemsEntity2.FIELD4 = "";//预留字段4 CwVouitemsEntity2.FIELD5 = "";//预留字段5 CwVouitemsEntity2.FIELD6 = "";//预留字段6 CwVouitemsEntity2.FIELD7 = "";//预留字段7 CwVouitemsEntity2.FIELD8 = "";//预留字段8 CwVouitemsEntity2.FIELD9 = "";//预留字段9 CwVouitemsEntity2.FIELD10 = "";//预留字段10 CwVouitemsEntity2.ISSYS = true;//是否手动录入 CwVouitemsEntity2.FSETTLCODE = "";//结算方式 CwVouitemsEntity2.FSETTLENO = "";//财务辅助编号 CwVouitemsEntity2.MODIFIEDUSER = userid;//最后一次更新操作人 CwVouitemsEntity2.BLNO = "";//BILLNOstrBILLNOALL //if (dsCwDesign.Tables[0].Rows[0]["ISRPMBLNO"].ToString().Trim() == "True") //{ // CwVouitemsEntity2.BLNO = dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["mblno"].ToString().Trim();//BILLNO //} CwVouitemsEntities.Add(CwVouitemsEntity2); } #endregion #region 插入明细表 财务费用 Decimal dlAMTDR = 0;//本位币贷方金额(互斥) Decimal dlAMTCR = 0;//本位币借方金额(互斥) Decimal dlFCYDR = 0;//外币借方金额 Decimal dlFCYCR = 0;//外币贷方金额 // for (int i = 0; i < dsVWChFeeSettlementDo_Z.Tables[0].Rows.Count; i++) { Decimal dl = Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["FINANCIAL_MONEY"].ToString().Trim()); if (dl != 0) { CwVouitemsEntity CwVouitemsEntity4 = new CwVouitemsEntity(); //CwVouitemsEntity4.GID 唯一编码 newid() CwVouitemsEntity4.ORDNO = strORDNO.Trim(); CwVouitemsEntity4.ITEMNO = n;// 行号 CwVouitemsEntity4.PACCID = "";//父级科目贷码 CwVouitemsEntity4.PROPERTY = 0;//科目属性 CwVouitemsEntity4.GRADE = 0;//科目级别 #region 2018年2月12日10:17:50 凭证汇率 李进举 // CwVouitemsEntity4.FCYEXRATE = decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ORIGEXCHANGERATE"].ToString() == "" ? "0" : dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ORIGEXCHANGERATE"].ToString().Trim()); CwVouitemsEntity4.FCYEXRATE = decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["EXRATE"].ToString() == "" ? "0" : dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["EXRATE"].ToString().Trim()); #endregion if (dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["BILLTYPE"].ToString().Trim() == "1") { if (dsFC != null) { #region 实收支资料 CwVouitemsEntity4.ACCID = dsCwDesign.Tables[0].Rows[0]["FC"].ToString().Trim(); CwVouitemsEntity4.ACCNAME = dsFC.Tables[0].Rows[0]["ACCNAME"].ToString().Trim();//科目名称 // if (dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["CURR"].ToString().Trim() == "RMB") { CwVouitemsEntity4.AMTDR = -Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["FINANCIAL_MONEY"].ToString().Trim()),2);//本位币贷方金额(互斥) CwVouitemsEntity4.AMTCR = 0;//本位币借方金额(互斥) CwVouitemsEntity4.FCYDR = 0;//外币借方金额 CwVouitemsEntity4.FCYCR = 0;//外币贷方金额 CwVouitemsEntity4.ISFCY = false;//是否外币 CwVouitemsEntity4.FCYNO = "RMB";//外币编号usd CwVouitemsEntity4.ISDEPTACC = false;//核算部门,按规则设置生成 CwVouitemsEntity4.ISEMPLACC = false;//核算人员,按规则设置生成 CwVouitemsEntity4.ISCORPACC = false;//核算客户,按规则设置生成 CwVouitemsEntity4.ISITEMACC = false;//核算项目,按规则设置生成 if (dsFC.Tables[0].Rows[0]["DC"].ToString().Trim() == "贷") { CwVouitemsEntity4.DC = "D";//借方向 } else { CwVouitemsEntity4.DC = "C";//借方向 } // strJie =strJie- Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["FINANCIAL_MONEY"].ToString().Trim()),2); dlAMTDR=dlAMTDR- Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["FINANCIAL_MONEY"].ToString().Trim()),2);//本位币贷方金额(互斥) dlAMTCR += 0;//本位币借方金额(互斥) dlFCYDR += 0;//外币借方金额 dlFCYCR += 0;//外币贷方金额 CwVouitemsEntity4.FCYEXRATE = 1;//汇率 } else { CwVouitemsEntity4.AMTDR = -Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["FINANCIAL_MONEY"].ToString().Trim()) * CwVouitemsEntity4.FCYEXRATE, 2);//本位币贷方金额(互斥) CwVouitemsEntity4.AMTCR = 0;//本位币借方金额(互斥) CwVouitemsEntity4.FCYDR = -Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["FINANCIAL_MONEY"].ToString().Trim()),2);//外币借方金额 CwVouitemsEntity4.FCYCR = 0;//外币贷方金额 CwVouitemsEntity4.ISFCY = true;//是否外币 CwVouitemsEntity4.FCYNO = dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["CURR"].ToString().Trim();//外币编号usd CwVouitemsEntity4.ISDEPTACC = false;//核算部门,按规则设置生成 CwVouitemsEntity4.ISEMPLACC = false;//核算人员,按规则设置生成 CwVouitemsEntity4.ISCORPACC = false;//核算客户,按规则设置生成 CwVouitemsEntity4.ISITEMACC = false;//核算项目,按规则设置生成 if (dsFC.Tables[0].Rows[0]["DC"].ToString().Trim() == "贷") { CwVouitemsEntity4.DC = "D";//借方向 } else { CwVouitemsEntity4.DC = "C";//借方向 } // strJie =strJie-Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["FINANCIAL_MONEY"].ToString().Trim()) * CwVouitemsEntity4.FCYEXRATE, 2); dlAMTDR =dlAMTDR- Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["FINANCIAL_MONEY"].ToString().Trim()) * CwVouitemsEntity4.FCYEXRATE, 2);//本位币贷方金额(互斥) dlAMTCR += 0;//本位币借方金额(互斥) dlFCYDR =dlFCYDR- Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["FINANCIAL_MONEY"].ToString().Trim()),2);//外币借方金额 dlFCYCR += 0;//外币贷方金额 } CwVouitemsEntity4.CORPID = "";//客户(系统) CwVouitemsEntity4.CUSTOMERNAME = "";//客户(系统) strBPVW = dsCwDesign.Tables[0].Rows[0]["BRTW"].ToString().Trim(); strEXPLAN = "收"; strEXPLAN += "/" + dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["CUSTOMERNAME"].ToString().Trim(); #endregion } } else if (dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["BILLTYPE"].ToString().Trim() == "2") { #region 实付支资料 CwVouitemsEntity4.ACCID = dsCwDesign.Tables[0].Rows[0]["FC"].ToString().Trim(); CwVouitemsEntity4.ACCNAME = dsFC.Tables[0].Rows[0]["ACCNAME"].ToString().Trim();//科目名称 // if (dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["CURR"].ToString().Trim() == "RMB") { CwVouitemsEntity4.AMTDR = Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["FINANCIAL_MONEY"].ToString().Trim()),2);//本位币贷方金额(互斥) CwVouitemsEntity4.AMTCR = 0;//本位币借方金额(互斥) CwVouitemsEntity4.FCYDR = 0;//外币借方金额 CwVouitemsEntity4.FCYCR = 0;//外币贷方金额 CwVouitemsEntity4.ISFCY = false;//是否外币 CwVouitemsEntity4.FCYNO = "RMB";//外币编号usd CwVouitemsEntity4.ISDEPTACC = false;//核算部门,按规则设置生成 CwVouitemsEntity4.ISEMPLACC = false;//核算人员,按规则设置生成 CwVouitemsEntity4.ISCORPACC = false;//核算客户,按规则设置生成 CwVouitemsEntity4.ISITEMACC = false;//核算项目,按规则设置生成 if (dsFC.Tables[0].Rows[0]["DC"].ToString().Trim() == "贷") { CwVouitemsEntity4.DC = "D";//借方向 } else { CwVouitemsEntity4.DC = "C";//借方向 } // strJie =strJie+Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["FINANCIAL_MONEY"].ToString().Trim()),2); dlAMTCR += 0;//本位币贷方金额(互斥) dlAMTDR =dlAMTDR+Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["FINANCIAL_MONEY"].ToString().Trim()),2);//本位币借方金额(互斥) dlFCYDR += 0;//外币借方金额 dlFCYCR += 0;//外币贷方金额 CwVouitemsEntity4.FCYEXRATE = 1;//汇率 } else { CwVouitemsEntity4.AMTDR = Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["FINANCIAL_MONEY"].ToString().Trim()) * CwVouitemsEntity4.FCYEXRATE, 2);//本位币贷方金额(互斥) CwVouitemsEntity4.AMTCR = 0;//本位币借方金额(互斥) CwVouitemsEntity4.FCYDR = Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["FINANCIAL_MONEY"].ToString().Trim()),2);//外币借方金额 CwVouitemsEntity4.FCYCR = 0;//外币贷方金额 CwVouitemsEntity4.ISFCY = true;//是否外币 CwVouitemsEntity4.FCYNO = "USD";//外币编号usd CwVouitemsEntity4.ISDEPTACC = false;//核算部门,按规则设置生成 CwVouitemsEntity4.ISEMPLACC = false;//核算人员,按规则设置生成 CwVouitemsEntity4.ISCORPACC = false;//核算客户,按规则设置生成 CwVouitemsEntity4.ISITEMACC = false;//核算项目,按规则设置生成 if (dsFC.Tables[0].Rows[0]["DC"].ToString().Trim() == "贷") { CwVouitemsEntity4.DC = "D";//借方向 } else { CwVouitemsEntity4.DC = "C";//借方向 } // strJie =strJie+Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["FINANCIAL_MONEY"].ToString().Trim()) * CwVouitemsEntity4.FCYEXRATE, 2); dlAMTCR += 0;//本位币贷方金额(互斥) dlAMTDR =dlAMTDR+Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["FINANCIAL_MONEY"].ToString().Trim()) * CwVouitemsEntity4.FCYEXRATE, 2);//本位币借方金额(互斥) dlFCYCR += 0;//外币借方金额 dlFCYDR =dlFCYDR+Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["FINANCIAL_MONEY"].ToString().Trim()),2);//外币贷方金额 } CwVouitemsEntity4.CORPID = "";//客户(系统) CwVouitemsEntity4.CUSTOMERNAME = "";//客户(系统) strBPVW = dsCwDesign.Tables[0].Rows[0]["BPVW"].ToString().Trim(); strEXPLAN = "付"; strEXPLAN += "/" + dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["CUSTOMERNAME"].ToString().Trim(); #endregion } n = n + 1; CwVouitemsEntity4.QTYUNIT = "";//数量包装单位 CwVouitemsEntity4.QTYDR = 0;//借方数量 CwVouitemsEntity4.QTYCR = 0;//贷方数量 // CwVouitemsEntity4.EXPLAN = strEXPLAN;//摘要,规则生成ALL CwVouitemsEntity4.DEPTID = "";//strDEPTID;//部门(系统) CwVouitemsEntity4.EMPLID = "";//strEMPLID;//人员(系统) CwVouitemsEntity4.SALE = "";//strSALE;//人员名称 CwVouitemsEntity4.PITEMID = 0;//父级项目编号 CwVouitemsEntity4.ITEMID = "0";//项目编号 CwVouitemsEntity4.FIELD1 = "";//预留字段1 CwVouitemsEntity4.FIELD2 = "";//预留字段2 CwVouitemsEntity4.FIELD3 = "";//预留字段3 CwVouitemsEntity4.FIELD4 = "";//预留字段4 CwVouitemsEntity4.FIELD5 = "";//预留字段5 CwVouitemsEntity4.FIELD6 = "";//预留字段6 CwVouitemsEntity4.FIELD7 = "";//预留字段7 CwVouitemsEntity4.FIELD8 = "";//预留字段8 CwVouitemsEntity4.FIELD9 = "";//预留字段9 CwVouitemsEntity4.FIELD10 = "";//预留字段10 CwVouitemsEntity4.ISSYS = true;//是否手动录入 CwVouitemsEntity4.FSETTLCODE = "";//结算方式 CwVouitemsEntity4.FSETTLENO = "";//财务辅助编号 CwVouitemsEntity4.MODIFIEDUSER = userid;//最后一次更新操作人 CwVouitemsEntity4.BLNO = "";//BILLNOstrBILLNOALL CwVouitemsEntities.Add(CwVouitemsEntity4); } } #endregion #region 插入明细表 财务费用 dlAMTDR = 0;//本位币贷方金额(互斥) dlAMTCR = 0;//本位币借方金额(互斥) dlFCYDR = 0;//外币借方金额 dlFCYCR = 0;//外币贷方金额 // for (int i = 0; i < dsVWChFeeSettlementDo_Z.Tables[0].Rows.Count; i++) { Decimal dl = Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ADVANCE_MONEY"].ToString().Trim()); if (dl != 0) { CwVouitemsEntity CwVouitemsEntity4 = new CwVouitemsEntity(); //CwVouitemsEntity4.GID 唯一编码 newid() CwVouitemsEntity4.ORDNO = strORDNO.Trim(); CwVouitemsEntity4.ITEMNO = n;// 行号 CwVouitemsEntity4.PACCID = "";//父级科目贷码 CwVouitemsEntity4.PROPERTY = 0;//科目属性 CwVouitemsEntity4.GRADE = 0;//科目级别 #region 2018年2月12日10:17:50 凭证汇率 李进举 // CwVouitemsEntity4.FCYEXRATE = decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ORIGEXCHANGERATE"].ToString() == "" ? "0" : dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ORIGEXCHANGERATE"].ToString().Trim()); CwVouitemsEntity4.FCYEXRATE = decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["EXRATE"].ToString() == "" ? "0" : dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["EXRATE"].ToString().Trim()); #endregion if (dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["BILLTYPE"].ToString().Trim() == "1") { if (dsFC != null) { #region 实收支资料 CwVouitemsEntity4.ACCID = dsCwDesign.Tables[0].Rows[0]["FC"].ToString().Trim(); CwVouitemsEntity4.ACCNAME = dsFC.Tables[0].Rows[0]["ACCNAME"].ToString().Trim();//科目名称 // if (dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["CURR"].ToString().Trim() == "RMB") { CwVouitemsEntity4.AMTDR = -Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ADVANCE_MONEY"].ToString().Trim()),2);//本位币贷方金额(互斥) CwVouitemsEntity4.AMTCR = 0;//本位币借方金额(互斥) CwVouitemsEntity4.FCYDR = 0;//外币借方金额 CwVouitemsEntity4.FCYCR = 0;//外币贷方金额 CwVouitemsEntity4.ISFCY = false;//是否外币 CwVouitemsEntity4.FCYNO = "RMB";//外币编号usd CwVouitemsEntity4.ISDEPTACC = false;//核算部门,按规则设置生成 CwVouitemsEntity4.ISEMPLACC = false;//核算人员,按规则设置生成 CwVouitemsEntity4.ISCORPACC = false;//核算客户,按规则设置生成 CwVouitemsEntity4.ISITEMACC = false;//核算项目,按规则设置生成 if (dsFC.Tables[0].Rows[0]["DC"].ToString().Trim() == "贷") { CwVouitemsEntity4.DC = "D";//借方向 } else { CwVouitemsEntity4.DC = "C";//借方向 } // strJie = strJie- Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ADVANCE_MONEY"].ToString().Trim()),2); dlAMTDR =dlAMTDR- Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ADVANCE_MONEY"].ToString().Trim()),2);//本位币贷方金额(互斥) dlAMTCR += 0;//本位币借方金额(互斥) dlFCYDR += 0;//外币借方金额 dlFCYCR += 0;//外币贷方金额 CwVouitemsEntity4.FCYEXRATE = 1;//汇率 } else { CwVouitemsEntity4.AMTDR = -Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ADVANCE_MONEY"].ToString().Trim()) * CwVouitemsEntity4.FCYEXRATE, 2);//本位币贷方金额(互斥) CwVouitemsEntity4.AMTCR = 0;//本位币借方金额(互斥) CwVouitemsEntity4.FCYDR = -Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ADVANCE_MONEY"].ToString().Trim()),2);//外币借方金额 CwVouitemsEntity4.FCYCR = 0;//外币贷方金额 CwVouitemsEntity4.ISFCY = true;//是否外币 CwVouitemsEntity4.FCYNO = dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["CURR"].ToString().Trim();//外币编号usd CwVouitemsEntity4.ISDEPTACC = false;//核算部门,按规则设置生成 CwVouitemsEntity4.ISEMPLACC = false;//核算人员,按规则设置生成 CwVouitemsEntity4.ISCORPACC = false;//核算客户,按规则设置生成 CwVouitemsEntity4.ISITEMACC = false;//核算项目,按规则设置生成 if (dsFC.Tables[0].Rows[0]["DC"].ToString().Trim() == "贷") { CwVouitemsEntity4.DC = "D";//借方向 } else { CwVouitemsEntity4.DC = "C";//借方向 } // strJie =strJie-Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ADVANCE_MONEY"].ToString().Trim()) * CwVouitemsEntity4.FCYEXRATE, 2); dlAMTDR =dlAMTDR-Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ADVANCE_MONEY"].ToString().Trim()) * CwVouitemsEntity4.FCYEXRATE, 2);//本位币贷方金额(互斥) dlAMTCR += 0;//本位币借方金额(互斥) dlFCYDR =dlFCYDR-Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ADVANCE_MONEY"].ToString().Trim()),2);//外币借方金额 dlFCYCR += 0;//外币贷方金额 } CwVouitemsEntity4.CORPID = "";//客户(系统) CwVouitemsEntity4.CUSTOMERNAME = "";//客户(系统) strBPVW = dsCwDesign.Tables[0].Rows[0]["BRTW"].ToString().Trim(); strEXPLAN = "收"; strEXPLAN += "/" + dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["CUSTOMERNAME"].ToString().Trim(); #endregion } } else if (dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["BILLTYPE"].ToString().Trim() == "2") { #region 实付支资料 CwVouitemsEntity4.ACCID = dsCwDesign.Tables[0].Rows[0]["FC"].ToString().Trim(); CwVouitemsEntity4.ACCNAME = dsFC.Tables[0].Rows[0]["ACCNAME"].ToString().Trim();//科目名称 // if (dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["CURR"].ToString().Trim() == "RMB") { CwVouitemsEntity4.AMTDR = Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ADVANCE_MONEY"].ToString().Trim()),2);//本位币贷方金额(互斥) CwVouitemsEntity4.AMTCR = 0;//本位币借方金额(互斥) CwVouitemsEntity4.FCYDR = 0;//外币借方金额 CwVouitemsEntity4.FCYCR = 0;//外币贷方金额 CwVouitemsEntity4.ISFCY = false;//是否外币 CwVouitemsEntity4.FCYNO = "RMB";//外币编号usd CwVouitemsEntity4.ISDEPTACC = false;//核算部门,按规则设置生成 CwVouitemsEntity4.ISEMPLACC = false;//核算人员,按规则设置生成 CwVouitemsEntity4.ISCORPACC = false;//核算客户,按规则设置生成 CwVouitemsEntity4.ISITEMACC = false;//核算项目,按规则设置生成 if (dsFC.Tables[0].Rows[0]["DC"].ToString().Trim() == "贷") { CwVouitemsEntity4.DC = "D";//借方向 } else { CwVouitemsEntity4.DC = "C";//借方向 } // strJie =strJie+Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ADVANCE_MONEY"].ToString().Trim()),2); dlAMTCR += 0;//本位币贷方金额(互斥) dlAMTDR =dlAMTDR+Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ADVANCE_MONEY"].ToString().Trim()),2);//本位币借方金额(互斥) dlFCYDR += 0;//外币借方金额 dlFCYCR += 0;//外币贷方金额 CwVouitemsEntity4.FCYEXRATE = 1;//汇率 } else { CwVouitemsEntity4.AMTDR = Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ADVANCE_MONEY"].ToString().Trim()) * CwVouitemsEntity4.FCYEXRATE, 2);//本位币贷方金额(互斥) CwVouitemsEntity4.AMTCR = 0;//本位币借方金额(互斥) CwVouitemsEntity4.FCYDR = Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ADVANCE_MONEY"].ToString().Trim()),2);//外币借方金额 CwVouitemsEntity4.FCYCR = 0;//外币贷方金额 CwVouitemsEntity4.ISFCY = true;//是否外币 CwVouitemsEntity4.FCYNO = "USD";//外币编号usd CwVouitemsEntity4.ISDEPTACC = false;//核算部门,按规则设置生成 CwVouitemsEntity4.ISEMPLACC = false;//核算人员,按规则设置生成 CwVouitemsEntity4.ISCORPACC = false;//核算客户,按规则设置生成 CwVouitemsEntity4.ISITEMACC = false;//核算项目,按规则设置生成 if (dsFC.Tables[0].Rows[0]["DC"].ToString().Trim() == "贷") { CwVouitemsEntity4.DC = "D";//借方向 } else { CwVouitemsEntity4.DC = "C";//借方向 } // strJie =strJie+Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ADVANCE_MONEY"].ToString().Trim()) * CwVouitemsEntity4.FCYEXRATE, 2); dlAMTCR += 0;//本位币贷方金额(互斥) dlAMTDR =dlAMTDR+Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ADVANCE_MONEY"].ToString().Trim()) * CwVouitemsEntity4.FCYEXRATE, 2);//本位币借方金额(互斥) dlFCYCR += 0;//外币借方金额 dlFCYDR =dlFCYDR+Math.Round(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["ADVANCE_MONEY"].ToString().Trim()),2);//外币贷方金额 } CwVouitemsEntity4.CORPID = "";//客户(系统) CwVouitemsEntity4.CUSTOMERNAME = "";//客户(系统) strBPVW = dsCwDesign.Tables[0].Rows[0]["BPVW"].ToString().Trim(); strEXPLAN = "付"; strEXPLAN += "/" + dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["CUSTOMERNAME"].ToString().Trim(); #endregion } n = n + 1; CwVouitemsEntity4.QTYUNIT = "";//数量包装单位 CwVouitemsEntity4.QTYDR = 0;//借方数量 CwVouitemsEntity4.QTYCR = 0;//贷方数量 // CwVouitemsEntity4.EXPLAN = strEXPLAN;//摘要,规则生成ALL CwVouitemsEntity4.DEPTID = "";//strDEPTID;//部门(系统) CwVouitemsEntity4.EMPLID = "";//strEMPLID;//人员(系统) CwVouitemsEntity4.SALE = "";//strSALE;//人员名称 CwVouitemsEntity4.PITEMID = 0;//父级项目编号 CwVouitemsEntity4.ITEMID = "0";//项目编号 CwVouitemsEntity4.FIELD1 = "";//预留字段1 CwVouitemsEntity4.FIELD2 = "";//预留字段2 CwVouitemsEntity4.FIELD3 = "";//预留字段3 CwVouitemsEntity4.FIELD4 = "";//预留字段4 CwVouitemsEntity4.FIELD5 = "";//预留字段5 CwVouitemsEntity4.FIELD6 = "";//预留字段6 CwVouitemsEntity4.FIELD7 = "";//预留字段7 CwVouitemsEntity4.FIELD8 = "";//预留字段8 CwVouitemsEntity4.FIELD9 = "";//预留字段9 CwVouitemsEntity4.FIELD10 = "";//预留字段10 CwVouitemsEntity4.ISSYS = true;//是否手动录入 CwVouitemsEntity4.FSETTLCODE = "";//结算方式 CwVouitemsEntity4.FSETTLENO = "";//财务辅助编号 CwVouitemsEntity4.MODIFIEDUSER = userid;//最后一次更新操作人 CwVouitemsEntity4.BLNO = "";//BILLNOstrBILLNOALL CwVouitemsEntities.Add(CwVouitemsEntity4); } } #endregion //#region 插入明细表 汇兑损益 // dlAMTDR = 0;//本位币贷方金额(互斥) //dlAMTCR = 0;//本位币借方金额(互斥) //dlFCYDR = 0;//外币借方金额 // dlFCYCR = 0;//外币贷方金额 //// //for (int i = 0; i < dsVWChFeeSettlementDo_Z.Tables[0].Rows.Count; i++) //{ // Decimal dl = Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["stlttl"].ToString().Trim())-(Decimal.Parse(dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["hj"].ToString().Trim())); // if (dl != 0) // { // CwVouitemsEntity CwVouitemsEntity4 = new CwVouitemsEntity(); // //CwVouitemsEntity4.GID 唯一编码 newid() // CwVouitemsEntity4.ORDNO = strORDNO.Trim(); // CwVouitemsEntity4.ITEMNO = n;// 行号 // CwVouitemsEntity4.PACCID = "";//父级科目贷码 // CwVouitemsEntity4.PROPERTY = 0;//科目属性 // CwVouitemsEntity4.GRADE = 0;//科目级别 // if (dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["BILLTYPE"].ToString().Trim() == "1") // { // if (dsEGOL != null) // { // #region 实收支资料 // CwVouitemsEntity4.ACCID = dsCwDesign.Tables[0].Rows[0]["FC"].ToString().Trim(); // CwVouitemsEntity4.ACCNAME = dsEGOL.Tables[0].Rows[0]["ACCNAME"].ToString().Trim();//科目名称 // // // CwVouitemsEntity4.AMTDR = dl;//本位币贷方金额(互斥) // CwVouitemsEntity4.AMTCR = 0;//本位币借方金额(互斥) // CwVouitemsEntity4.FCYDR = 0;//外币借方金额 // CwVouitemsEntity4.FCYCR = 0;//外币贷方金额 // CwVouitemsEntity4.ISFCY = false;//是否外币 // CwVouitemsEntity4.FCYNO = "RMB";//外币编号usd // CwVouitemsEntity4.ISDEPTACC = false;//核算部门,按规则设置生成 // CwVouitemsEntity4.ISEMPLACC = false;//核算人员,按规则设置生成 // CwVouitemsEntity4.ISCORPACC = false;//核算客户,按规则设置生成 // CwVouitemsEntity4.ISITEMACC = false;//核算项目,按规则设置生成 // if (dsEGOL.Tables[0].Rows[0]["DC"].ToString().Trim() == "贷") // { // CwVouitemsEntity4.DC = "D";//借方向 // } // else // { // CwVouitemsEntity4.DC = "C";//借方向 // } // // // strJie += dl; // dlAMTDR += dl;//本位币贷方金额(互斥) // dlAMTCR += 0;//本位币借方金额(互斥) // dlFCYDR += 0;//外币借方金额 // dlFCYCR += 0;//外币贷方金额 // CwVouitemsEntity4.CORPID = "";//客户(系统) // CwVouitemsEntity4.CUSTOMERNAME = "";//客户(系统) // strBPVW = dsCwDesign.Tables[0].Rows[0]["BRTW"].ToString().Trim(); // strEXPLAN = "收"; // strEXPLAN += "/" + dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["CUSTOMERNAME"].ToString().Trim(); // #endregion // } // } // else if (dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["BILLTYPE"].ToString().Trim() == "2") // { // #region 实付支资料 // CwVouitemsEntity4.ACCID = dsCwDesign.Tables[0].Rows[0]["FC"].ToString().Trim(); // CwVouitemsEntity4.ACCNAME = dsEGOL.Tables[0].Rows[0]["ACCNAME"].ToString().Trim();//科目名称 // // // CwVouitemsEntity4.AMTDR = -dl;//本位币贷方金额(互斥) // CwVouitemsEntity4.AMTCR = 0;//本位币借方金额(互斥) // CwVouitemsEntity4.FCYDR = 0;//外币借方金额 // CwVouitemsEntity4.FCYCR = 0;//外币贷方金额 // CwVouitemsEntity4.ISFCY = false;//是否外币 // CwVouitemsEntity4.FCYNO = "RMB";//外币编号usd // CwVouitemsEntity4.ISDEPTACC = false;//核算部门,按规则设置生成 // CwVouitemsEntity4.ISEMPLACC = false;//核算人员,按规则设置生成 // CwVouitemsEntity4.ISCORPACC = false;//核算客户,按规则设置生成 // CwVouitemsEntity4.ISITEMACC = false;//核算项目,按规则设置生成 // if (dsEGOL.Tables[0].Rows[0]["DC"].ToString().Trim() == "贷") // { // CwVouitemsEntity4.DC = "D";//借方向 // } // else // { // CwVouitemsEntity4.DC = "C";//借方向 // } // // // strDai += dl; // dlAMTDR += -dl;//本位币贷方金额(互斥) // dlAMTCR += 0;//本位币借方金额(互斥) // dlFCYDR += 0;//外币借方金额 // dlFCYCR += 0;//外币贷方金额 // CwVouitemsEntity4.CORPID = "";//客户(系统) // CwVouitemsEntity4.CUSTOMERNAME = "";//客户(系统) // strBPVW = dsCwDesign.Tables[0].Rows[0]["BPVW"].ToString().Trim(); // strEXPLAN = "付"; // strEXPLAN += "/" + dsVWChFeeSettlementDo_Z.Tables[0].Rows[i]["CUSTOMERNAME"].ToString().Trim(); // #endregion // } // n = n + 1; // CwVouitemsEntity4.FCYEXRATE = 0;//汇率 // CwVouitemsEntity4.QTYUNIT = "";//数量包装单位 // CwVouitemsEntity4.QTYDR = 0;//借方数量 // CwVouitemsEntity4.QTYCR = 0;//贷方数量 // // // CwVouitemsEntity4.EXPLAN = strEXPLAN;//摘要,规则生成ALL // CwVouitemsEntity4.DEPTID = "";//strDEPTID;//部门(系统) // CwVouitemsEntity4.EMPLID = "";//strEMPLID;//人员(系统) // CwVouitemsEntity4.SALE = "";//strSALE;//人员名称 // CwVouitemsEntity4.PITEMID = 0;//父级项目编号 // CwVouitemsEntity4.ITEMID = "0";//项目编号 // CwVouitemsEntity4.FIELD1 = "";//预留字段1 // CwVouitemsEntity4.FIELD2 = "";//预留字段2 // CwVouitemsEntity4.FIELD3 = "";//预留字段3 // CwVouitemsEntity4.FIELD4 = "";//预留字段4 // CwVouitemsEntity4.FIELD5 = "";//预留字段5 // CwVouitemsEntity4.FIELD6 = "";//预留字段6 // CwVouitemsEntity4.FIELD7 = "";//预留字段7 // CwVouitemsEntity4.FIELD8 = "";//预留字段8 // CwVouitemsEntity4.FIELD9 = "";//预留字段9 // CwVouitemsEntity4.FIELD10 = "";//预留字段10 // CwVouitemsEntity4.ISSYS = true;//是否手动录入 // CwVouitemsEntity4.FSETTLCODE = "";//结算方式 // CwVouitemsEntity4.FSETTLENO = "";//财务辅助编号 // CwVouitemsEntity4.MODIFIEDUSER = userid;//最后一次更新操作人 // CwVouitemsEntity4.BLNO = "";//BILLNOstrBILLNOALL // CwVouitemsEntities.Add(CwVouitemsEntity4); // } //} //#endregion #region 插入明细表 汇兑损益 dlAMTDR = 0;//本位币贷方金额(互斥) dlAMTCR = 0;//本位币借方金额(互斥) dlFCYDR = 0;//外币借方金额 dlFCYCR = 0;//外币贷方金额 // decimal TTLDR = 0; decimal TTLCR = 0; foreach (var enumValue in CwVouitemsEntities) { TTLDR = TTLDR + enumValue.AMTDR; TTLCR = TTLCR + enumValue.AMTCR; strEXPLAN = enumValue.EXPLAN; } if (TTLDR - TTLCR != 0) { CwVouitemsEntity CwVouitemsEntity4 = new CwVouitemsEntity(); //CwVouitemsEntity4.GID 唯一编码 newid() CwVouitemsEntity4.ORDNO = strORDNO.Trim(); CwVouitemsEntity4.ITEMNO = n;// 行号 CwVouitemsEntity4.PACCID = "";//父级科目贷码 CwVouitemsEntity4.PROPERTY = 0;//科目属性 CwVouitemsEntity4.GRADE = 0;//科目级别 if (dsEGOL != null) { #region 实收支资料 CwVouitemsEntity4.ACCID = dsCwDesign.Tables[0].Rows[0]["FC"].ToString().Trim(); CwVouitemsEntity4.ACCNAME = dsEGOL.Tables[0].Rows[0]["ACCNAME"].ToString().Trim();//科目名称 // CwVouitemsEntity4.AMTDR = (TTLCR - TTLDR);//本位币贷方金额(互斥) CwVouitemsEntity4.AMTCR = 0;//本位币借方金额(互斥) CwVouitemsEntity4.FCYDR = 0;//外币借方金额 CwVouitemsEntity4.FCYCR = 0;//外币贷方金额 CwVouitemsEntity4.ISFCY = false;//是否外币 CwVouitemsEntity4.FCYNO = "RMB";//外币编号usd CwVouitemsEntity4.ISDEPTACC = false;//核算部门,按规则设置生成 CwVouitemsEntity4.ISEMPLACC = false;//核算人员,按规则设置生成 CwVouitemsEntity4.ISCORPACC = false;//核算客户,按规则设置生成 CwVouitemsEntity4.ISITEMACC = false;//核算项目,按规则设置生成 if (dsEGOL.Tables[0].Rows[0]["DC"].ToString().Trim() == "贷") { CwVouitemsEntity4.DC = "D";//借方向 } else { CwVouitemsEntity4.DC = "C";//借方向 } // strJie += (TTLCR - TTLDR); dlAMTDR += (TTLCR - TTLDR);//本位币贷方金额(互斥) dlAMTCR += 0;//本位币借方金额(互斥) dlFCYDR += 0;//外币借方金额 dlFCYCR += 0;//外币贷方金额 CwVouitemsEntity4.CORPID = "";//客户(系统) CwVouitemsEntity4.CUSTOMERNAME = "";//客户(系统) strBPVW = dsCwDesign.Tables[0].Rows[0]["BRTW"].ToString().Trim(); #endregion } n = n + 1; CwVouitemsEntity4.FCYEXRATE =1;//汇率 CwVouitemsEntity4.QTYUNIT = "";//数量包装单位 CwVouitemsEntity4.QTYDR = 0;//借方数量 CwVouitemsEntity4.QTYCR = 0;//贷方数量 // CwVouitemsEntity4.EXPLAN = strEXPLAN;//摘要,规则生成ALL CwVouitemsEntity4.DEPTID = "";//strDEPTID;//部门(系统) CwVouitemsEntity4.EMPLID = "";//strEMPLID;//人员(系统) CwVouitemsEntity4.SALE = "";//strSALE;//人员名称 CwVouitemsEntity4.PITEMID = 0;//父级项目编号 CwVouitemsEntity4.ITEMID = "0";//项目编号 CwVouitemsEntity4.FIELD1 = "";//预留字段1 CwVouitemsEntity4.FIELD2 = "";//预留字段2 CwVouitemsEntity4.FIELD3 = "";//预留字段3 CwVouitemsEntity4.FIELD4 = "";//预留字段4 CwVouitemsEntity4.FIELD5 = "";//预留字段5 CwVouitemsEntity4.FIELD6 = "";//预留字段6 CwVouitemsEntity4.FIELD7 = "";//预留字段7 CwVouitemsEntity4.FIELD8 = "";//预留字段8 CwVouitemsEntity4.FIELD9 = "";//预留字段9 CwVouitemsEntity4.FIELD10 = "";//预留字段10 CwVouitemsEntity4.ISSYS = true;//是否手动录入 CwVouitemsEntity4.FSETTLCODE = "";//结算方式 CwVouitemsEntity4.FSETTLENO = "";//财务辅助编号 CwVouitemsEntity4.MODIFIEDUSER = userid;//最后一次更新操作人 CwVouitemsEntity4.BLNO = "";//BILLNOstrBILLNOALL CwVouitemsEntities.Add(CwVouitemsEntity4); } #endregion #region 插入主表 string rq = sVOUDATE.Trim(); CwVouchersEntity CwVouchersEntity = new CwVouchersEntity(); CwVouchersEntity.ORDNO = strORDNO.Trim();//唯一编码 newid() varchar (36) CwVouchersEntity.VOUDATE = DateTime.Parse(rq.Trim());//凭证日期="";//datetime="";//  CwVouchersEntity.ACCYEAR = rq.Trim().Substring(0, 4);//年="";//int="";//  int iM1 = rq.Trim().IndexOf("-") + 1; int iM2 = rq.Trim().LastIndexOf("-"); string strACCMONTH = ""; if (iM2 - iM1 == 1) { strACCMONTH = "0" + rq.Trim().Substring(iM1, iM2 - iM1); } else { strACCMONTH = rq.Trim().Substring(iM1, iM2 - iM1); } string strVOUNO = ""; strVOUNO = getVOUNO(dsCwDesign, rq.Trim().Substring(0, 4), strACCMONTH); CwVouchersEntity.VOUNO = strVOUNO;//月初清零 varchar (16) CwVouchersEntity.ACCMONTH = strACCMONTH;//月="";//int="";//  string strVOUALLNO = rq.Trim().Substring(0, 4) + strACCMONTH + strVOUNO; CwVouchersEntity.VOUALLNO = strBPVW.Trim()+strVOUALLNO.Trim();//月初清零 varchar (16) CwVouchersEntity.VKNO = strBPVW.Trim();//凭证字="";//varchar="";//(8) CwVouchersEntity.ATTACHS = int.Parse(dsCwDesign.Tables[0].Rows[0]["AFFIXNO"].ToString().Trim());//附件(单据)数="";//int="";//  CwVouchersEntity.AMTDR = strJie;// iAMTDR;//本位币借方="";//numeric(18, 2)="";//  CwVouchersEntity.AMTCR = strDai;//iAMTDR;// iAMTCR;//本位币贷方="";//numeric(18, 2)="";//  CwVouchersEntity.FCY = "";//是否含有外币科目="";//varchar="";//(1) CwVouchersEntity.QTY = "";//是否含有数量科目="";//varchar="";//(1) CwVouchersEntity.VOUPROP = "";//凭证属性="";//varchar="";//(1) // CwVouchersEntity.PREPARED = T_ALL_DA.GetStrSQL("FINANCESOFTCODE", "select top 1 FINANCESOFTCODE from user_baseinfo where USERID in (select top 1 gid from [user] where SHOWNAME='" + this.hd_comboPREPARED.Value.Trim() + "' and ISDELETED=0)");//制单人="";//varchar="";//(10) CwVouchersEntity.PREPARED = sPREPARED;//制单人="";//varchar="";//(10) CwVouchersEntity.CHECKED = "";//审核人="";//varchar="";//(10) CwVouchersEntity.ENTERED = "";//记账人="";//varchar="";//(10) CwVouchersEntity.ERRMSG = "";//错误信息="";//varchar="";//(6) CwVouchersEntity.ISDELETE = false;//是否废除="";//bit="";//Y CwVouchersEntity.DELETEUSER = "";//废除操作人="";//Varchar(36)="";//Y //CwVouchersEntity.DELETETIME="";//废除时间="";//Datetime="";//Y #endregion //生成入库 string strSel = " and BILLNO=" + sGids + " " ; if (CwVouitemsEntities.Count == 0) { iResult = 2; } else iResult = CwVouchersDA.setInsertAll(CwVouchersEntity, CwVouitemsEntities, "ch_fee_hexiao", strBPVW.Trim() + strVOUALLNO, strSel, userid, 2); } else { return -1; } return iResult; } protected static String getVOUNO(DataSet dsCwDesign, string strY, string strM) { T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA(); string sVOUNO = ""; string strVOUNO = "select top 1 VOUNO from cw_vouchers where ACCYEAR='" + strY + "' and ACCMONTH='" + strM + "' and (ISDELETE=0 or ISDELETE is null) order by convert(decimal(10),VOUNO) desc"; strVOUNO = T_ALL_DA.GetStrSQL("VOUNO", strVOUNO); if (strVOUNO.Trim() == "") { strVOUNO = "0001"; DataSet ds = T_ALL_DA.GetAllSQL("select * from code_rule where RULENAME='凭证号' and RULETYPE=5"); if (ds != null) { if (ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows[0]["RULENOSTART"].ToString().Trim() != "") { strVOUNO = ds.Tables[0].Rows[0]["RULENOSTART"].ToString().Trim(); } } } } else { if (dsCwDesign.Tables[0].Rows[0]["ISFILLSERIALNO"].ToString().Trim() == "True") { string strFILL = "select top 1 serialno from code_fill_serialno where ISDELETE=0 and tablename='cw_vouchers' and serialno like '%" + strY + strM + "%' and serialno not in (select VOUALLNO from cw_vouchers where (ISDELETE=0 or ISDELETE is null)) order by serialno"; strFILL = T_ALL_DA.GetStrSQL("serialno", strFILL); if (strFILL.Trim() != "") { strVOUNO = strFILL.Trim().Substring(strFILL.Length - 4); //strVOUNO = strFILL.Trim().Substring(6); return strVOUNO; } } sVOUNO = (int.Parse(strVOUNO) + 1).ToString().Trim(); strVOUNO = sVOUNO; for (int i = 0; i < 4 - sVOUNO.Length; i++) { strVOUNO = "0" + strVOUNO; } } return strVOUNO; } public static DBResult GetIsVoucherJK(string strGids) { var result = new DBResult(); T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA(); string sSQL = "SELECT count(*) as inum from (select *,(select STLNAME from [code_stlmode] where STLCODE=ch_fee_settlement.SETTLETYPE) as SETTLETYPEREF,(case when CURR='RMB' then (select BANKNAME from [sys_bank] where GID=ch_fee_settlement.ACCOUNTRMB) else (select BANKNAME from [sys_bank] where GID=ch_fee_settlement.ACCOUNTUSD) end) as ACCOUNTREF from ch_fee_settlement) as a where HXBILLNO IN ('" + strGids.Trim().Replace(",", "','") + "') and (SETTLETYPEREF <> '现金' and ISNULL(ACCOUNTREF,'') = '')"; int inum = int.Parse(T_ALL_DA.GetStrSQL("inum", sSQL)); if (inum == 0) { result.Success = true; result.Message = ""; return result; } else { #region 2018年2月13日11:53:03 鼎世 李进举 批量提生成凭证增加异常单号的提醒 string hxnoStr = ""; try { string sqlHxNoList = @"SELECT hxnoStr = ( STUFF(( SELECT ',' + b.HXBILLNO FROM ch_fee_settlement b WHERE b.HXBILLNO = a.HXBILLNO FOR XML PATH('') ), 1, 1, '') ) from (select *,(select STLNAME from [code_stlmode] where STLCODE=ch_fee_settlement.SETTLETYPE) as SETTLETYPEREF,(case when CURR='RMB' then (select BANKNAME from [sys_bank] where GID=ch_fee_settlement.ACCOUNTRMB) else (select BANKNAME from [sys_bank] where GID=ch_fee_settlement.ACCOUNTUSD) end) as ACCOUNTREF from ch_fee_settlement) as a where HXBILLNO IN ('" + strGids.Trim().Replace(",", "','") + "') and (SETTLETYPEREF <> '现金' and ISNULL(ACCOUNTREF,'') = '')"; hxnoStr = T_ALL_DA.GetStrSQL("hxnoStr", sqlHxNoList).ToString(); if (hxnoStr!="") { hxnoStr = "异常数据:[" + hxnoStr + "]"; } } catch (Exception) { hxnoStr = ""; } #endregion result.Success = false; result.Message = "请选择已锁定,且未生成凭证的单据!"+hxnoStr; return result; } } static public List GetCodeCurrencyListJK(string sVOUDATE, string companyid) { var strSql = new StringBuilder(); strSql.Append("select top 1 CURRENCY=(select top 1 CODENAME from code_currency where GID=currency_exchange.CURRENCYID),[VALUE] as FCYEXRATE from currency_exchange where COMPANYID='" + companyid + "' and ISDELETE=0"); if (!string.IsNullOrEmpty(sVOUDATE)) { strSql.Append(" and STARTTIME<='" + sVOUDATE + "' and ENDTIME>='" + sVOUDATE + "'"); } else { strSql.Append(" and STARTTIME<=GETDATE() and ENDTIME>=GETDATE()"); } strSql.Append(" order by ENDTIME desc"); List ListMsCwVouchersGl4 = new List(); ListMsCwVouchersGl4 = SetCodeCurrencyData(strSql); if (ListMsCwVouchersGl4.Count < 1) { strSql.Clear(); strSql.Append("select top 1 CODENAME as CURRENCY,[DEFAULTRATE] as FCYEXRATE from code_currency where CODENAME='USD'"); } return SetCodeCurrencyData(strSql); } private static List SetCodeCurrencyData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { MsCwVouchersGl4 data = new MsCwVouchersGl4(); #region Set DB data to Object data.CURR = (reader["CURRENCY"] == null ? "" : Convert.ToString(reader["CURRENCY"])); data.FCYEXRATE = (reader["FCYEXRATE"] == null ? "" : Convert.ToString(reader["FCYEXRATE"])); #endregion headList.Add(data); } reader.Close(); } return headList; } /// /// 是否自动弹出结算接口凭证 /// /// public static DBResult onIsSettlementsopenJK() { var result = new DBResult(); T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA(); string sSQL = "SELECT top 1 ISSETTLEMENTSOPEN from cw_design"; Boolean blISSETTLEMENTSOPEN = Boolean.Parse(T_ALL_DA.GetStrSQL("ISSETTLEMENTSOPEN", sSQL)); if (blISSETTLEMENTSOPEN) { result.Success = true; result.Message = ""; return result; } else { result.Success = false; result.Message = ""; return result; } } #endregion } }