dengyu
/
DS7
1
0
Fork 0
Code Issues Pull Requests Projects 1 Releases Wiki Activity
You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
DS7_HYRD
ShenZhenCHJY
DS7_JinGang
dev
QDHHYT
master
临时发布20240103
DS7_JingHan
DS7Zhanhao
HuanHaiYunTong
shenzhenchjy
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'DS7Zhanhao'
${ noResults }
DS7/DSWeb/Import/DAL/ImportSales/ImportSalesDAL.cs

2070 lines
101 KiB
C#
Raw Permalink Normal View History Unescape Escape

1
2 years ago
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<ImportSalesmb> 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<ImportSalesmb> 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<ImportSalesmb> SetData ( StringBuilder strSql )
{
var headList = new List<ImportSalesmb>();
/*
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<ImportCargomb> 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<ImportCargomb> SetCargoData(StringBuilder strSql)
{
var headList = new List<ImportCargomb>();
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<Import_SaleZDmb> 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<Import_SaleZDmb> SetZDData(StringBuilder strSql)
{
var headList = new List<Import_SaleZDmb>();
/*
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<Import_SaleZDDetailmb> 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<Import_SaleZDDetailmb> SetZDDetailData(StringBuilder strSql)
{
var headList = new List<Import_SaleZDDetailmb>();
/*
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<ImportSaledetailmb> 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<ImportSaledetailmb> SetDetailData(StringBuilder strSql)
{
var headList = new List<ImportSaledetailmb>();
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<Deliverymb> 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<Deliverymb> SetDeliveryData ( StringBuilder strSql )
{
var headList = new List<Deliverymb>();
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<DeliveryDetailmb> 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<DeliveryDetailmb> SetDeliveryDetailData ( StringBuilder strSql )
{
var headList = new List<DeliveryDetailmb>();
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<MsChFee_Sales> 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<MsChFee_Sales> SetFeeData(StringBuilder strSql)
{
var bodyList = new List<MsChFee_Sales>();
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<C_Mmb> 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<SalesStateAuditmb> 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<SalesStateAuditmb> SetSalesStateData ( StringBuilder strSql )
{
var headList = new List<SalesStateAuditmb>();
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<SalesAdvancePay_state_DELmb> 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<SalesAdvancePay_state_DELmb> SetSalesAdvancePay_state_DELData ( StringBuilder strSql )
{
var headList = new List<SalesAdvancePay_state_DELmb>();
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<string>();
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<MsOrderFee_Import> 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<MsOrderFee_Import> SetOrderData ( StringBuilder strSql )
{
var bodyList = new List<MsOrderFee_Import>();
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
}
}