using System; using System.Data; using System.Collections.Generic; using System.Text; 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.CommMng.Models; using DSWeb.EntityDA; using HcUtility.Comm; using DSWeb.Areas.CommMng.DAL; using DSWeb.Areas.Account.Models.ChfeeDetail; namespace DSWeb.Areas.Account.DAL.Chfee_Recvprocess { public partial class ChrecvprocessDAL { #region 结算列表 static public List GetDataList(string strCondition, string userid = "", string usercode = "", string orgcode = "", string sort = null, int self=0) { var rangstr = ""; if (self == 1) rangstr = " SETTLEUSER='"+userid+"'"; else rangstr = GetRangDAListStr("modRecvprocess", 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,BILLNO,BILLDATE,SETTLEMODE,CUSTOMERNAME,SETTLETYPE ,AMOUNTRMB,ACCOUNTRMB,RATE "); strSql.Append(" ,AMOUNTUSD,ACCOUNTUSD,BILLTYPE,VOUCHERNO,SETTLEUSER,SETTLETIME,AUDITUSER,AUDITTIME,CREATEUSER,CREATETIME"); strSql.Append(" ,ISEXPORT,FINANCIALVOUCHER,BILLSTATUS,REMARK,COMPANYID,CUSTBANK,CUSTACCOUNT,VOUNO,ACCOUNTS_CURRENCY "); strSql.Append(" ,ACCOUNTS_RATE,ACCOUNTS_MONEY,PREPAY_CURRENCY,PREPAY_RATE,PREPAY_MONEY,AHSR_CURRENCY "); strSql.Append(" ,AHSR_RATE,AHSR_MONEY,FINANCIAL_CURRENCY,FINANCIAL_RATE,FINANCIAL_MONEY,ADVANCE_CURRENCY,ADVANCE_RATE,ADVANCE_MONEY,CURR "); 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.SETTLEUSER) as SETTLEUSERREF"); strSql.Append(",(select ShowName from [user] where GID=cm.CREATEUSER) as CREATEUSERREF"); strSql.Append(",(select STLNAME from [code_stlmode] where STLCODE=cm.SETTLETYPE) as SETTLETYPEREF"); strSql.Append(",(select BANKNAME from [sys_bank] where GID=cm.ACCOUNTRMB) as ACCOUNTRMBREF"); strSql.Append(",(select BANKNAME from [sys_bank] where GID=cm.ACCOUNTUSD) as ACCOUNTUSDREF"); strSql.Append(",(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=97007 and EnumValueID=cm.SETTLEMODE) as SETTLEMODEREF"); strSql.Append(",ISNULL((select sum(isnull(amount,0)-isnull(invoice,0)) as noinvoice from ch_fee where ISINVOICE<>1 and gid in (select feeid from ch_fee_do where BILLNO=cm.BILLNO)),0) as NOINVOICE"); strSql.Append(" FROM ch_fee_settlement cm where SETTLEMODE=5 "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" and " + strCondition); } var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring + ",SETTLETIME DESC"); } else { strSql.Append(" order by SETTLETIME DESC"); } return SetData(strSql); } static public string GetDataListStr(string strCondition, string userid = "", string usercode = "", string orgcode = "", string sort = null, int self=0) { var rangstr = ""; if (self == 1) rangstr = " SETTLEUSER='" + userid + "'"; else rangstr = GetRangDAListStr("modRecvprocess", 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,BILLNO,BILLDATE,SETTLEMODE,CUSTOMERNAME,SETTLETYPE ,AMOUNTRMB,ACCOUNTRMB,RATE "); strSql.Append(" ,AMOUNTUSD,ACCOUNTUSD,BILLTYPE,VOUCHERNO,SETTLEUSER,SETTLETIME,AUDITUSER,AUDITTIME,CREATEUSER,CREATETIME"); strSql.Append(" ,ISEXPORT,FINANCIALVOUCHER,BILLSTATUS,REMARK,COMPANYID,CUSTBANK,CUSTACCOUNT,VOUNO,ACCOUNTS_CURRENCY "); strSql.Append(" ,ACCOUNTS_RATE,ACCOUNTS_MONEY,PREPAY_CURRENCY,PREPAY_RATE,PREPAY_MONEY,AHSR_CURRENCY "); strSql.Append(" ,AHSR_RATE,AHSR_MONEY,FINANCIAL_CURRENCY,FINANCIAL_RATE,FINANCIAL_MONEY,ADVANCE_CURRENCY,ADVANCE_RATE,ADVANCE_MONEY,CURR "); 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.SETTLEUSER) as SETTLEUSERREF"); strSql.Append(",(select ShowName from [user] where GID=cm.CREATEUSER) as CREATEUSERREF"); strSql.Append(",(select STLNAME from [code_stlmode] where STLCODE=cm.SETTLETYPE) as SETTLETYPEREF"); strSql.Append(",(select BANKNAME from [sys_bank] where GID=cm.ACCOUNTRMB) as ACCOUNTRMBREF"); strSql.Append(",(select BANKNAME from [sys_bank] where GID=cm.ACCOUNTUSD) as ACCOUNTUSDREF"); strSql.Append(",(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=97007 and EnumValueID=cm.SETTLEMODE) as SETTLEMODEREF"); strSql.Append(",ISNULL((select sum(amount-invoice) as noinvoice from ch_fee where gid in (select feeid from ch_fee_do where BILLNO=cm.BILLNO)),0) as NOINVOICE"); strSql.Append(" FROM ch_fee_settlement cm where SETTLEMODE=5 "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" and " + strCondition); } var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by SETTLETIME DESC"); } return strSql.ToString(); } static public ChSettlement GetData(string condition, string userid = "", string usercode = "", string orgcode = "") { var list = GetDataList(condition, userid, usercode, orgcode); if (list.Count > 0) return list[0]; return new ChSettlement(); } private static List SetData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { ChSettlement data = new ChSettlement(); #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"]); data.SETTLEMODE = Convert.ToInt32(reader["SETTLEMODE"]); data.SETTLEMODEREF = Convert.ToString(reader["SETTLEMODEREF"]); data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]); if (reader["SETTLETYPE"] != DBNull.Value) data.SETTLETYPE = Convert.ToInt32(reader["SETTLETYPE"]); data.SETTLETYPEREF = Convert.ToString(reader["SETTLETYPEREF"]); if (reader["AMOUNTRMB"] != DBNull.Value) data.AMOUNTRMB = Convert.ToDecimal(reader["AMOUNTRMB"]); data.ACCOUNTRMB = Convert.ToString(reader["ACCOUNTRMB"]); if (reader["RATE"] != DBNull.Value) data.RATE = Convert.ToDecimal(reader["RATE"]); if (reader["AMOUNTUSD"] != DBNull.Value) data.AMOUNTUSD = Convert.ToDecimal(reader["AMOUNTUSD"]); data.ACCOUNTUSD = Convert.ToString(reader["ACCOUNTUSD"]); data.CURR = Convert.ToString(reader["CURR"]); if (data.CURR == "RMB") { data.AMOUNT = data.AMOUNTRMB; data.ACCOUNT = data.ACCOUNTRMB; data.ACCOUNTREF = Convert.ToString(reader["ACCOUNTRMBREF"]); } else { data.AMOUNT = data.AMOUNTUSD; data.ACCOUNT = data.ACCOUNTUSD; data.ACCOUNTREF = Convert.ToString(reader["ACCOUNTUSDREF"]); } data.BILLTYPE = Convert.ToInt32(reader["BILLTYPE"]); data.VOUCHERNO = Convert.ToString(reader["VOUCHERNO"]); data.SETTLEUSER = Convert.ToString(reader["SETTLEUSER"]); data.SETTLEUSERREF = Convert.ToString(reader["SETTLEUSERREF"]); if (reader["SETTLETIME"] != DBNull.Value) data.SETTLETIME = Convert.ToDateTime(reader["SETTLETIME"]).ToString("yyyy-MM-dd"); 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"]); if (reader["ISEXPORT"] != DBNull.Value) data.ISEXPORT = Convert.ToBoolean(reader["ISEXPORT"]); data.FINANCIALVOUCHER = Convert.ToString(reader["FINANCIALVOUCHER"]); 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.CUSTBANK = Convert.ToString(reader["CUSTBANK"]); data.CUSTACCOUNT = Convert.ToString(reader["CUSTACCOUNT"]); data.ACCOUNTS_CURRENCY = Convert.ToString(reader["ACCOUNTS_CURRENCY"]); data.ACCOUNTS_RATE = Convert.ToDecimal(reader["ACCOUNTS_RATE"]); data.ACCOUNTS_MONEY = Convert.ToDecimal(reader["ACCOUNTS_MONEY"]); data.PREPAY_CURRENCY = Convert.ToString(reader["PREPAY_CURRENCY"]); data.PREPAY_RATE = Convert.ToDecimal(reader["PREPAY_RATE"]); data.PREPAY_MONEY = Convert.ToDecimal(reader["PREPAY_MONEY"]); data.AHSR_CURRENCY = Convert.ToString(reader["AHSR_CURRENCY"]); data.AHSR_RATE = Convert.ToDecimal(reader["AHSR_RATE"]); data.AHSR_MONEY = Convert.ToDecimal(reader["AHSR_MONEY"]); data.FINANCIAL_CURRENCY = Convert.ToString(reader["FINANCIAL_CURRENCY"]); data.FINANCIAL_RATE = Convert.ToDecimal(reader["FINANCIAL_RATE"]); data.FINANCIAL_MONEY = Convert.ToDecimal(reader["FINANCIAL_MONEY"]); data.ADVANCE_CURRENCY = Convert.ToString(reader["ADVANCE_CURRENCY"]); data.ADVANCE_RATE = Convert.ToDecimal(reader["ADVANCE_RATE"]); data.ADVANCE_MONEY = Convert.ToDecimal(reader["ADVANCE_MONEY"]); data.NOINVOICE = Convert.ToString(reader["NOINVOICE"]); #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.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"); 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"); strSql.Append(",b.DOC,b.SALE,b.ENTERP,(select INVOICENO from ch_fee_invoice where BILLNO IN (SELECT BILLNO FROM CH_FEE_DO WHERE FEEID=c.FEEID AND CATEGORY=7)) INVOICENO "); strSql.Append(" FROM ch_fee_do c"); strSql.Append(" left join v_op_bill b on (b.bsno=c.bsno)"); 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.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"]); 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"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 费用列表明细 static public List GetFeeDetailList(string strCondition) { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append("f.BSNO,f.GID AS CH_ID,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)-isnull(f.ORDERAMOUNT,0)+isnull(f.ORDERSETTLEMENT,0) as balamount,f.currency,f.exChangerate,f.INVOICE"); strSql.Append(" from ch_fee f "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); } 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.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"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion static public List GetCurrExrateData(string billcust, string billcurr, string feesql) { var strSql = new StringBuilder(); strSql.Append(" SELECT DISTINCT CURRENCY,EXCHANGERATE "); strSql.Append(" FROM ch_fee f where CURRENCY<>'" + billcurr + "' AND CUSTOMERNAME='" + billcust + "'"); 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 ORDERAMOUNT=ISNULL(ORDERAMOUNT,0)+@ORDERAMOUNT 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, 15); 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, "@ORDERAMOUNT", 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) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { 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 ORDERAMOUNT=ISNULL(ORDERAMOUNT,0)+@ORDERAMOUNT 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, 15); 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, "@ORDERAMOUNT", 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 ORDERAMOUNT=ORDERAMOUNT-@ORIGAMOUNT where GID=@GID"); foreach (var enumValue in boday) { cmddelete.Parameters.Clear(); db.AddInParameter(cmddelete, "@FEEDOID", DbType.String, enumValue.FEEDOID); db.ExecuteNonQuery(cmddelete, tran); cmdupdate.Parameters.Clear(); db.AddInParameter(cmdupdate, "@ORIGAMOUNT", 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) { tran.Rollback(); result.Success = false; result.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_recv"); db.AddInParameter(cmd, "@con_no", DbType.String, billno); db.AddInParameter(cmd, "@billtype", DbType.Int16, billtype); db.ExecuteNonQuery(cmd); return 0; } #region 提交审核和撤销审核 public static DBResult UpdateStatus ( string bills, string status ) { var result = new DBResult(); var billList = bills.Split(','); var upstatus = "7"; if (status == "submit") { upstatus = "8"; } else if (status == "cancelsubmit") { upstatus = "7"; } else if (status == "Confirm") { upstatus = "9"; } else if (status == "CancelConfirm") { upstatus = "8"; } else if (status == "SubmitAudit") { upstatus = "10"; } else if (status == "CancelSubmitAudit") { upstatus = "9"; } else if (status == "Audit") { upstatus = "1"; } else if (status == "CancelAudit") { upstatus = "11"; } Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { foreach (var bill in billList) { if (status == "Audit" || status == "CancelAudit") { var dataList =GetBodyList("BILLNO='" +bill + "'"); if (dataList != null) { var cmdupdatefeedo = db.GetSqlStringCommand("update ch_fee_do set CATEGORY=8 where GID=@FEEDOID"); var cmdupdatefee = db.GetSqlStringCommand("update ch_fee set ORDERAMOUNT=ORDERAMOUNT-@ORIGAMOUNT,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 (status == "CancelAudit") { cmdupdatefeedo = db.GetSqlStringCommand("update ch_fee_do set CATEGORY=15 where GID=@FEEDOID"); cmdupdatefee = db.GetSqlStringCommand("update ch_fee set ORDERAMOUNT=ORDERAMOUNT+@ORIGAMOUNT,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 WHEN AMOUNT<>0 AND (SETTLEMENT-@SETTLEMENT4)=0 THEN 0 ELSE FEESTATUS end) where GID=@GID"); } foreach (var enumValue in dataList) { cmdupdatefeedo.Parameters.Clear(); db.AddInParameter(cmdupdatefeedo, "@FEEDOID", DbType.String, enumValue.FEEDOID); db.ExecuteNonQuery(cmdupdatefeedo, tran); cmdupdatefee.Parameters.Clear(); db.AddInParameter(cmdupdatefee, "@ORIGAMOUNT", DbType.Decimal, enumValue.ORIGAMOUNT); db.AddInParameter(cmdupdatefee, "@SETTLEMENT", DbType.Decimal, enumValue.ORIGAMOUNT); db.AddInParameter(cmdupdatefee, "@SETTLEMENT2", DbType.Decimal, enumValue.ORIGAMOUNT); db.AddInParameter(cmdupdatefee, "@SETTLEMENT3", DbType.Decimal, enumValue.ORIGAMOUNT); db.AddInParameter(cmdupdatefee, "@SETTLEMENT4", DbType.Decimal, enumValue.ORIGAMOUNT); db.AddInParameter(cmdupdatefee, "@GID", DbType.String, enumValue.FEEID); db.ExecuteNonQuery(cmdupdatefee, tran); } } } var cmdupdate = db.GetSqlStringCommand("update ch_fee_settlement set BILLSTATUS=" + upstatus + " where BILLNO=@BILLNO"); cmdupdate.Parameters.Clear(); db.AddInParameter(cmdupdate, "@BILLNO", DbType.String, bill); 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; } } return result; } public static DBResult SaveInfo ( string BILLNO, string CUSTOMERNAME, string SETTLEUSER )//string CustomerName, string SETTLEUSER, { 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_settlement set SETTLEUSER=@SETTLEUSER ,CUSTOMERNAME=@CUSTOMERNAME where BILLNO=@BILLNO"); cmdupdate.Parameters.Clear(); db.AddInParameter(cmdupdate, "@BILLNO", DbType.String, BILLNO); db.AddInParameter(cmdupdate, "@CUSTOMERNAME", DbType.String, CUSTOMERNAME); db.AddInParameter(cmdupdate, "@SETTLEUSER", DbType.String, SETTLEUSER); 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; } } return result; } #endregion #region 驳回 public static DBResult AuditBackList(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 cmdupdate = db.GetSqlStringCommand("update ch_fee_recvapplication set BILLSTATUS=6 where BILLNO=@BILLNO"); foreach (var enumValue in boday) { cmdupdate.Parameters.Clear(); db.AddInParameter(cmdupdate, "@BILLNO", DbType.String, enumValue.BILLNO); 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; } #endregion #region 收费登记列表权限范围 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 "); strSql.Append(" from VW_User_Authority "); strSql.Append(" where [NAME]='modRecvProcessList' and USERID='" + userid + "' and ISDELETE=0"); string visiblerange = "4"; string operaterange = "4"; 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"]); break; } reader.Close(); } if (visiblerange == "4") { str = " ((CREATEUSER='" + userid + "') or (SETTLEUSER='" + userid + "'))"; } else if (visiblerange == "3") { str = " ((CREATEUSER='" + userid + "') or (SETTLEUSER='" + userid + "')) "; } else if (visiblerange == "2") { var rangeDa = new RangeDA(); var deptname = rangeDa.GetDEPTNAME(userid); str = " ((CREATEUSER in (select USERID from user_company where COMPANYID='" + companyid + "') and CREATEUER in (select userid from user_baseinfo where DEPTNAME='" + deptname + "')) "; str = str + " or (SETTLEUSER in (select USERID from user_company where COMPANYID='" + companyid + "') and SETTLEUSER in (select userid from user_baseinfo where DEPTNAME='" + deptname + "'))) "; } else if (visiblerange == "1") { str = " (CREATEUSER in (select USERID from user_company where COMPANYID='" + companyid + "') or SETTLEUSER in (select USERID from user_company where COMPANYID='" + companyid + "'))"; } else if (visiblerange == "0") { str = " 1=1 "; } return str; } #endregion } }