using System; using System.Data; using System.Data.Common; using System.Collections.Generic; using System.Text; using DSWeb.Areas.OA.Models.Baoxiao; using DSWeb.Areas.OA.Models.Comm; using Microsoft.Practices.EnterpriseLibrary.Data; using HcUtility.Comm; using DSWeb.Areas.CommMng.DAL; using DSWeb.EntityDA; using System.Data.SqlClient; using DSWeb.Areas.CommMng.Models; namespace DSWeb.Areas.OA.DAL.Baoxiao { public partial class BaoxiaoDAL { #region 报销单列表 //报销单列表 static public List GetDataList(string strCondition, string corpid, string userid, string username, string sort = null) { var rangstr = GetListRangDAStr("BXLIST", userid, username, corpid); if (!string.IsNullOrEmpty(rangstr)) { if (!string.IsNullOrEmpty(strCondition)) { strCondition = strCondition + " and " + rangstr; } else { strCondition = rangstr; } } var strSql = new StringBuilder(); strSql.Append(" select BXGID,bx.BSNO,COMPANY,DEPTNAME,CREATEUSER,BXNO,"); strSql.Append(" case CREATEDATE when '1900-1-1' then '' else CONVERT(varchar, CREATEDATE, 23 ) end CREATEDATE "); strSql.Append(" ,bx.ISDELETE,bx.DELETEUSER,bx.DELETETIME,BXSTATUS,REMARK,AMOUNT "); strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99024 and EnumValueID=bx.bxstatus) as bxstatus_Ref "); strSql.Append(" ,CHEQUENO,CHEQUEAMOUNT,BX.ISVOU,(case BX.ISVOU when 1 then '是' else '否' end) ISVOU_REF,BX.VOUCHERNO "); strSql.Append(" ,(case BX.ISVOU when 1 then '是' else '否' end) ISVOU_REF,BX.VOUCHERNO "); strSql.Append(" ,(case (select isnull(sum(obp.amount),0) from OA_Baoxiao_Pay obp where obp.BXGID=bx.BXGID) "); strSql.Append(" when isnull(bx.amount,0) then '是' else '否' end) ISPAYED "); strSql.Append(" FROM OA_Baoxiao BX "); strSql.Append(" where (bx.isdelete=0 or bx.isdelete is null) "); 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 CREATEDATE DESC"); } return SetData(strSql); } static public Baoxiaomb GetData(string condition,string companyid,string userid,string username) { var list = GetDataList(condition, companyid,userid,username); if (list.Count > 0) return list[0]; return new Baoxiaomb(); } 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()) { Baoxiaomb data = new Baoxiaomb(); #region Set DB data to Object data.BXGID = Convert.ToString(reader["BXGID"]); data.BSNO = Convert.ToString(reader["BSNO"]); data.COMPANY = Convert.ToString(reader["COMPANY"]); data.DEPTNAME = Convert.ToString(reader["DEPTNAME"]); data.CREATEUSER = Convert.ToString(reader["CREATEUSER"]); data.CREATEDATE = Convert.ToString(reader["CREATEDATE"]); data.ISDELETE = Convert.ToString(reader["ISDELETE"]); data.DELETEUSER = Convert.ToString(reader["DELETEUSER"]); data.DELETETIME = Convert.ToString(reader["DELETETIME"]); data.BXSTATUS = Convert.ToString(reader["BXSTATUS"]); data.BXSTATUS_REF = Convert.ToString(reader["BXSTATUS_REF"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.AMOUNT = Convert.ToString(reader["AMOUNT"]); data.CHEQUENO = Convert.ToString(reader["CHEQUENO"]); data.CHEQUEAMOUNT = Convert.ToString(reader["CHEQUEAMOUNT"]); data.ISVOU_REF = Convert.ToString(reader["ISVOU_REF"]); data.VOUCHERNO = Convert.ToString(reader["VOUCHERNO"]); data.BXNO = Convert.ToString(reader["BXNO"]); data.ISPAYED = Convert.ToString(reader["ISPAYED"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 审核用报销单列表 //报销单列表 static public List GetAuditDataList(string strCondition,string workflowname,string corpid,string userid,string username) { var rangstr = GetListRangDAStr("BXAUDITLIST", userid, username, corpid); if (!string.IsNullOrEmpty(rangstr)) { if (!string.IsNullOrEmpty(strCondition)) { strCondition = strCondition + " and " + rangstr; } else { strCondition = rangstr; } } var strSql = new StringBuilder(); strSql.Append(" declare @workflowname varchar(50),@corpid varchar(50),@userid varchar(50)"); strSql.Append(" set @workflowname ='" + workflowname + "'"); strSql.Append(" set @corpid ='" + corpid + "'"); strSql.Append(" set @userid ='" + userid + "'"); strSql.Append(" select BXGID,bx.BSNO,COMPANY,DEPTNAME,CREATEUSER,BXNO,"); strSql.Append(" case CREATEDATE when '1900-1-1' then '' else CONVERT(varchar, CREATEDATE, 120 ) end CREATEDATE "); strSql.Append(" ,bx.ISDELETE,bx.DELETEUSER,bx.DELETETIME,BXSTATUS,REMARK,AMOUNT "); strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99024 and EnumValueID=bx.bxstatus) as bxstatus_Ref "); strSql.Append(" ,CHEQUENO,CHEQUEAMOUNT,BX.ISVOU,(case BX.ISVOU when 1 then '是' else '否' end) ISVOU_REF,BX.VOUCHERNO "); strSql.Append(" ,(case BX.ISVOU when 1 then '是' else '否' end) ISVOU_REF,BX.VOUCHERNO "); strSql.Append(" ,(case (select isnull(sum(obp.amount),0) from OA_Baoxiao_Pay obp where obp.BXGID=bx.BXGID) "); strSql.Append(" when isnull(bx.amount,0) then '是' else '否' end) ISPAYED "); strSql.Append(" ,dbo.[GetUsingStep](wu.workflowid,wu.currentid,@userid,wu.stepno) usingStep,wu.workflowid "); strSql.Append(" FROM OA_Baoxiao BX left join workflow_using wu on wu.bsno=bx.bxgid "); strSql.Append(" where (bx.isdelete=0 or bx.isdelete is null) "); //strSql.Append(" and dbo.[GetUsingStep](@workflowname,@corpid,wu.currentid,@userid,wu.stepno)<>0 "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" and " + strCondition); } return SetAuditData(strSql); } static public Baoxiaomb GetAuditData(string condition, string corpid, string userid, string username) { var list = GetDataList(condition, userid, username, corpid); if (list.Count > 0) return list[0]; return new Baoxiaomb(); } private static List SetAuditData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { BaoxiaoAuditmb data = new BaoxiaoAuditmb(); #region Set DB data to Object data.BXGID = Convert.ToString(reader["BXGID"]); data.BSNO = Convert.ToString(reader["BSNO"]); data.COMPANY = Convert.ToString(reader["COMPANY"]); data.DEPTNAME = Convert.ToString(reader["DEPTNAME"]); data.CREATEUSER = Convert.ToString(reader["CREATEUSER"]); data.CREATEDATE = Convert.ToString(reader["CREATEDATE"]); data.ISDELETE = Convert.ToString(reader["ISDELETE"]); data.DELETEUSER = Convert.ToString(reader["DELETEUSER"]); data.DELETETIME = Convert.ToString(reader["DELETETIME"]); data.BXSTATUS = Convert.ToString(reader["BXSTATUS"]); data.BXSTATUS_REF = Convert.ToString(reader["BXSTATUS_REF"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.AMOUNT = Convert.ToString(reader["AMOUNT"]); data.CHEQUENO = Convert.ToString(reader["CHEQUENO"]); data.CHEQUEAMOUNT = Convert.ToString(reader["CHEQUEAMOUNT"]); data.ISVOU_REF = Convert.ToString(reader["ISVOU_REF"]); data.VOUCHERNO = Convert.ToString(reader["VOUCHERNO"]); data.BXNO = Convert.ToString(reader["BXNO"]); data.ISPAYED = Convert.ToString(reader["ISPAYED"]); data.USINGSTEP = Convert.ToString(reader["USINGSTEP"]); data.WORKFLOWID = Convert.ToString(reader["WORKFLOWID"]); //data.WORKFLOWUSING_GID = Convert.ToString(reader["WORKFLOWUSING_GID"]); #endregion headList.Add(data); } reader.Close(); } return headList; } public static string GetListRangDAStr(string modustr, string userid, string username, string companyid) { string str = ""; //string modustr = ""; var strSql = new StringBuilder(); strSql.Append(" SELECT "); strSql.Append(" VISIBLERANGE,OPERATERANGE,AUTHORITYID,VSSQL "); strSql.Append(" from VW_User_Authority "); strSql.Append(" where [NAME]='" + modustr + "' and USERID='" + userid + "' and ISDELETE=0"); string visiblerange = "4"; string operaterange = "4"; string AUTHORITYID = ""; string VSSQL = ""; 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"]); VSSQL = Convert.ToString(reader["VSSQL"]); break; } reader.Close(); } if (visiblerange == "4") { str = " BX.CREATEUSER='" + username + "'"; } else if (visiblerange == "3") { str = " BX.CREATEUSER='" + username + "'"; } else if (visiblerange == "2") { var rangeDa = new RangeDA(); var deptname = rangeDa.GetDEPTNAME(userid); str = " BX.CREATEUSER in (select showname from vw_user where COMPANYID='" + companyid + "') "; } else if (visiblerange == "1") { str = " BX.CREATEUSER in (select showname from vw_user where COMPANYID='" + companyid + "') "; } else if (visiblerange == "5") { str = " BX.CREATEUSER in (select showname from vw_user where COMPANYID in (select COMPANYID from user_authority_range_company where userid='" + userid + "' and AUTHORITYID='" + AUTHORITYID + "' and VISIBLERANGE=1)) "; //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='" + companyid + "' "; // } // else // { // str = str + " or COMPANYID='" + companyid + "' "; // }; // } // str = str + ")"; // reader.Close(); //} } else if (visiblerange == "6") { str = " EXISTS (select 1 from user_authority_range_op P left join [user] u on (u.GID=P.OPID) where (BX.CREATEUSER=U.SHOWNAME ) AND P.userid='" + userid + "' and P.AUTHORITYID='" + AUTHORITYID + "' and P.VISIBLERANGE=1) "; } else if (visiblerange == "0") { str = " 1=1 "; } VSSQL = VSSQL.Trim(); if (!string.IsNullOrEmpty(VSSQL)) { if (!string.IsNullOrEmpty(str)) { str = str + " and (" + VSSQL + ") "; } else { str = " (" + VSSQL + ") "; } } return str; } #endregion static public List GetBSNO ( string userid,string username,string companyid ) { var rangstr = GetRangDAStr("mod_Baoxiao_BSNO", userid, username, companyid); var strSql = new StringBuilder(); strSql.Append(" select bsno,CUSTNO,mblno,HBLNO,'' OPLB,CUSTOMERNAME CUSTNAME,CNTRTOTAL,GOODSNAME from V_op_bill where FEESTATUS=0 and (OPLB='op_seae' or OPLB='op_seai') "); if (!string.IsNullOrEmpty(rangstr)) { strSql.Append( " and " + rangstr); } return SetDataBSNO(strSql); } /* static public List GetAllBSNO ( string COMPANYID ) { var strSql = new StringBuilder(); strSql.Append(" select bsno,CUSTNO,mblno,OPLB from V_op_bs "); return SetDataBSNO(strSql); }*/ static public List GetAllBSNO ( string condition, string userid, string username, string companyid ) { var rangstr = GetRangDAStr("mod_Baoxiao_BSNO", userid, username, companyid); var strSql = new StringBuilder(); strSql.Append(" select bsno,CUSTNO,mblno,OPLB,HBLNO,CUSTOMERNAME CUSTNAME,CNTRTOTAL,GOODSNAME from V_op_bs "); if (!string.IsNullOrEmpty(condition)) { strSql.Append(" where " + condition ); if (!string.IsNullOrEmpty(rangstr)) { strSql.Append(" and " + rangstr); } }else if (!string.IsNullOrEmpty(rangstr)) { strSql.Append(" where " + rangstr); } return SetDataBSNO(strSql); } static public List GetTruckMBLNO ( string condition ) { var strSql = new StringBuilder(); strSql.Append(" select distinct mblno,HBLNO,'' BSNO,'' CUSTNO,'' OPLB,CUSTOMERNAME CUSTNAME,CNTRTOTAL,GOODSNAME from V_OP_BS "); return SetDataBSNO(strSql); } static public List GetTruckBSNO ( string condition ) { var strSql = new StringBuilder(); strSql.Append(" select bsno, CUSTNO ,mblno,OPLB,HBLNO,CUSTOMERNAME CUSTNAME,CNTRTOTAL,GOODSNAME from V_OP_BS "); if (!string.IsNullOrEmpty(condition)) { strSql.Append(condition); } return SetDataBSNO(strSql); } private static List SetDataBSNO(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = strSql.ToString(); cmd.CommandTimeout = 180000; //要加这一句 using (IDataReader reader = db.ExecuteReader(cmd)) { while (reader.Read()) { BSNOmb data = new BSNOmb(); #region Set DB data to Object data.BSNO = Convert.ToString(reader["BSNO"]); data.CUSTNO = Convert.ToString(reader["CUSTNO"]); data.MBLNO = Convert.ToString(reader["MBLNO"]); data.HBLNO = Convert.ToString(reader["HBLNO"]); data.OPLB = Convert.ToString(reader["OPLB"]); data.CUSTNAME = Convert.ToString(reader["CUSTNAME"]); data.CNTRTOTAL = Convert.ToString(reader["CNTRTOTAL"]); data.GOODSNAME = Convert.ToString(reader["GOODSNAME"]); #endregion headList.Add(data); } reader.Close(); } return headList; } public static string GetRangDAStr ( string modustr, string userid, string username, string companyid ) { string str = ""; //string modustr = ""; var strSql = new StringBuilder(); strSql.Append(" SELECT "); strSql.Append(" VISIBLERANGE,OPERATERANGE "); strSql.Append(" from VW_User_Authority "); strSql.Append(" where [NAME]='" + modustr + "' 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 = " INPUTBY='" + userid + "'"; } else if (visiblerange == "3") { str = " INPUTBY='" + userid + "'"; } else if (visiblerange == "2") { var rangeDa = new RangeDA(); var deptname = rangeDa.GetDEPTNAME(userid); str = " INPUTBY in (select showname from vw_user where COMPANYID='" + companyid + "') and INPUTBY in (select showname from vw_user where DEPTNAME='" + deptname + "')"; } else if (visiblerange == "1") { str = " INPUTBY in (select showname from vw_user where COMPANYID='" + companyid + "') "; } else if (visiblerange == "0") { str = " 1=1 "; } return str; } #region 查询费用数据 static public List GetFeeList(string strCondition) { var strSql = new StringBuilder(); strSql.Append(" SELECT "); strSql.Append(" c.GId,c.BsNo,vs.MBLNO , c.FeeStatus,vs.CUSTNO, "); strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99024 and EnumValueID=c.FeeStatus) as FeeStatus_Ref "); strSql.Append(" ,FeeType, (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99020 and EnumValueID=c.FeeType) as FeeType_Ref "); strSql.Append(" ,FeeName,(select NAME from vMsTruckFeeType where NAME=c.FeeName) as FeeName_Ref "); strSql.Append(" ,FeeDescription,CustomerType,c.CustomerName, "); strSql.Append(" (select top 1 CustName from vMsTruckClient v where v.CustName=c.CustomerName) as CustomerName_Ref "); strSql.Append(" ,Unit,UnitPrice,Quantity,Amount,Currency,ExChangerate,Reason "); strSql.Append(" ,c.Remark,Commissionrate,Settlement,Invoice,OrderAmount,OrderInvoice,SubmitDate "); strSql.Append(" ,Auditoperator,AuditDate,EnteroPerator,EnterDate,DebitNo,IsDebit,IsOpen "); strSql.Append(" ,IsAdvancedpay,Sort,IsInvoice,FeeFrt,IsCrmOrderFee,AuditStatus,InvoiceNum "); strSql.Append(" ,ChequeNum,WmsOutBsNo,c.BXGID,C.TAX,C.TAXRATE,C.NOTAXAMOUNT,C.reason,C.CHEQUENUM,C.ISOPEN, "); strSql.Append(" case c.isopen when 1 then '是' else '否' end ISOPEN_REF "); strSql.Append(" from ch_fee c left join v_op_bs vs on vs.bsno=c.bsno "); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } return SetFeeData(strSql); } static public List GetAmountList(string strCondition) { var strSql = new StringBuilder(); strSql.Append(" SELECT "); strSql.Append(" c.GId,c.BsNo,vs.MBLNO , c.FeeStatus,vs.CUSTNO, "); strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99024 and EnumValueID=c.FeeStatus) as FeeStatus_Ref "); strSql.Append(" ,FeeType, (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99020 and EnumValueID=c.FeeType) as FeeType_Ref "); strSql.Append(" ,FeeName,(select NAME from vMsTruckFeeType where NAME=c.FeeName) as FeeName_Ref "); strSql.Append(" ,FeeDescription,CustomerType,c.CustomerName, "); strSql.Append(" (select top 1 CustName from vMsTruckClient v where v.CustName=c.CustomerName) as CustomerName_Ref "); strSql.Append(" ,Unit,UnitPrice,Quantity,Amount,Currency,ExChangerate,Reason "); strSql.Append(" ,c.Remark,Commissionrate,Settlement,Invoice,OrderAmount,OrderInvoice,SubmitDate "); strSql.Append(" ,Auditoperator,AuditDate,EnteroPerator,EnterDate,DebitNo,IsDebit,IsOpen "); strSql.Append(" ,IsAdvancedpay,Sort,IsInvoice,FeeFrt,IsCrmOrderFee,AuditStatus,InvoiceNum "); strSql.Append(" ,ChequeNum,WmsOutBsNo,c.BXGID,C.TAX,C.TAXRATE,C.NOTAXAMOUNT,C.reason,C.CHEQUENUM,C.ISOPEN, "); strSql.Append(" case c.isopen when 1 then '是' else '否' end ISOPEN_REF,c.feeName+'_'+convert(varchar(50),c.Amount) as FeeNameAmount "); strSql.Append(" from ch_fee c left join vw_settlement vs on vs.bsno=c.bsno "); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } return SetAmountData(strSql); } private static List SetFeeData(StringBuilder strSql) { var bodyList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { MsChFee data = new MsChFee(); #region Set DB data to Object data.GId = Convert.ToString(reader["GId"]); data.BsNo = Convert.ToString(reader["BsNo"]); data.CUSTNO = Convert.ToString(reader["CUSTNO"]); data.FeeStatus = Convert.ToDecimal(reader["FeeStatus"]); data.FeeStatus_Ref = Convert.ToString(reader["FeeStatus_Ref"]); data.FeeType = Convert.ToDecimal(reader["FeeType"]); data.FeeType_Ref = Convert.ToString(reader["FeeType_Ref"]); data.FeeName = Convert.ToString(reader["FeeName"]); data.FeeName_Ref = Convert.ToString(reader["FeeName_Ref"]); data.FeeDescription = Convert.ToString(reader["FeeDescription"]); data.CustomerType = Convert.ToString(reader["CustomerType"]); data.CustomerName = Convert.ToString(reader["CustomerName"]); data.CustomerName_Ref = Convert.ToString(reader["CustomerName_Ref"]); data.Unit = Convert.ToString(reader["Unit"]); data.UnitPrice = Convert.ToDecimal(reader["UnitPrice"]); data.Quantity = Convert.ToDecimal(reader["Quantity"]); data.Amount = Convert.ToDecimal(reader["Amount"]); data.TAX = Convert.ToString(reader["TAX"]); data.Taxrate = Convert.ToDecimal(reader["TAXRATE"]); data.NOTAXAMOUNT = Convert.ToString(reader["NOTAXAMOUNT"]); data.Currency = Convert.ToString(reader["Currency"]); data.ExChangerate = Convert.ToDecimal(reader["ExChangerate"]); data.Reason = Convert.ToString(reader["Reason"]); data.Remark = Convert.ToString(reader["Remark"]); data.Commissionrate = Convert.ToDecimal(reader["Commissionrate"]); data.Settlement = Convert.ToDecimal(reader["Settlement"]); if (reader["Invoice"] != DBNull.Value) data.Invoice = Convert.ToDecimal(reader["Invoice"]); data.OrderAmount = Convert.ToDecimal(reader["OrderAmount"]); data.OrderInvoice = Convert.ToDecimal(reader["OrderInvoice"]); if (reader["SubmitDate"] != DBNull.Value) data.SubmitDate = Convert.ToDateTime(reader["SubmitDate"]); data.Auditoperator = Convert.ToString(reader["Auditoperator"]); if (reader["AuditDate"] != DBNull.Value) data.AuditDate = Convert.ToDateTime(reader["AuditDate"]); data.EnteroPerator = Convert.ToString(reader["EnteroPerator"]); if (reader["EnterDate"] != DBNull.Value) data.EnterDate = Convert.ToDateTime(reader["EnterDate"]); data.DebitNo = Convert.ToString(reader["DebitNo"]); data.IsDebit = Convert.ToString(reader["IsDebit"]); data.IsOpen = Convert.ToString(reader["IsOpen"]); data.IsAdvancedpay = Convert.ToString(reader["IsAdvancedpay"]); data.Sort = Convert.ToString(reader["Sort"]); data.IsInvoice = Convert.ToString(reader["IsInvoice"]); data.FeeFrt = Convert.ToString(reader["FeeFrt"]); data.IsCrmOrderFee = Convert.ToString(reader["IsCrmOrderFee"]); data.AuditStatus = Convert.ToDecimal(reader["AuditStatus"]); data.InvoiceNum = Convert.ToString(reader["InvoiceNum"]); data.ChequeNum = Convert.ToString(reader["ChequeNum"]); data.WmsOutBsNo = Convert.ToString(reader["WmsOutBsNo"]); data.BXGID = Convert.ToString(reader["BXGID"]); data.MBLNO = Convert.ToString(reader["MBLNO"]); data.Reason = Convert.ToString(reader["Reason"]); data.CHEQUENUM = Convert.ToString(reader["CHEQUENUM"]); data.IsOpen = Convert.ToString(reader["IsOpen"]); data.IsOpen_REF = Convert.ToString(reader["IsOpen_REF"]); #endregion bodyList.Add(data); } reader.Close(); } return bodyList; } private static List SetAmountData(StringBuilder strSql) { var bodyList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { MsChFeeAmount data = new MsChFeeAmount(); #region Set DB data to Object data.GId = Convert.ToString(reader["GId"]); data.BsNo = Convert.ToString(reader["BsNo"]); data.CUSTNO = Convert.ToString(reader["CUSTNO"]); data.FeeStatus = Convert.ToDecimal(reader["FeeStatus"]); data.FeeStatus_Ref = Convert.ToString(reader["FeeStatus_Ref"]); data.FeeType = Convert.ToDecimal(reader["FeeType"]); data.FeeType_Ref = Convert.ToString(reader["FeeType_Ref"]); data.FeeName = Convert.ToString(reader["FeeName"]); data.FeeName_Ref = Convert.ToString(reader["FeeName_Ref"]); data.FeeDescription = Convert.ToString(reader["FeeDescription"]); data.CustomerType = Convert.ToString(reader["CustomerType"]); data.CustomerName = Convert.ToString(reader["CustomerName"]); data.CustomerName_Ref = Convert.ToString(reader["CustomerName_Ref"]); data.Unit = Convert.ToString(reader["Unit"]); data.UnitPrice = Convert.ToDecimal(reader["UnitPrice"]); data.Quantity = Convert.ToDecimal(reader["Quantity"]); data.Amount = Convert.ToDecimal(reader["Amount"]); data.TAX = Convert.ToString(reader["TAX"]); data.Taxrate = Convert.ToDecimal(reader["TAXRATE"]); data.NOTAXAMOUNT = Convert.ToString(reader["NOTAXAMOUNT"]); data.Currency = Convert.ToString(reader["Currency"]); data.ExChangerate = Convert.ToDecimal(reader["ExChangerate"]); data.Reason = Convert.ToString(reader["Reason"]); data.Remark = Convert.ToString(reader["Remark"]); data.Commissionrate = Convert.ToDecimal(reader["Commissionrate"]); data.Settlement = Convert.ToDecimal(reader["Settlement"]); if (reader["Invoice"] != DBNull.Value) data.Invoice = Convert.ToDecimal(reader["Invoice"]); data.OrderAmount = Convert.ToDecimal(reader["OrderAmount"]); data.OrderInvoice = Convert.ToDecimal(reader["OrderInvoice"]); if (reader["SubmitDate"] != DBNull.Value) data.SubmitDate = Convert.ToDateTime(reader["SubmitDate"]); data.Auditoperator = Convert.ToString(reader["Auditoperator"]); if (reader["AuditDate"] != DBNull.Value) data.AuditDate = Convert.ToDateTime(reader["AuditDate"]); data.EnteroPerator = Convert.ToString(reader["EnteroPerator"]); if (reader["EnterDate"] != DBNull.Value) data.EnterDate = Convert.ToDateTime(reader["EnterDate"]); data.DebitNo = Convert.ToString(reader["DebitNo"]); data.IsDebit = Convert.ToString(reader["IsDebit"]); data.IsOpen = Convert.ToString(reader["IsOpen"]); data.IsAdvancedpay = Convert.ToString(reader["IsAdvancedpay"]); data.Sort = Convert.ToString(reader["Sort"]); data.IsInvoice = Convert.ToString(reader["IsInvoice"]); data.FeeFrt = Convert.ToString(reader["FeeFrt"]); data.IsCrmOrderFee = Convert.ToString(reader["IsCrmOrderFee"]); data.AuditStatus = Convert.ToDecimal(reader["AuditStatus"]); data.InvoiceNum = Convert.ToString(reader["InvoiceNum"]); data.ChequeNum = Convert.ToString(reader["ChequeNum"]); data.WmsOutBsNo = Convert.ToString(reader["WmsOutBsNo"]); data.BXGID = Convert.ToString(reader["BXGID"]); data.MBLNO = Convert.ToString(reader["MBLNO"]); data.Reason = Convert.ToString(reader["Reason"]); data.CHEQUENUM = Convert.ToString(reader["CHEQUENUM"]); data.IsOpen = Convert.ToString(reader["IsOpen"]); data.IsOpen_REF = Convert.ToString(reader["IsOpen_REF"]); data.FeeNameAmount = Convert.ToString(reader["FeeNameAmount"]); #endregion bodyList.Add(data); } reader.Close(); } return bodyList; } #endregion #region 查询支付数据 static public List GetPayList(string BXGID) { var strSql = new StringBuilder(); strSql.Append(" SELECT *, "); strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=26 and EnumValueID=obp.settlementtype) as settlementtype_Ref "); strSql.Append(" from OA_Baoxiao_Pay obp "); if (BXGID.Trim() != String.Empty) { strSql.Append(" where BXGID='" + BXGID+"'"); } return SetPayData(strSql); } private static List SetPayData(StringBuilder strSql) { var bodyList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { BXPaymb data = new BXPaymb(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.BXGID = Convert.ToString(reader["BXGID"]); data.SETTLEMENTTYPE = Convert.ToString(reader["SETTLEMENTTYPE"]); data.SETTLEMENTTYPE_REF = Convert.ToString(reader["SETTLEMENTTYPE_REF"]); data.TICKETNO = Convert.ToString(reader["TICKETNO"]); data.AMOUNT = Convert.ToString(reader["AMOUNT"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.FINANCIALCODE = Convert.ToString(reader["FINANCIALCODE"]); #endregion bodyList.Add(data); } reader.Close(); } return bodyList; } #endregion #region 提交申请 // 根据handle类型执行更新操作：更新费用信息ch_fee.feestatus和工作流运行表WorkFlowDo信息 // 费用状态值 费用状态主要有11个状态,分别为 // 审核通过（STATUS=0）录入状态（STATUS=1）提交审核（STATUS=2）申请修改 (STATUS=3) // 取消申请(STATUS=5)驳回提交（STATUS=6）驳回申请（STATUS=7）部分结算（STATUS=8）结算完毕（STATUS=9） //List chfeeList internal static DBResult Audit(int newStatus, string WorkFlowName, /*string DEPTGID,*/ string USERID, string Reason, List BaoxiaoList) { var result = new DBResult(); var islast = false; foreach (var BX in BaoxiaoList) { islast = false; if (WorkFlowDAL.GetWorkFlowFinal_BSNO(WorkFlowName, BX.BXGID, USERID)) islast = true; var status = int.Parse(BX.BXSTATUS); var canAudit = true; var BXGID = BX.BXGID; BX.WORKFLOWID = WorkFlowDAL.GetBSNOWorkFlowID(WorkFlowName, BXGID)[0].GID; if (newStatus == 2) { canAudit = status == 1 || status == 6; } else if (newStatus == 0) { canAudit = status == 2; } else if (newStatus == 6) { canAudit = status == 0 || status == 2; } else if (newStatus == 1) { canAudit = status == 2; } else if (newStatus == 8 || newStatus == 9) { canAudit = status == 0; } else if (WorkFlowDAL.GetWorkFlowDo(BX.WORKFLOWID, BX.BXGID)!=0) { canAudit = false; } if (!canAudit) { result.Success = false; if (newStatus == 2) result.Message = "只有在'录入状态'和'驳回提交'状态下才能提交审核操作"; else if (newStatus == 0) { result.Message = "只有在‘提交审核’状态下才能进行‘审核通过’操作"; } else if (newStatus == 6) { result.Message = "只有在‘审核通过’或‘提交审核’状态下才能进行此操作"; } else if (newStatus == 1) { result.Message = "只有‘提交审核’状态下才能进行此操作"; } else if (newStatus == 8 || newStatus == 9) { result.Message = "只有在‘审核通过’状态下才能进行此操作"; } else if (newStatus == 1) { result.Message = "只有未经审核的单据才能撤回"; } return result; } else { if (newStatus == 6) { T_ALL_DA T_ALL_DA = new T_ALL_DA(); var BLCOUNT = T_ALL_DA.GetStrSQL("BLCOUNT", "select count(*) BLCOUNT from v_op_bill where FEESTATUS=1 AND BSNO IN (SELECT BSNO FROM ch_fee where BXGID='"+ BX.BXGID + "') "); if (BLCOUNT != "0") { result.Success = false; result.Message = "费用相关业务已费用锁定,不允许驳回！"; return result; } } } } Database db = DatabaseFactory.CreateDatabase(); //if () var updateSql = " update OA_Baoxiao set BXStatus=@BXStatus where BXGID=@BXGID "; if (newStatus == 0 ) { updateSql = updateSql + " update ch_fee set BSNO=PaymentGID,ACCTAXRATE=TAXRATE from ch_fee f where ISNULL(BSNO,'')='' AND bxgid=@BXGID "; updateSql = updateSql + " update ch_fee set JKGID=CARGO_GID from ch_fee f where (ISNULL(JKGID,'')='' or JKGID='-') and ISNULL(CARGO_GID,'')<>'' AND bxgid=@BXGID "; updateSql = updateSql + " insert into ch_fee_do (gid,billno,bsno,mblno,customername,bstype,feeid,feename,currency, "; updateSql = updateSql + " amount,doamount,feetype,category,billstatus,createtime,exchangerate,origamount,companyid "; updateSql = updateSql + " )select newid(),BXGID,bsno,'',customername,2,gid,feename,currency, "; updateSql = updateSql + " amount,amount as doamount,2,8,1,getdate(),1,amount, "; updateSql = updateSql + " (select companyid from user_company uc where uc.userid=f.enteroperator) "; updateSql = updateSql + " from ch_fee f where bxgid=@BXGID "; if (islast) { updateSql = updateSql + " update ch_fee set feestatus=9,orderamount=amount,settlement=amount where BXGID=@BXGID "; } else { updateSql = updateSql + " update ch_fee set feestatus=2,orderamount=amount where BXGID=@BXGID "; } foreach (var BX in BaoxiaoList) { WorkFlowDAL.InsertWorkFlowDo(WorkFlowName, BX.BXGID, USERID,BX.BXNO); } } else if (newStatus == 6) { updateSql = updateSql + " delete from ch_fee_do where feeid in(select gid from ch_fee where bxgid=@BXGID ) "; updateSql = updateSql + " update ch_fee set feestatus=6,orderamount=0,settlement=0 where BXGID=@BXGID "; updateSql = updateSql + " update ch_fee set bsno='' where ISNULL(PaymentGID,'')<>'' AND BXGID=@BXGID "; updateSql = updateSql + " update ch_fee set JKGID='' where ISNULL(CARGO_GID,'')<>'' and JKGID=CARGO_GID AND BXGID=@BXGID "; foreach (var BX in BaoxiaoList) { WorkFlowDAL.DeleteWorkFlowDo(WorkFlowName, BX.BXGID, USERID, Reason); } }else if (newStatus == 2) { foreach (var BX in BaoxiaoList) { WorkFlowDAL.WorkFlowStart(WorkFlowName, BX.BXGID,USERID,BX.BXNO,BX.BXGID,"","","备注:"+BX.REMARK); } updateSql = updateSql + " update ch_fee set feestatus=2,orderamount=amount where BXGID=@BXGID "; } else if (newStatus == 1) { foreach (var BX in BaoxiaoList) { WorkFlowDAL.DeleteWorkFlowUsing(WorkFlowName, BX.BXGID, USERID); } } else { updateSql = updateSql + " update ch_fee set feestatus=@BXStatus where BXGID=@BXGID "; } const string enumSql = "select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99024 and EnumValueID=@EnumValueID"; if (newStatus == 0 && islast != true) { newStatus = 2; } using (DbConnection connection = db.CreateConnection()) { connection.Open(); try { var cmdEnum = db.GetSqlStringCommand(enumSql); db.AddInParameter(cmdEnum, "@EnumValueID", DbType.Int32, newStatus); var newStatusRef = Convert.ToString(db.ExecuteScalar(cmdEnum)); DbTransaction transaction = connection.BeginTransaction(); try { foreach (var BX in BaoxiaoList) { var cmdUpdate = db.GetSqlStringCommand(updateSql); db.AddInParameter(cmdUpdate, "@BXStatus", DbType.Int32, newStatus); db.AddInParameter(cmdUpdate, "@BXGID", DbType.String, BX.BXGID); db.ExecuteNonQuery(cmdUpdate, transaction); } transaction.Commit(); result.Success = true; result.Message = "提交成功"; result.Data = newStatusRef; } catch (Exception e) { transaction.Rollback(); result.Success = false; if (e is MsChFeeDealException) result.Message = e.Message; else result.Message = "执行提交发生未知错误，请重试"; } } finally { connection.Close(); } } if (newStatus==0||result.Success == true) { SetGZ(BaoxiaoList);//设置挂账信息 } return result; } #endregion public static int SetGZ(List BaoxiaoList) { var _count = 0; var updateSql = ""; foreach (var BX in BaoxiaoList) { var BXGID = BX.BXGID; updateSql = " update ch_fee set feestatus=0 where "; updateSql = updateSql + " CHEQUENUM in( select TICKETNO from OA_Baoxiao_Pay where SETTLEMENTTYPE=4 and bxgid='" + BXGID + "') "; updateSql = updateSql + " and bxgid='"+BXGID+"' "; updateSql = updateSql + " update ch_fee set feestatus=9 where "; updateSql = updateSql + " CHEQUENUM in( select TICKETNO from OA_Baoxiao_Pay where SETTLEMENTTYPE<>4 and bxgid='" + BXGID + "') "; updateSql = updateSql + " and bxgid='" + BXGID + "' "; } return _count; } #region 删除报销单下的费用 static public int DELBXChFee(string BXGID) { var strSql = new StringBuilder(); strSql.Append(" delete from ch_fee where bxgid =( '" + BXGID + "')"); var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } #endregion #region 解除费用与报销单的关联 public static int FeeUnUnion(List feeUnUnionList) { var strSql = new StringBuilder(); var _count = 0; foreach (var Fee in feeUnUnionList) { var GID = Fee.GId; strSql.Append(" update ch_fee set bxgid = null where gid='" + GID + "' "); _count = _count + SetFee(strSql); } return _count;//ExecuteNonQuery } #endregion public static int SetFee(StringBuilder strSql) { var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } } internal class MsChFeeDealException : Exception { internal MsChFeeDealException(string message) : base(message) { } } }