using System; using System.Data; using System.Collections.Generic; using System.Text; using DSWeb.Areas.Import.Models.ImportTrade; using DSWeb.Areas.Import.Models.Comm; using Microsoft.Practices.EnterpriseLibrary.Data; using DSWeb.Areas.CommMng.Models; using DSWeb.EntityDA; using HcUtility.Comm; using DSWeb.Areas.CommMng.DAL; using DSWeb.Areas.Account.Models.ChfeeDetail; using System.Data.SqlClient; using WebSqlHelper; using System.Data.Common; namespace DSWeb.Areas.Import.DAL.ImportSales { public partial class ImportSalesDAL { #region 销售单列表 static public List GetDataList(string strCondition, string userid="", string usercode="", string orgcode="", string sort = null) { var rangstr = GetRangDAListStr("", 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 dbo.trimdate(salesdate) salesdate,s.GID,s.SALESNO,s.CUSTOMERNAME "); strSql.Append(" ,s.OP,SALER,s.CORPID,DEPTGID,dbo.F_Sale_CargoName(s.SALESNO) CARGONAME,s.REMARK,s.CURRENCY,s.EXCHANGERATE,s.AMOUNT,s.AMOUNTRMB "); strSql.Append(" ,dbo.trimdate(s.DELIVERYDATE) DELIVERYDATE,s.DELIVERYADDRESS,DELIVERYCONTACTS,DELIVERYTEL,BSSTATUS,s.FEESTATUS,s.mainstate"); strSql.Append(" ,(select showname from VW_user where userid=s.op) OPRef "); strSql.Append(" ,(select showname from VW_user where userid=s.saler) salerRef "); strSql.Append(" ,(case s.bsstatus when 1 then '锁定' else '未锁定' end) bsstatusRef "); strSql.Append(" ,(case s.feestatus when 1 then '锁定' else '未锁定' end) feestatusRef "); strSql.Append(" ,isnull((select sum(weight) from Import_DeliveryDetail where SALEDETAIL_GID in(select gid from Import_Saledetail where SALESNO=s.SALESNO)),0) DELIVERYWEIGHT "); strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=31 and EnumValueID=s.MainState) as MainstateRef "); strSql.Append(" ,SALESTATUS,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=32 and EnumValueID=s.SALESTATUS) as SALESTATUSRef "); strSql.Append(" ,isnull((select sum(doamount) from SalesAdvancePay_state where salesno=s.salesno ),0) Forecast_Amount "); strSql.Append(" ,s.AmountRMB-isnull((select sum(doamount) from SalesAdvancePay_state where salesno=s.salesno ),0) Forecast_Remain "); strSql.Append(" ,z.WHX_YS,z.Kerenkuan,z.Dairenkuan,s.PREPAYMENT,isnull(s.REPLY,0) REPLY,isnull(s.ISMAKED,0) ISMAKED "); strSql.Append(" ,(select sum(amount*exchangerate) from ch_fee where salesno=s.salesno) SALES_AMOUNT "); strSql.Append(" ,(select sum(settlement*exchangerate) from ch_fee where salesno=s.salesno) SALES_SETTLEMENT "); strSql.Append(" ,dbo.F_SaleInfo(s.SALESNO) SALEINFO "); strSql.Append(" ,dbo.F_DeliveryInfo(s.SALESNO) DeliveryInfo "); strSql.Append(" ,dbo.F_PAYCOMPANY((select top 1 BSNO from Import_Saledetail where SALESNO=s.SALESNO)) PAYCOMPANY,sd.FUNDRATE "); strSql.Append(" ,(select sum(isnull(Amount,0)*isnull(exchangerate,0)) from ch_fee where SALESNO=s.SALESNO) AMOUNTRMB_ACT "); strSql.Append(" ,isnull((select sum(doamount) from SalesAdvancePay_state where salesno=s.salesno ),0)-(select sum(isnull(Amount,0)*isnull(exchangerate,0)) from ch_fee where SALESNO=s.SALESNO) AMOUNTRMB_REMAIN"); strSql.Append(" ,dbo.trimdate((select min(arrivaldate) from import_main where contractno in(select bsno from import_saledetail where salesno=s.salesno))) ArrivalDate"); strSql.Append(" ,convert(bigint ,s.TimeMark) as TimeMark "); strSql.Append(" ,s.ISINVOICE,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=41 and EnumValueID=s.ISINVOICE) ISINVOICE_REF "); strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=10 and EnumValueID=(select port from import_main where ContractNo=(select top 1 bsno from Import_Saledetail where SALESNO=s.SALESNO))) port"); strSql.Append(" ,vc.country ,vc.FactoryNo,vc.name Cargo_Name,vc.productiondate_min,vc.productiondate_max "); strSql.Append(" ,sd.PREPAYMENT SD_PREPAYMENT,sd.PREPAYMENT_ACT SD_PREPAYMENT_ACT,sd.[WEIGHT] SD_WEIGHT,sd.BOXCOUNT SD_BOXCOUNT "); strSql.Append(" ,w.STORAGENAME,dbo.trimdate(m.Ex_sailingdate) Ex_sailingdate,vc.price_limit,dbo.trimdate(w.WMSDATE) WMSDATE "); strSql.Append(" ,sd.PRICE,sd.PRICERMB,sd.DirectPrice,sd.gid SaleDetail_GID,sd.AMOUNTRMB AMOUNTRMB_Detail"); strSql.Append(" ,(select sum(Amount) from ch_fee_order where BSNO in(select gid from Import_SaleZD where SALESNO=s.SALESNO and isnull(ISUSE,0)=1) and (feename='总应收' or feename like '%补款%' )) ZDAMOUNT"); strSql.Append(" ,m.Agent+isnull(m.agent2,'') Agent,m.Inspection_Storage,dbo.trimdate(m.Unsealdate) Unsealdate,c.CargoType "); strSql.Append(" from Import_Sales s "); strSql.Append(" left join vw_importtrade_zhqk z on z.customername=s.CUSTOMERNAME "); strSql.Append(" left join import_saledetail sd on sd.salesno=s.salesno "); strSql.Append(" left join ImportTrade_Cargo vc on vc.gid=sd.cargo_gid "); strSql.Append(" left join wms w on w.Cargo_gid=sd.CARGO_GID "); strSql.Append(" left join import_main m on m.ContractNo=vc.ContractNo "); strSql.Append(" left join import_cargo c on c.ContractNo=m.ContractNo "); strSql.Append(" where 1=1 "); 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 CUSTOMERNAME "); } return SetData(strSql); } static public List GetDataList_Single ( string strCondition, string userid = "", string usercode = "", string orgcode = "", string sort = null ) { var rangstr = GetRangDAListStr("", 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 dbo.trimdate(salesdate) salesdate,s.GID,s.SALESNO,s.CUSTOMERNAME "); strSql.Append(" ,s.OP,SALER,s.CORPID,DEPTGID,dbo.F_Sale_CargoName(s.SALESNO) CARGONAME,s.REMARK,s.CURRENCY,s.EXCHANGERATE,s.AMOUNT,s.AMOUNTRMB "); strSql.Append(" ,dbo.trimdate(s.DELIVERYDATE) DELIVERYDATE,s.DELIVERYADDRESS,DELIVERYCONTACTS,DELIVERYTEL,BSSTATUS,s.FEESTATUS,s.mainstate"); strSql.Append(" ,(select showname from VW_user where userid=s.op) OPRef "); strSql.Append(" ,(select showname from VW_user where userid=s.saler) salerRef "); strSql.Append(" ,(case s.bsstatus when 1 then '锁定' else '未锁定' end) bsstatusRef "); strSql.Append(" ,(case s.feestatus when 1 then '锁定' else '未锁定' end) feestatusRef "); strSql.Append(" ,isnull((select sum(weight) from Import_DeliveryDetail where SALEDETAIL_GID in(select gid from Import_Saledetail where SALESNO=s.SALESNO)),0) DELIVERYWEIGHT "); strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=31 and EnumValueID=s.MainState) as MainstateRef "); strSql.Append(" ,SALESTATUS,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=32 and EnumValueID=s.SALESTATUS) as SALESTATUSRef "); strSql.Append(" ,isnull((select sum(doamount) from SalesAdvancePay_state where salesno=s.salesno ),0) Forecast_Amount "); strSql.Append(" ,s.AmountRMB-isnull((select sum(doamount) from SalesAdvancePay_state where salesno=s.salesno ),0) Forecast_Remain "); strSql.Append(" ,z.WHX_YS,z.Kerenkuan,z.Dairenkuan,s.PREPAYMENT,isnull(s.REPLY,0) REPLY,isnull(s.ISMAKED,0) ISMAKED "); strSql.Append(" ,(select sum(amount*exchangerate) from ch_fee where salesno=s.salesno) SALES_AMOUNT "); strSql.Append(" ,(select sum(settlement*exchangerate) from ch_fee where salesno=s.salesno) SALES_SETTLEMENT "); strSql.Append(" ,dbo.F_SaleInfo(s.SALESNO) SALEINFO "); strSql.Append(" ,dbo.F_DeliveryInfo(s.SALESNO) DeliveryInfo "); strSql.Append(" ,dbo.F_PAYCOMPANY((select top 1 BSNO from Import_Saledetail where SALESNO=s.SALESNO)) PAYCOMPANY,sd.FUNDRATE "); strSql.Append(" ,(select sum(isnull(Amount,0)*isnull(exchangerate,0)) from ch_fee where SALESNO=s.SALESNO) AMOUNTRMB_ACT "); strSql.Append(" ,isnull((select sum(doamount) from SalesAdvancePay_state where salesno=s.salesno ),0)-(select sum(isnull(Amount,0)*isnull(exchangerate,0)) from ch_fee where SALESNO=s.SALESNO) AMOUNTRMB_REMAIN"); strSql.Append(" ,dbo.trimdate((select min(arrivaldate) from import_main where contractno in(select bsno from import_saledetail where salesno=s.salesno))) ArrivalDate"); strSql.Append(" ,convert(bigint ,s.TimeMark) as TimeMark "); strSql.Append(" ,s.ISINVOICE,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=41 and EnumValueID=s.ISINVOICE) ISINVOICE_REF "); strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=10 and EnumValueID=(select port from import_main where ContractNo=(select top 1 bsno from Import_Saledetail where SALESNO=s.SALESNO))) port"); strSql.Append(" ,vc.country ,vc.FactoryNo,vc.name Cargo_Name,vc.productiondate_min,vc.productiondate_max "); strSql.Append(" ,sd.PREPAYMENT SD_PREPAYMENT,sd.PREPAYMENT_ACT SD_PREPAYMENT_ACT,sd.[WEIGHT] SD_WEIGHT,sd.BOXCOUNT SD_BOXCOUNT "); strSql.Append(" ,w.STORAGENAME,dbo.trimdate(m.Ex_sailingdate) Ex_sailingdate,vc.price_limit,dbo.trimdate(w.WMSDATE) WMSDATE "); strSql.Append(" ,sd.PRICE,sd.PRICERMB,sd.DirectPrice,sd.gid SaleDetail_GID,sd.AMOUNTRMB AMOUNTRMB_Detail"); strSql.Append(" ,(select sum(Amount) from ch_fee_order where BSNO in(select gid from Import_SaleZD where SALESNO=s.SALESNO and isnull(ISUSE,0)=1) and (feename='总应收' or feename like '%补款%' )) ZDAMOUNT"); strSql.Append(" ,m.Agent+isnull(m.agent2,'') Agent,m.Inspection_Storage,dbo.trimdate(m.Unsealdate) Unsealdate "); strSql.Append(" from Import_Sales s "); strSql.Append(" left join vw_importtrade_zhqk z on z.customername=s.CUSTOMERNAME "); //strSql.Append(" left join import_saledetail sd on sd.salesno=s.salesno "); //strSql.Append(" left join ImportTrade_Cargo vc on vc.gid=sd.cargo_gid "); //strSql.Append(" left join wms w on w.Cargo_gid=sd.CARGO_GID "); strSql.Append(" left join import_main m on m.ContractNo in(select bsno from Import_Saledetail where SALESNO=s.salesno ) "); strSql.Append(" where 1=1 "); 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 CUSTOMERNAME "); } return SetData(strSql); } static public ImportSalesmb 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 ImportSalesmb(); } private static List SetData ( 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))*/ Database db = DatabaseFactory.CreateDatabase(); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = strSql.ToString(); cmd.CommandTimeout = 120000; using (IDataReader reader = db.ExecuteReader(cmd)) { while (reader.Read()) { ImportSalesmb data = new ImportSalesmb(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.SALESNO = Convert.ToString(reader["SALESNO"]); data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]); data.OP = Convert.ToString(reader["OP"]); data.SALER = Convert.ToString(reader["SALER"]); data.CORPID = Convert.ToString(reader["CORPID"]); data.DEPTGID = Convert.ToString(reader["DEPTGID"]); data.CARGONAME = Convert.ToString(reader["CARGONAME"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.CURRENCY = Convert.ToString(reader["CURRENCY"]); data.EXCHANGERATE = Convert.ToString(reader["EXCHANGERATE"]); data.AMOUNT = Convert.ToString(reader["AMOUNT"]); data.AMOUNTRMB = Convert.ToString(reader["AMOUNTRMB"]); data.SALESDATE = Convert.ToString(reader["SALESDATE"]); data.DELIVERYDATE = Convert.ToString(reader["DELIVERYDATE"]); data.DELIVERYADDRESS = Convert.ToString(reader["DELIVERYADDRESS"]); data.DELIVERYCONTACTS = Convert.ToString(reader["DELIVERYCONTACTS"]); data.DELIVERYTEL = Convert.ToString(reader["DELIVERYTEL"]); data.BSSTATUS = Convert.ToString(reader["BSSTATUS"]); data.FEESTATUS = Convert.ToString(reader["FEESTATUS"]); data.TimeMark = Convert.ToDecimal(reader["TimeMark"]); data.OPRef = Convert.ToString(reader["OPRef"]); data.SALERRef = Convert.ToString(reader["SALERRef"]); data.BSSTATUSRef = Convert.ToString(reader["BSSTATUSRef"]); data.FEESTATUSRef = Convert.ToString(reader["FEESTATUSRef"]); data.DELIVERYWEIGHT = Convert.ToString(reader["DELIVERYWEIGHT"]); data.MAINSTATE = Convert.ToString(reader["MAINSTATE"]); data.SALESTATUS = Convert.ToString(reader["SALESTATUS"]); data.MAINSTATERef = Convert.ToString(reader["MAINSTATERef"]); data.SALESTATUSRef = Convert.ToString(reader["SALESTATUSRef"]); data.Forecast_Amount = Convert.ToString(reader["Forecast_Amount"]); data.Forecast_Remain = Convert.ToString(reader["Forecast_Remain"]); data.WHX_YS = Convert.ToString(reader["WHX_YS"]); data.KERENKUAN = Convert.ToString(reader["KERENKUAN"]); data.DAIRENKUAN = Convert.ToString(reader["DAIRENKUAN"]); data.REPLY = Convert.ToString(reader["REPLY"]); data.ISMAKED = Convert.ToString(reader["ISMAKED"]); data.PREPAYMENT = Convert.ToString(reader["PREPAYMENT"]); data.SALES_AMOUNT = Convert.ToString(reader["SALES_AMOUNT"]); data.SALES_SETTLEMENT = Convert.ToString(reader["SALES_SETTLEMENT"]); data.SALEINFO = Convert.ToString(reader["SALEINFO"]); data.DeliveryInfo = Convert.ToString(reader["DeliveryInfo"]); data.PAYCOMPANY = Convert.ToString(reader["PAYCOMPANY"]); data.FUNDRATE = Convert.ToString(reader["FUNDRATE"]); data.AMOUNTRMB_ACT = Convert.ToString(reader["AMOUNTRMB_ACT"]); data.AMOUNTRMB_REMAIN = Convert.ToString(reader["AMOUNTRMB_REMAIN"]); data.ArrivalDate = Convert.ToString(reader["ArrivalDate"]); data.ISINVOICE = Convert.ToString(reader["ISINVOICE"]); data.ISINVOICE_REF = Convert.ToString(reader["ISINVOICE_REF"]); data.port = Convert.ToString(reader["port"]); data.country = Convert.ToString(reader["country"]); data.FactoryNo = Convert.ToString(reader["FactoryNo"]); data.Cargo_Name = Convert.ToString(reader["Cargo_Name"]); data.productiondate_min = Convert.ToString(reader["productiondate_min"]); data.productiondate_max = Convert.ToString(reader["productiondate_max"]); data.SD_PREPAYMENT = Convert.ToString(reader["SD_PREPAYMENT"]); data.SD_PREPAYMENT_ACT = Convert.ToString(reader["SD_PREPAYMENT_ACT"]); data.SD_WEIGHT = Convert.ToString(reader["SD_WEIGHT"]); data.SD_BOXCOUNT = Convert.ToString(reader["SD_BOXCOUNT"]); data.STORAGENAME = Convert.ToString(reader["STORAGENAME"]); data.Ex_sailingdate = Convert.ToString(reader["Ex_sailingdate"]); data.price_limit = Convert.ToString(reader["price_limit"]); data.WMSDATE = Convert.ToString(reader["WMSDATE"]); data.PRICE = Convert.ToString(reader["PRICE"]); data.PRICERMB = Convert.ToString(reader["PRICERMB"]); data.DirectPrice = Convert.ToString(reader["DirectPrice"]); data.SaleDetail_GID = Convert.ToString(reader["SaleDetail_GID"]); data.ZDAMOUNT = Convert.ToString(reader["ZDAMOUNT"]); data.AMOUNTRMB_Detail = Convert.ToString(reader["AMOUNTRMB_Detail"]); data.Agent = Convert.ToString(reader["Agent"]); data.Inspection_Storage = Convert.ToString(reader["Inspection_Storage"]); data.Unsealdate = Convert.ToString(reader["Unsealdate"]); data.CargoType = Convert.ToString(reader["CargoType"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 待售商品列表 static public List GetCargoList(string strCondition, string userid = "", string usercode = "", string orgcode = "", string sort = null) { var strSql = new StringBuilder(); strSql.Append(" select GID,hth,buyer,name,MainStateRef,dbo.trimdate(arrivaldate) arrivaldate,weight,weight_act,weight_remain,costprice "); strSql.Append(" ,c.factoryno,c.wmsdate,c.productiondate_min,c.productiondate_max "); strSql.Append(" from ImportTrade_Cargo c where 1=1 "); 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 hth DESC"); } return SetCargoData(strSql); } private static List SetCargoData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { ImportCargomb data = new ImportCargomb(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.HTH = Convert.ToString(reader["HTH"]); data.BUYER = Convert.ToString(reader["BUYER"]); data.NAME = Convert.ToString(reader["NAME"]); data.FACTORYNO = Convert.ToString(reader["FACTORYNO"]); data.MAINSTATEREF = Convert.ToString(reader["MAINSTATEREF"]); data.ARRIVALDATE = Convert.ToString(reader["ARRIVALDATE"]); data.WEIGHT = Convert.ToString(reader["WEIGHT"]); data.WEIGHT_ACT = Convert.ToString(reader["WEIGHT_ACT"]); data.WEIGHT_REMAIN = Convert.ToString(reader["WEIGHT_REMAIN"]); data.COSTPRICE = Convert.ToString(reader["COSTPRICE"]); data.WMSDATE = Convert.ToString(reader["WMSDATE"]); data.PRODUCTIONDATE_MIN = Convert.ToString(reader["PRODUCTIONDATE_MIN"]); data.PRODUCTIONDATE_MAX = Convert.ToString(reader["PRODUCTIONDATE_MAX"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 销售单账单列表 static public List GetZDList(string strCondition ) { var strSql = new StringBuilder(); strSql.Append(" select GID,SALESNO,OP,dbo.trimtime(OPDATE) OPDATE,REMARK,CREATEUSER,dbo.trimtime(CREATETIME) CREATETIME"); strSql.Append(" ,isnull(ISUSE,0) ISUSE from Import_SaleZD "); strSql.Append(" where 1=1 "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" and " + strCondition); } //var sortstring = DatasetSort.Getsortstring(sort); strSql.Append(" order by CREATETIME "); return SetZDData(strSql); } static public Import_SaleZDmb GetZDData(string condition, string userid = "", string usercode = "", string orgcode = "") { var list = GetZDList(condition); if (list.Count > 0) return list[0]; //否则 return new Import_SaleZDmb(); } private static List SetZDData(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))*/ Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { Import_SaleZDmb data = new Import_SaleZDmb(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.SALESNO = Convert.ToString(reader["SALESNO"]); data.OP = Convert.ToString(reader["OP"]); data.OPDATE = Convert.ToString(reader["OPDATE"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.CREATEUSER = Convert.ToString(reader["CREATEUSER"]); data.CREATETIME = Convert.ToString(reader["CREATETIME"]); data.ISUSE = Convert.ToString(reader["ISUSE"]); #endregion headList.Add(data); } reader.Close(); } return headList; } static public List GetZDDetailList(string strCondition, string userid = "", string usercode = "", string orgcode = "", string sort = null) { var strSql = new StringBuilder(); strSql.Append(" select GID,SALESNO,ZD_GID,SALEDETAIL_GID,WEIGHT,BOXCOUNT,CURRENCY,PRICE,AMOUNT,EXCHANGERATE_CUST,PREPAYMENT,FUNDRATE "); strSql.Append(" ,dbo.trimdate(STOCKDATE_1) STOCKDATE_1,dbo.trimdate(STOCKDATE_2) STOCKDATE_2,AGENCRATE_CUST,REMARK "); strSql.Append(" ,(select top 1 name from Import_cargo where gid in( select CARGO_GID from Import_Saledetail where gid=Import_SaleZDDetail.SALEDETAIL_GID)) NAME"); strSql.Append(" ,( select CARGO_GID from Import_Saledetail where gid=Import_SaleZDDetail.SALEDETAIL_GID) CARGO_GID"); strSql.Append(" from Import_SaleZDDetail "); strSql.Append(" where 1=1 "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" and " + strCondition); } //var sortstring = DatasetSort.Getsortstring(sort); strSql.Append(" order by WEIGHT "); return SetZDDetailData(strSql); } private static List SetZDDetailData(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))*/ Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { Import_SaleZDDetailmb data = new Import_SaleZDDetailmb(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.SALESNO = Convert.ToString(reader["SALESNO"]); data.ZD_GID = Convert.ToString(reader["ZD_GID"]); data.SALEDETAIL_GID = Convert.ToString(reader["SALEDETAIL_GID"]); data.WEIGHT = Convert.ToString(reader["WEIGHT"]); data.BOXCOUNT = Convert.ToString(reader["BOXCOUNT"]); data.CURRENCY = Convert.ToString(reader["CURRENCY"]); data.PRICE = Convert.ToString(reader["PRICE"]); data.AMOUNT = Convert.ToString(reader["AMOUNT"]); data.EXCHANGERATE_CUST = Convert.ToString(reader["EXCHANGERATE_CUST"]); data.PREPAYMENT = Convert.ToString(reader["PREPAYMENT"]); data.FUNDRATE = Convert.ToString(reader["FUNDRATE"]); data.STOCKDATE_1 = Convert.ToString(reader["STOCKDATE_1"]); data.STOCKDATE_2 = Convert.ToString(reader["STOCKDATE_2"]); data.AGENCRATE_CUST = Convert.ToString(reader["AGENCRATE_CUST"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.NAME = Convert.ToString(reader["NAME"]); data.CARGO_GID = Convert.ToString(reader["CARGO_GID"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 将商品添加至销售合同 static public int AddSales(string SALESNO,string CargoList, string corpcode, string userid) { var strSql = new StringBuilder(); strSql.Append(" insert Into import_saledetail (GID,SALESNO,CARGO_GID,BSNO,[WEIGHT],BOXCOUNT,CURRENCY,PRICE,PRICERMB,AMOUNT,EXCHANGERATE,AMOUNTRMB,PREPAYMENT,EXCHANGERATE_Cust,Agencrate_Cust,FUNDRATE) "); strSql.Append(" select newid(),'" + SALESNO + "',GID,contractno,0,0,'RMB',0,0,0,1,0,0,1,0,0 from ImportTrade_Cargo where gid in( " + CargoList + ") and isnull(ISDELETE,0)<>1 "); var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } static public int DeleteDetail ( string SALESNO,string CargoList) { var strSql = new StringBuilder(); if (CargoList == "") { strSql.Append(" delete from Import_DeliveryDetail where GID in( select gid from Import_SaleDetail where SALESNO='" + SALESNO + "')"); strSql.Append(" delete from Import_SaleDetail where SALESNO='" + SALESNO + "' "); } else { strSql.Append(" delete from Import_SaleDetail where gid in('" + CargoList + "') and BSNO not in (select BSNO from ch_fee where SALESNO='" + SALESNO + "') "); } var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } #endregion #region 销售合同内商品明细列表 static public List GetDetailList(string strCondition, string userid = "", string usercode = "", string orgcode = "", string sort = null) { /* 暂不限制查询范围 var rangstr = GetRangDAListStr("", 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 s.GID,s.SALESNO,s.CARGO_GID,s.BSNO,s.WEIGHT,s.BOXCOUNT,s.CURRENCY,convert(numeric(18,6),s.PRICE) price,convert(numeric(18,6),s.PRICERMB) PRICERMB "); strSql.Append(" ,convert(numeric(18,2),s.AMOUNT) AMOUNT,s.EXCHANGERATE "); strSql.Append(" ,convert(numeric(18,2),s.AMOUNTRMB) AMOUNTRMB,s.REMARK,HTH,NAME,WEIGHT_ACT,WEIGHT_REMAIN,BOXCOUNT_REMAIN,c.costPrice "); /* strSql.Append(" ,(select sum(weight) from import_deliverydetail where saledetail_gid=s.gid) Deliveryed_WEIGHT "); strSql.Append(" ,(select sum(boxcount) from import_deliverydetail where saledetail_gid=s.gid) Deliveryed_boxcount ");*/ strSql.Append(" ,ss.CUSTOMERNAME,s.PREPAYMENT,s.PREPAYMENT_ACT,s.PriceRemark "); strSql.Append(" ,(select sum(WEIGHT) from Import_DeliveryDetail where SaleDetail_GID=s.GID) Delivery_Weight "); strSql.Append(" ,(select sum(BOXCOUNT) from Import_DeliveryDetail where SaleDetail_GID=s.GID) Delivery_BoxCount "); strSql.Append(" ,S.DirectPrice,S.DirectShop,s.Exchangerate_Cust,s.AGENCRATE_Cust,dbo.trimdate(s.STOCKDATE_1) STOCKDATE_1,dbo.trimdate(STOCKDATE_2) STOCKDATE_2,s.FUNDRATE "); strSql.Append(" from Import_saledetail s "); strSql.Append(" left join ImportTrade_Cargo c on c.gid=s.CARGO_GID "); strSql.Append(" left join Import_Sales ss on ss.salesno=s.salesno "); //strSql.Append(" left join Import_DeliveryDetail DDL on DDL.SaleDetail_GID=s.GID "); strSql.Append(" where 1=1 "); 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 BSNO"); } return SetDetailData(strSql); } private static List SetDetailData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { ImportSaledetailmb data = new ImportSaledetailmb(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.SALESNO = Convert.ToString(reader["SALESNO"]); data.CARGO_GID = Convert.ToString(reader["CARGO_GID"]); data.BSNO = Convert.ToString(reader["BSNO"]); data.WEIGHT = Convert.ToString(reader["WEIGHT"]); data.BOXCOUNT = Convert.ToString(reader["BOXCOUNT"]); data.CURRENCY = Convert.ToString(reader["CURRENCY"]); data.PRICE = Convert.ToString(reader["PRICE"]); data.AMOUNT = Convert.ToString(reader["AMOUNT"]); data.PREPAYMENT = Convert.ToString(reader["PREPAYMENT"]); data.PREPAYMENT_ACT = Convert.ToString(reader["PREPAYMENT_ACT"]); data.EXCHANGERATE = Convert.ToString(reader["EXCHANGERATE"]); data.PRICERMB = Convert.ToString(reader["PRICERMB"]); data.AMOUNTRMB = Convert.ToString(reader["AMOUNTRMB"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.HTH = Convert.ToString(reader["HTH"]); data.NAME = Convert.ToString(reader["NAME"]); data.WEIGHT_ACT = Convert.ToString(reader["WEIGHT_ACT"]); data.WEIGHT_REMAIN = Convert.ToString(reader["WEIGHT_REMAIN"]); data.BOXCOUNT_REMAIN = Convert.ToString(reader["BOXCOUNT_REMAIN"]); data.COSTPRICE = Convert.ToString(reader["COSTPRICE"]); data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]); data.PRICEREMARK = Convert.ToString(reader["PRICEREMARK"]); data.Delivery_Weight = Convert.ToString(reader["Delivery_Weight"]); data.Delivery_Boxcount = Convert.ToString(reader["Delivery_Boxcount"]); data.DirectPrice = Convert.ToString(reader["DirectPrice"]); data.DirectShop = Convert.ToString(reader["DirectShop"]); data.Exchangerate_Cust = Convert.ToString(reader["Exchangerate_Cust"]); data.AGENCRATE_Cust = Convert.ToString(reader["AGENCRATE_Cust"]); data.STOCKDATE_1 = Convert.ToString(reader["STOCKDATE_1"]); data.STOCKDATE_2 = Convert.ToString(reader["STOCKDATE_2"]); data.FUNDRATE = Convert.ToString(reader["FUNDRATE"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #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]='modImportSales' 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 = " 1=2 "; } else if (visiblerange == "3") { str = " (OP='" + username + "' or SALER='" + username + "')"; } else if (visiblerange == "2") { var rangeDa = new RangeDA(); var deptname = rangeDa.GetDEPTNAME(userid); str = " SALER in (select showname from vw_user where deptgid=(select deptgid from vw_user where userid='" + userid + "')) "; } else if (visiblerange == "1") { str = " SALER in (select showname from vw_user where COMPANYID='" + companyid + "')"; } else if (visiblerange == "0") { str = " 1=1 "; } return str; } #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 "); strSql.Append(" from VW_User_Authority "); strSql.Append(" where [NAME]='modPaySettleAppSearch' 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 = " (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 deptname = rangeDa.GetDEPTNAME(userid); str = " (OP in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + companyid + "') and GID in (select userid from user_baseinfo where DEPTNAME='" + deptname + "'))" + " OR INPUTBY in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + companyid + "') and GID in (select userid from user_baseinfo where DEPTNAME='" + deptname + "'))" + " OR SALE in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + companyid + "') and GID in (select userid from user_baseinfo where DEPTNAME='" + deptname + "'))" + " OR CUSTSERVICE in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + companyid + "') and GID in (select userid from user_baseinfo where DEPTNAME='" + deptname + "')))"; } else if (visiblerange == "1") { str = " (OP in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + companyid + "'))" + " OR INPUTBY in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + companyid + "'))" + " OR SALE in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + companyid + "'))" + " OR CUSTSERVICE in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + companyid + "')))"; } else if (visiblerange == "0") { str = " 1=1 "; } return str; } #endregion #region 出库通知单 列表 static public List GetDeliveryList ( string strCondition, string userid = "", string usercode = "", string orgcode = "", string sort = null ) { var rangstr = GetRangDAListStr("", 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,SALESNO,DELIVERYNO,dbo.trimdate(DELIVERYDATE) DELIVERYDATE,CUSTOMERNAME,CORPID,DEPTGID "); strSql.Append(" ,OP,CONTACT,TEL,TRUCKNO,REMARK,convert(bigint,timemark) TIMEMARK ");//,WAREHOUSE,SALER strSql.Append(" ,(select name from company where gid=CORPID) CORPRef "); strSql.Append(" ,(select deptname from sys_dept where gid=DEPTGID) DEPTRef "); strSql.Append(" ,(select top 1 STORAGENAME from wms where ASSOCIATEDNO=(select top 1 BSNO from Import_Saledetail where SALESNO=import_delivery.SALESNO)) WAREHOUSE "); strSql.Append(" ,BSSTATUS,OUTRECEIPT,SFKC,SFCM "); strSql.Append(" ,(select top 1 SALER from Import_Sales where SALESNO =import_delivery.SALESNO) SALER "); strSql.Append(" ,DeliveryType,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=42 and EnumValueID=DeliveryType) DeliveryType_REF "); strSql.Append(" from import_delivery where 1=1 "); 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 DELIVERYNO DESC"); } return SetDeliveryData(strSql); } static public Deliverymb GetDeliveryData ( string condition, string userid = "", string usercode = "", string orgcode = "" ) { var list = GetDeliveryList(condition, userid, usercode, orgcode); if (list.Count > 0) return list[0]; //否则 return new Deliverymb(); } private static List SetDeliveryData ( 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()) { Deliverymb data = new Deliverymb(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.DELIVERYNO = Convert.ToString(reader["DELIVERYNO"]); data.SALESNO = Convert.ToString(reader["SALESNO"]); data.DELIVERYDATE = Convert.ToString(reader["DELIVERYDATE"]); data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]); data.CORPID = Convert.ToString(reader["CORPID"]); data.DEPTGID = Convert.ToString(reader["DEPTGID"]); data.OP = Convert.ToString(reader["OP"]); data.SALER = Convert.ToString(reader["SALER"]); data.CONTACT = Convert.ToString(reader["CONTACT"]); data.TEL = Convert.ToString(reader["TEL"]); data.TRUCKNO = Convert.ToString(reader["TRUCKNO"]); data.WAREHOUSE = Convert.ToString(reader["WAREHOUSE"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.TIMEMARK = Convert.ToString(reader["TIMEMARK"]); data.CORPRef = Convert.ToString(reader["CORPRef"]); data.DEPTRef = Convert.ToString(reader["DEPTRef"]); data.BSSTATUS = Convert.ToString(reader["BSSTATUS"]); data.OUTRECEIPT = Convert.ToString(reader["OUTRECEIPT"]); data.SFKC = Convert.ToString(reader["SFKC"]); data.SFCM = Convert.ToString(reader["SFCM"]); data.DeliveryType = Convert.ToString(reader["DeliveryType"]); data.DeliveryType_REF = Convert.ToString(reader["DeliveryType_REF"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 出库通知单 明细 static public List GetDeliveryDetailList ( string strCondition, string userid = "", string usercode = "", string orgcode = "", string sort = null ) { var rangstr = GetRangDAListStr("", 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 D.GID,D.DELIVERYNO,D.SALEDETAIL_GID "); strSql.Append(" ,D.WEIGHT,D.BOXCOUNT,D.WEIGHT_EX,D.BOXCOUNT_EX ,s.salesno,s.weight WEIGHT_SALE,s.boxcount BOXCOUNT_SALE ,c.name CARGONAME,c.factoryno,m.hth "); strSql.Append(" ,(select sum(weight) from import_deliverydetail where saledetail_gid=D.SALEDETAIL_GID) WEIGHT_DE "); strSql.Append(" ,(select sum(boxcount) from import_deliverydetail where saledetail_gid=D.SALEDETAIL_GID) boxcount_DE "); strSql.Append(" ,c.gid CARGO_GID,SS.CUSTOMERNAME,D.remark,ss.SALESTATUS,ss.mainstate,w.GOODSSTOCK,w.GOODSPACKSTOCK "); strSql.Append(" from Import_DeliveryDetail D "); strSql.Append(" left join import_saledetail s on s.gid=d.SALEDETAIL_GID "); strSql.Append(" left join import_cargo c on c.gid=s.cargo_gid "); strSql.Append(" left join import_main m on c.contractno=m.contractno "); strSql.Append(" left join Import_sales ss on ss.salesno=s.salesno "); strSql.Append(" left join wms w on w.Cargo_gid=c.GID and isnull(w.isdelete,0)=0 "); strSql.Append(" where 1=1 "); 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 m.hth"); } return SetDeliveryDetailData(strSql); } static public DeliveryDetailmb GetDeliveryDetailData ( string condition, string userid = "", string usercode = "", string orgcode = "" ) { var list = GetDeliveryDetailList(condition, userid, usercode, orgcode); if (list.Count > 0) return list[0]; //否则 return new DeliveryDetailmb(); } private static List SetDeliveryDetailData ( StringBuilder strSql ) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { DeliveryDetailmb data = new DeliveryDetailmb(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.DELIVERYNO = Convert.ToString(reader["DELIVERYNO"]); data.SALEDETAIL_GID = Convert.ToString(reader["SALEDETAIL_GID"]); data.WEIGHT = Convert.ToString(reader["WEIGHT"]); data.BOXCOUNT = Convert.ToString(reader["BOXCOUNT"]); data.WEIGHT_EX = Convert.ToString(reader["WEIGHT_EX"]); data.BOXCOUNT_EX = Convert.ToString(reader["BOXCOUNT_EX"]); data.SALESNO = Convert.ToString(reader["SALESNO"]); data.HTH = Convert.ToString(reader["HTH"]); data.CARGONAME = Convert.ToString(reader["CARGONAME"]); data.FACTORYNO = Convert.ToString(reader["FACTORYNO"]); data.WEIGHT_SALE = Convert.ToString(reader["WEIGHT_SALE"]); data.BOXCOUNT_SALE = Convert.ToString(reader["BOXCOUNT_SALE"]); data.WEIGHT_DE = Convert.ToString(reader["WEIGHT_DE"]); data.BOXCOUNT_DE = Convert.ToString(reader["BOXCOUNT_DE"]); data.CARGO_GID = Convert.ToString(reader["CARGO_GID"]); data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.SALESTATUS = Convert.ToString(reader["SALESTATUS"]); data.MAINSTATE = Convert.ToString(reader["MAINSTATE"]); data.GOODSSTOCK = Convert.ToString(reader["GOODSSTOCK"]); data.GOODSPACKSTOCK = Convert.ToString(reader["GOODSPACKSTOCK"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 删除出库单 static public int DeleteDelivery ( string DELIVERYNO ) { var strSql = new StringBuilder(); strSql.Append(" delete from Import_DeliveryDetail where DELIVERYNO ='" + DELIVERYNO + "' "); strSql.Append(" delete from Import_Delivery where DELIVERYNO ='" + DELIVERYNO + "' "); var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } #endregion #region 将销售明细添加至出库单明细 static public int AddDeliveryDetail(string DELIVERYNO, string DetailList, string corpcode, string userid) { var strSql = new StringBuilder(); strSql.Append(" insert Into Import_DeliveryDetail (GID,DELIVERYNO,SALEDETAIL_GID,WEIGHT,BOXCOUNT,WEIGHT_EX,BOXCOUNT_EX) "); strSql.Append(" select newid(),'" + DELIVERYNO + "',GID,WEIGHT,BOXCOUNT,WEIGHT,BOXCOUNT from Import_Saledetail where gid in( " + DetailList + ") "); strSql.Append(" and ( select salestatus from import_sales where salesno=Import_Saledetail.salesno )=2 ");//确保销售单正处于提交出库的状态 var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } static public int DeleteDeliveryDetail(string DELIVERYNO, string GIDList) { var strSql = new StringBuilder(); if (GIDList == "") { strSql.Append(" delete from ch_fee where DELIVERYNO in(select gid from Import_DeliveryDetail where DELIVERYNO= '" + DELIVERYNO + "') "); strSql.Append(" delete from Import_DeliveryDetail where DELIVERYNO ='" + DELIVERYNO + "' "); } else { strSql.Append(" delete from ch_fee where DELIVERYNO in(" + GIDList + ") "); strSql.Append(" delete from Import_DeliveryDetail where gid in(" + GIDList + ") and DELIVERYNO ='" + DELIVERYNO + "' "); } var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } static public string GetCUSTOMERNAME ( string DELIVERYNO ) { var strSql = new StringBuilder(); strSql.Append(" select CUSTOMERNAME from Import_sales where salesno= "); strSql.Append(" (select top 1 salesno from import_saledetail where gid in (select saledetail_gid from Import_DeliveryDetail where DELIVERYNO='" + DELIVERYNO + "')) "); var CUSTOMERNAME = ""; Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]); } reader.Close(); } return CUSTOMERNAME; } #endregion #region 锁定出库单/解锁出库单 static public int LockDelivery(string DELIVERYNO) { var strSql = new StringBuilder(); //更改状态标记 strSql.Append(" update Import_Delivery set BSSTATUS=2 where DELIVERYNO ='" + DELIVERYNO + "' and BSSTATUS in(0,6) "); var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } static public int UnLockDelivery(string DELIVERYNO) { var strSql = new StringBuilder(); //不处理费用 //不处理库存 //更改状态标记 strSql.Append(" update Import_Delivery set BSSTATUS=0 where DELIVERYNO ='" + DELIVERYNO + "' and BSSTATUS=2 "); var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } static public int LockDelivery_2 ( string DELIVERYNO ) { var strSql = new StringBuilder(); //更改状态标记 strSql.Append(" update Import_Delivery set BSSTATUS=1 where DELIVERYNO ='" + DELIVERYNO + "' and BSSTATUS=2 "); var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); // return _count; } static public int UnLockDelivery_2 ( string DELIVERYNO ) { var strSql = new StringBuilder(); //不处理费用 //不处理库存 //更改状态标记 strSql.Append(" update Import_Delivery set BSSTATUS=2 where DELIVERYNO ='" + DELIVERYNO + "' and BSSTATUS=1 "); var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } #endregion #region static public List GetFeeList(string strCondition, string sort) { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append("GId,BsNo,FeeStatus,"); strSql.Append("(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99024 and EnumValueID=ch_fee.FeeStatus) as FeeStatus_Ref"); strSql.Append(",FeeType,"); strSql.Append("(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99020 and EnumValueID=ch_fee.FeeType) as FeeType_Ref"); strSql.Append(",FeeName,"); strSql.Append("(select NAME from vMsTruckFeeType where NAME=ch_fee.FeeName) as FeeName_Ref"); strSql.Append(",FeeDescription,CustomerType,CustomerName,"); strSql.Append("(select CustName from vMsTruckClient where CustName=ch_fee.CustomerName) as CustomerName_Ref"); strSql.Append(",Unit,UnitPrice,Quantity,Amount,Currency,ExChangerate,Reason"); strSql.Append(",Remark,Commissionrate,Settlement,Invoice,OrderAmount,OrderInvoice,SubmitDate"); strSql.Append(",(select showname from [user] where gid=ch_fee.Auditoperator) Auditoperator,AuditDate,EnteroPerator,EnterDate,DebitNo,IsDebit,IsOpen"); strSql.Append(",IsAdvancedpay,Sort,IsInvoice,FeeFrt,IsCrmOrderFee,AuditStatus,InvoiceNum"); strSql.Append(",ChequeNum,WmsOutBsNo,accdate,voucherno"); strSql.Append(",(select ShowName from [user] where GID=ch_fee.EnteroPerator) as OpName"); strSql.Append(",dbo.F_SettlementDate(gid,1) SettlementDate,SALESNO,DELIVERYNO "); strSql.Append(",(select top 1 BILLNO from ch_fee_do where feeid=ch_fee.gid and category=2 ) SETTLEBILLNO "); strSql.Append(",Amount*ExchangeRate AMOUNTRMB "); strSql.Append(" from ch_fee "); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by sort"); } return SetFeeData(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_Sales data = new MsChFee_Sales(); #region Set DB data to Object data.GId = Convert.ToString(reader["GId"]); data.BsNo = Convert.ToString(reader["BsNo"]); 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.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.ACCDATE = Convert.ToString(reader["ACCDATE"]); data.VOUCHERNO = Convert.ToString(reader["VOUCHERNO"]); data.OpName = Convert.ToString(reader["OpName"]); data.SettlementDate = Convert.ToString(reader["SettlementDate"]); data.SALESNO = Convert.ToString(reader["SALESNO"]); data.DELIVERYNO = Convert.ToString(reader["DELIVERYNO"]); data.SETTLEBILLNO = Convert.ToString(reader["SETTLEBILLNO"]); data.AMOUNTRMB = Convert.ToString(reader["AMOUNTRMB"]); #endregion bodyList.Add(data); } reader.Close(); } return bodyList; } #endregion #region 事务：撤销结算 static public int SetUnSettlement ( string BILLNO ) { int iResult = 0; using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { var strSql = new StringBuilder(); strSql.Append(" update ch_fee set settlement=0,FEESTATUS=0 where gid in(select feeid from ch_fee_do where billno='" + BILLNO + "') "); strSql.Append(" delete from ch_fee_do where BILLNO='" + BILLNO + "' "); strSql.Append(" delete from ch_fee_settlement where BILLNO='" + BILLNO + "' "); SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString()); sqlTran.Commit(); iResult = 1;//状态为1表示插入成功 } catch (Exception execError) { iResult = -1;//有异常,插入失败 sqlTran.Rollback(); iResult = -2;//插入异常,事务已回滚成功 throw execError; } finally { SqlHelper.CloseConnection(); } } return iResult; } static public int ResetDoamount ( ) { int iResult = 0; using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { var strSql = new StringBuilder(); strSql.Append(" delete from ch_fee_advance_payment_detail where SETTLEBILLNO not in ( "); strSql.Append(" select BILLNO from ch_fee_settlement) and isnull(SETTLEBILLNO,'')<>'' "); strSql.Append(" update ch_fee_advance_payment set doamount=isnull( "); strSql.Append(" (select sum(doamount) from ch_fee_advance_payment_detail "); strSql.Append(" where BILLNO=ch_fee_advance_payment.BILLNO and isnull(ISDELETE,0)=0),0) "); SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString()); sqlTran.Commit(); iResult = 1;//状态为1表示插入成功 } catch (Exception execError) { iResult = -1;//有异常,插入失败 sqlTran.Rollback(); iResult = -2;//插入异常,事务已回滚成功 throw execError; } finally { SqlHelper.CloseConnection(); } } return iResult; } #endregion #region 增加/删除出库结算单 static public int AddSettlement ( string DELIVERYNO, string CORPID, string USERID ) {//目前已无用 var strSql = new StringBuilder(); strSql.Append(" insert into ch_fee_settlement (GID,BILLNO,BILLDATE,SETTLEMODE,CUSTOMERNAME,settletype,AMOUNTRMB,ACCOUNTRMB "); strSql.Append(" ,rate,AMOUNTUSD,ACCOUNTUSD,BILLTYPE,SETTLEUSER,SETTLETIME,ISEXPORT,BILLSTATUS,COMPANYID "); strSql.Append(" ,ACCOUNTS_CURRENCY,ACCOUNTS_RATE,ACCOUNTS_MONEY,PREPAY_CURRENCY,PREPAY_RATE,PREPAY_MONEY "); strSql.Append(" ,AHSR_CURRENCY,AHSR_RATE,AHSR_MONEY,FINANCIAL_CURRENCY,FINANCIAL_RATE,FINANCIAL_MONEY "); strSql.Append(" ,ADVANCE_CURRENCY,ADVANCE_RATE,ADVANCE_MONEY,CURR "); strSql.Append(" ) VALUES( "); strSql.Append(" newid(),'" + DELIVERYNO + "',getdate(),2,'',4,0,'' "); strSql.Append(" ,1,0,'',1,'" + USERID + "',null,0,2,'" + CORPID + "' "); strSql.Append(" ,0,1,0,0,1,0,0,1,0,0,1,0,0,1,0,'RMB') "); var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } static public int GetVOUNO ( string BILLNO ) {//目前已无用 var strSql = new StringBuilder(); strSql.Append(" select count(*) _count from ch_fee_settlement where BILLNO='" + BILLNO + "' and isnull(VOUNO,'')<>'' "); var _count = 0; Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { _count = Convert.ToInt16(reader["_count"]); } reader.Close(); } return _count; } static public int DelSettlement ( string BILLNO ) { var strSql = new StringBuilder(); strSql.Append(" delete from ch_fee_do where BILLNO='" + BILLNO + "' "); strSql.Append(" delete from ch_fee_settlement where BILLNO='" + BILLNO + "' "); var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } static public int SetAdvancePay ( string BILLNO,string SEALSNO ) { var strSql = new StringBuilder(); strSql.Append(" update ch_fee_advance_payment_detail set SETTLEBILLNO='" + BILLNO + "' where SETTLEBILLNO='" + SEALSNO + "' "); var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } static public int DelAdvancePay ( string BILLNO ) { var strSql = new StringBuilder(); strSql.Append(" update ch_fee_advance_payment set doamount=isnull((select sum(doamount) from ch_fee_advance_payment_detail "); strSql.Append(" where BILLNO=ch_fee_advance_payment.BILLNO and SETTLEBILLNO<>'" + BILLNO + "' and isnull(ISDELETE,0)=0),0) "); strSql.Append(" where BILLNO in(select billno from ch_fee_advance_payment_detail where SETTLEBILLNO='" + BILLNO + "' and isnull(ISDELETE,0)=0) "); strSql.Append(" delete from ch_fee_advance_payment_detail where SETTLEBILLNO='" + BILLNO + "' "); var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } #endregion #region 提交财务审核/提交出库 static public int SetSalesAudit ( string SEALSNO ) { var strSql = new StringBuilder(); strSql.Append(" update Import_Sales set SALESTATUS=1 where SALESNO='" + SEALSNO + "' "); strSql.Append(" update SalesAdvancePay_state set PASSED=1 where SALESNO='" + SEALSNO + "' "); var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } static public int SetSalesUnAudit(string SEALSNO) { var strSql = new StringBuilder(); strSql.Append(" update Import_Sales set SALESTATUS=0 where SALESNO='" + SEALSNO + "' "); strSql.Append(" update SalesAdvancePay_state set PASSED=0 where SALESNO='" + SEALSNO + "' "); var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } static public int SetSalesOver(string SEALSNO) { var strSql = new StringBuilder(); strSql.Append(" update Import_Sales set SALESTATUS=3 where SALESNO='" + SEALSNO + "' "); var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } static public int LockSaleStatus(string DELIVERYNO) { var strSql = new StringBuilder(); strSql.Append(" update Import_Sales set SALESTATUS=2 where SALESNO='" + DELIVERYNO + "' or SALESNO in(select salesno from Import_Delivery where DELIVERYNO= '" + DELIVERYNO + "') "); var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } static public int UnLockSaleStatus( string SEALSNO) { var strSql1 = new StringBuilder(); strSql1.Append(" select Count(*) _count from import_deliveryDetail where SALEDETAIL_GID in( "); strSql1.Append(" select gid from Import_Saledetail where SALESNO='"+SEALSNO+"') "); var _delivery = 0; Database db1 = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db1.ExecuteReader(CommandType.Text, strSql1.ToString())) { while (reader.Read()) { _delivery = Convert.ToInt16(reader["_count"]); } reader.Close(); } if (_delivery > 0) { return -1; } else { var strSql = new StringBuilder(); strSql.Append(" update Import_Sales set SALESTATUS=1 where SALESNO='" + SEALSNO + "' "); Database db = DatabaseFactory.CreateDatabase(); var _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } } static public int SetRKState ( string GID,string PASSED ) { var strSql = new StringBuilder(); strSql.Append(" update SalesAdvancePay_state set PASSED=" + PASSED + " where GID='" + GID + "' "); strSql.Append(" update Import_Saledetail set PREPAYMENT_ACT= "); strSql.Append(" (select sum(doamount) from SalesAdvancePay_state where SALESNO=Import_Saledetail.SALESNO and PASSED=1 ) "); strSql.Append(" *(case Import_Saledetail.PREPAYMENT when 0 then 0 else Import_Saledetail.PREPAYMENT end) "); strSql.Append(" /case (select sum(prepayment) from Import_Saledetail sd where sd.SALESNO=Import_Saledetail.SALESNO) "); strSql.Append(" when 0 then 1 else (select sum(prepayment) from Import_Saledetail sd where sd.SALESNO=Import_Saledetail.SALESNO) end "); strSql.Append(" where SALESNO=( select SALESNO from SalesAdvancePay_state where gid='" + GID + "') "); var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } #endregion #region 将费用设为结算/未结算 static public int FeeSettlement(string BILLNO) { var strSql = new StringBuilder(); strSql.Append(" update ch_fee set settlement=amount,FEESTATUS=9 where gid in(select feeid from ch_fee_do where billno='" + BILLNO + "') "); var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } static public int FeeUnSettlement ( string BILLNO ) { var strSql = new StringBuilder(); strSql.Append(" update ch_fee set settlement=0,FEESTATUS=0 where gid in(select feeid from ch_fee_do where billno='" + BILLNO + "') "); var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } #endregion #region 获取新的销售单编号 static public string GetSALESNO ( string BSNO ) { var ExistSALESNO=""; var _order=0; do { ExistSALESNO = ""; _order = _order + 1; var strCheck = new StringBuilder(); strCheck.Append(" select top 1 SALESNO ");//0未提交 strCheck.Append(" from import_SALES where SALESNO = '" + BSNO +"-"+_order.ToString()+ "'"); DataSet ds = new DataSet(); Database db_check = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db_check.ExecuteReader(CommandType.Text, strCheck.ToString())) { while (reader.Read()) { ExistSALESNO = Convert.ToString(reader["SALESNO"]); } reader.Close(); } } while (ExistSALESNO != ""); return BSNO + "-" + _order.ToString(); } #endregion #region 销售单准许出库 审核流程 // 更新销售单状态值salestatus和工作流运行表WorkFlowDo信息 // salestatus //List chfeeList /* internal static DBResult SalesOUTAudit ( int newStatus, string WorkFlowName, string USERID ) { 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 == 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 == 8 || newStatus == 9) { result.Message = "只有在‘审核通过’状态下才能进行此操作"; } else if (newStatus == 1) { result.Message = "只有未经审核的单据才能撤回"; } return result; } } Database db = DatabaseFactory.CreateDatabase(); var updateSql = " update OA_Baoxiao set BXStatus=@BXStatus where BXGID=@BXGID "; if (newStatus == 0) { 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); } } 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 "; 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); } 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 #region 认款用销售单列表 static public List GetSalesStateDataList ( string strCondition, string userid = "", string usercode = "", string orgcode = "", string sort = null ) { var rangstr = GetRangDAListStr("", 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 dbo.trimdate(salesdate) salesdate,a.GID,a.SALESNO,a.DOAMOUNT,a.AD_BILLNO,a.PASSED "); strSql.Append(" ,case PASSED when 0 then '未通过' else '通过' end PASSED_REF "); strSql.Append(" ,a.CREATEUSER,(select showname from VW_user where userid=a.CREATEUSER) CREATEUSER_REF,dbo.trimdate(CREATETIME) CREATETIME "); strSql.Append(" ,AUDITOR,(select showname from VW_user where userid=a.AUDITOR) AUDITOR_REF,dbo.trimdate(AUDITTIME) AUDITTIME "); strSql.Append(" ,CUSTOMERNAME,OP,SALER,CORPID,DEPTGID,dbo.F_Sale_CargoName(s.SALESNO) CARGONAME,REMARK,CURRENCY,EXCHANGERATE,AMOUNT,AMOUNTRMB "); strSql.Append(" ,dbo.trimdate(DELIVERYDATE) DELIVERYDATE,DELIVERYADDRESS,DELIVERYCONTACTS,DELIVERYTEL,BSSTATUS,FEESTATUS,convert(bigint,TIMEMARK) TIMEMARK,mainstate"); strSql.Append(" ,(select showname from VW_user where userid=s.op) OPRef "); strSql.Append(" ,(select showname from VW_user where userid=s.saler) salerRef "); strSql.Append(" ,(case s.bsstatus when 1 then '锁定' else '未锁定' end) bsstatusRef "); strSql.Append(" ,(case s.feestatus when 1 then '锁定' else '未锁定' end) feestatusRef "); strSql.Append(" ,isnull((select sum(weight) from Import_DeliveryDetail where SALEDETAIL_GID in(select gid from Import_Saledetail where SALESNO=s.SALESNO)),0) DELIVERYWEIGHT "); strSql.Append(" ,Mainstate,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=31 and EnumValueID=s.MainState) as MainstateRef "); strSql.Append(" ,SALESTATUS,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=32 and EnumValueID=s.SALESTATUS) as SALESTATUSRef "); strSql.Append(" ,(select sum(doamount) from SalesAdvancePay_state where salesno=s.salesno ) Forecast_Amount "); strSql.Append(" ,s.amountRMB-(select sum(doamount) from SalesAdvancePay_state where salesno=s.salesno ) Forecast_Remain "); strSql.Append(" from SalesAdvancePay_state a left join Import_Sales s on s.SALESNO=a.SALESNO where 1=1 "); 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 SALESNO DESC"); } return SetSalesStateData(strSql); } private static List SetSalesStateData ( StringBuilder strSql ) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { SalesStateAuditmb data = new SalesStateAuditmb(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.SALESNO = Convert.ToString(reader["SALESNO"]); data.DOAMOUNT = Convert.ToString(reader["DOAMOUNT"]); data.AD_BILLNO = Convert.ToString(reader["AD_BILLNO"]); data.PASSED = Convert.ToString(reader["PASSED"]); data.PASSED_REF = Convert.ToString(reader["PASSED_REF"]); data.CREATEUSER = Convert.ToString(reader["CREATEUSER"]); data.CREATEUSER_REF = Convert.ToString(reader["CREATEUSER_REF"]); data.CREATETIME = Convert.ToString(reader["CREATETIME"]); data.AUDITOR_REF = Convert.ToString(reader["AUDITOR_REF"]); data.AUDITOR = Convert.ToString(reader["AUDITOR"]); data.AUDITTIME = Convert.ToString(reader["AUDITTIME"]); //////////// data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]); data.OP = Convert.ToString(reader["OP"]); data.SALER = Convert.ToString(reader["SALER"]); data.CORPID = Convert.ToString(reader["CORPID"]); data.DEPTGID = Convert.ToString(reader["DEPTGID"]); data.CARGONAME = Convert.ToString(reader["CARGONAME"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.CURRENCY = Convert.ToString(reader["CURRENCY"]); data.EXCHANGERATE = Convert.ToString(reader["EXCHANGERATE"]); data.AMOUNT = Convert.ToString(reader["AMOUNT"]); data.AMOUNTRMB = Convert.ToString(reader["AMOUNTRMB"]); data.SALESDATE = Convert.ToString(reader["SALESDATE"]); data.DELIVERYDATE = Convert.ToString(reader["DELIVERYDATE"]); data.DELIVERYADDRESS = Convert.ToString(reader["DELIVERYADDRESS"]); data.DELIVERYCONTACTS = Convert.ToString(reader["DELIVERYCONTACTS"]); data.DELIVERYTEL = Convert.ToString(reader["DELIVERYTEL"]); data.BSSTATUS = Convert.ToString(reader["BSSTATUS"]); data.FEESTATUS = Convert.ToString(reader["FEESTATUS"]); data.TIMEMARK = Convert.ToString(reader["TIMEMARK"]); data.OPRef = Convert.ToString(reader["OPRef"]); data.SALERRef = Convert.ToString(reader["SALERRef"]); data.BSSTATUSRef = Convert.ToString(reader["BSSTATUSRef"]); data.FEESTATUSRef = Convert.ToString(reader["FEESTATUSRef"]); data.DELIVERYWEIGHT = Convert.ToString(reader["DELIVERYWEIGHT"]); data.MAINSTATE = Convert.ToString(reader["MAINSTATE"]); data.SALESTATUS = Convert.ToString(reader["SALESTATUS"]); data.MAINSTATERef = Convert.ToString(reader["MAINSTATERef"]); data.SALESTATUSRef = Convert.ToString(reader["SALESTATUSRef"]); data.Forecast_Amount = Convert.ToString(reader["Forecast_Amount"]); data.Forecast_Remain = Convert.ToString(reader["Forecast_Remain"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 已删除的认款列表 static public List GetSalesAdvancePay_state_DELList ( string strCondition, string userid = "", string usercode = "", string orgcode = "", string sort = null ) { var strSql = new StringBuilder(); strSql.Append(" select GID,SALESNO,DOAMOUNT,PASSED,AD_BILLNO "); strSql.Append(" ,(select showname from vw_user where userid=CREATEUSER) CREATEUSER,CREATETIME "); strSql.Append(" ,(select showname from vw_user where userid=DELETEUSER) DELETEUSER,DELETETIME from SalesAdvancePay_state_DEL "); 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 SALESNO DESC"); } return SetSalesAdvancePay_state_DELData(strSql); } private static List SetSalesAdvancePay_state_DELData ( StringBuilder strSql ) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { SalesAdvancePay_state_DELmb data = new SalesAdvancePay_state_DELmb(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.SALESNO = Convert.ToString(reader["SALESNO"]); data.DOAMOUNT = Convert.ToString(reader["DOAMOUNT"]); data.PASSED = Convert.ToString(reader["PASSED"]); data.AD_BILLNO = Convert.ToString(reader["AD_BILLNO"]); data.CREATEUSER = Convert.ToString(reader["CREATEUSER"]); data.CREATETIME = Convert.ToString(reader["CREATETIME"]); data.DELETEUSER = Convert.ToString(reader["DELETEUSER"]); data.DELETETIME = Convert.ToString(reader["DELETETIME"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 根据语句获取值 public static string GetField ( string strSql ) { var billNoList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (DbConnection connection = db.CreateConnection()) //using (SqlTransaction idbTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { connection.Open(); try { var objCargo = db.ExecuteScalar(CommandType.Text, strSql); var _r = Convert.ToString(objCargo); if (!string.IsNullOrEmpty(_r)) { return _r; }else{ return "0"; } } catch (Exception exception) { return ""; } } } #endregion #region 销售单内的账单信息 static public List GetOrderDataList(string strCondition, string userid = "", string username = "", string companyid = "") { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append("GId,BsNo,FeeStatus,"); strSql.Append("(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99024 and EnumValueID=ch_fee_order.FeeStatus) as FeeStatus_Ref"); strSql.Append(",FeeType,"); strSql.Append("(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99020 and EnumValueID=ch_fee_order.FeeType) as FeeType_Ref"); strSql.Append(",FeeName,"); strSql.Append("FeeDescription,CustomerType,CustomerName,"); strSql.Append("Unit,UnitPrice,TaxUnitPrice,Quantity,TaxRate,NoTaxAmount,Amount,AccTaxRate,Currency,ExChangerate,Reason"); strSql.Append(",Remark,Commissionrate,Settlement,Invoice,OrderAmount,OrderInvoice,SubmitDate"); strSql.Append(",Auditoperator,AuditDate,EnteroPerator,EnterDate,DebitNo,IsDebit,IsOpen"); strSql.Append(",(select ShowName from [user] where GID=ch_fee_order.EnteroPerator) as OpName"); strSql.Append(",IsAdvancedpay,Sort,IsInvoice,FeeFrt,IsCrmOrderFee,AuditStatus,InvoiceNum"); strSql.Append(",ChequeNum,WmsOutBsNo,LineNum"); strSql.Append(",(select top 1 showname from [user] where gid=MODIFIEDUSER) MODIFIEDUSER,dbo.trimtime( MODIFIEDTIME) MODIFIEDTIME,0 UNINVOICE,0 PREAMOUNT,'' INPUTMODE "); strSql.Append(",'' Auditoperatorref,Amount,cargo_gid,(select name from Import_cargo where gid=cargo_gid )cargo_name,BXGID "); strSql.Append(" from ch_fee_order "); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } strSql.Append(" order by sort,LineNum "); return SetOrderData(strSql); } private static List SetOrderData ( StringBuilder strSql ) { var bodyList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { MsOrderFee_Import data = new MsOrderFee_Import(); #region Set DB data to Object data.GId = Convert.ToString(reader["GId"]); data.BsNo = Convert.ToString(reader["BsNo"]); if (reader["FeeStatus"] != DBNull.Value) data.FeeStatus = Convert.ToInt16(reader["FeeStatus"]); data.FeeStatus_Ref = Convert.ToString(reader["FeeStatus_Ref"]); if (reader["FeeType"] != DBNull.Value) 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"]); if (reader["UnitPrice"] != DBNull.Value) data.UnitPrice = Convert.ToDecimal(reader["UnitPrice"]); data.Currency = Convert.ToString(reader["Currency"]); if (reader["ExChangerate"] != DBNull.Value) data.ExChangerate = Convert.ToDecimal(reader["ExChangerate"]); data.Reason = Convert.ToString(reader["Reason"]); data.Remark = Convert.ToString(reader["Remark"]); if (reader["Commissionrate"] != DBNull.Value) data.Commissionrate = Convert.ToDecimal(reader["Commissionrate"]); if (reader["Settlement"] != DBNull.Value) data.Settlement = Convert.ToDecimal(reader["Settlement"]); if (reader["Invoice"] != DBNull.Value) data.Invoice = Convert.ToDecimal(reader["Invoice"]); if (reader["OrderAmount"] != DBNull.Value) data.OrderAmount = Convert.ToDecimal(reader["OrderAmount"]); if (reader["OrderInvoice"] != DBNull.Value) data.OrderInvoice = Convert.ToDecimal(reader["OrderInvoice"]); if (reader["SubmitDate"] != DBNull.Value) data.SubmitDate = Convert.ToDateTime(reader["SubmitDate"]); data.Auditoperator = Convert.ToString(reader["Auditoperatorref"]); if (reader["AuditDate"] != DBNull.Value) data.AuditDate = Convert.ToDateTime(reader["AuditDate"]); data.EnteroPerator = Convert.ToString(reader["EnteroPerator"]); data.OpName = Convert.ToString(reader["OpName"]); if (reader["EnterDate"] != DBNull.Value) data.EnterDate = Convert.ToDateTime(reader["EnterDate"]); data.DebitNo = Convert.ToString(reader["DebitNo"]); data.IsDebit = Convert.ToString(reader["IsDebit"]); if (Convert.ToBoolean(reader["IsOpen"]) == true) data.IsOpen = "1"; if (Convert.ToBoolean(reader["IsAdvancedpay"]) == true) data.IsAdvancedpay = "1"; if (reader["Sort"] != DBNull.Value) data.Sort = Convert.ToString(reader["Sort"]); if (reader["IsInvoice"] != DBNull.Value) if (Convert.ToBoolean(reader["IsInvoice"]) == true) data.IsInvoice = "1"; data.FeeFrt = Convert.ToString(reader["FeeFrt"]); if (reader["IsCrmOrderFee"] != DBNull.Value) data.IsCrmOrderFee = Convert.ToString(reader["IsCrmOrderFee"]); if (reader["AuditStatus"] != DBNull.Value) 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.MODIFIEDUSER = Convert.ToString(reader["MODIFIEDUSER"]); if (reader["MODIFIEDTIME"] != DBNull.Value) data.MODIFIEDTIME = Convert.ToString(reader["MODIFIEDTIME"]); data.Amount = Convert.ToDecimal(reader["Amount"]); data.CARGO_GID = Convert.ToString(reader["cargo_gid"]); data.CARGO_NAME = Convert.ToString(reader["cargo_name"]); data.BXGID = Convert.ToString(reader["BXGID"]); #endregion bodyList.Add(data); } reader.Close(); } return bodyList; } #endregion } }