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 'master'
${ noResults }
DS7/DSWeb/Areas/MvcShipping/DAL/RptWms/RptWmsSettlementListDAL.cs

2967 lines
218 KiB
C#
Raw Permalink Blame History Unescape Escape

This file contains ambiguous Unicode characters!

This file contains ambiguous Unicode characters that may be confused with others in your current locale. If your use case is intentional and legitimate, you can safely ignore this warning. Use the Escape button to highlight these characters.

using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using DSWeb.MvcShipping.Models.RptWmsSettlementListEntity;
using DSWeb.MvcShipping.Models.RptWmsSettlementListViewEntity;
using Microsoft.Practices.EnterpriseLibrary.Data;
using DSWeb.Areas.CommMng.Models;
using DSWeb.EntityDA;
using HcUtility.Comm;
using System.Data.SqlClient;
using HcUtility.Core;
using DSWeb.DataAccess;
using DSWeb.Models;
using DSWeb.MvcShipping.Helper;
using DSWeb.Areas.CommMng.DAL;
namespace DSWeb.MvcShipping.DAL.RptWmsSettlementListDAL
{
public class RptWmsSettlementListDAL
{
#region Inquery 主表 wms_settlement_list
static public List<RptWmsSettlementListEntity> GetDataList(string strCondition, string strUserID, string strCompanyID, string usercode, string orgcode, string sort = null)
{
var strSql = new StringBuilder();
strSql.Append("SELECT GID,(case when ISLOCK=1 then '是' else '' end) as ISLOCK,DUINO,CUSTOMERNAME,CONVERT(char(10), STARTBILLINGDATE, 20) as STARTBILLINGDATE,CONVERT(char(10), ENDBILLINGDATE, 20) as ENDBILLINGDATE,REMARK,CORPID,CREATEUSER,CREATETIME,ISMAKEOUT,MAKEOUTUSER,MAKEOUTTIME,(select top 1 SHOWNAME from [user] where gid=wms_settlement_list.CREATEUSER) as CREATEUSER_NAME,(select top 1 SHOWNAME from [user] where gid=wms_settlement_list.MAKEOUTUSER) as MAKEOUTUSER_NAME from wms_settlement_list where CORPID='" + strCompanyID + "' and ISDELETE=0");//CREATEUSER='" + strUserID + "' and
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 DUINO desc,CREATETIME desc");
}
return SetData(strSql);
}
private static List<RptWmsSettlementListEntity> SetData(StringBuilder strSql)
{
var headList = new List<RptWmsSettlementListEntity>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
RptWmsSettlementListEntity data = new RptWmsSettlementListEntity();
#region Set DB data to Object
data.GID = Convert.ToString(reader["GID"]);//惟一编号
data.ISLOCK = Convert.ToString(reader["ISLOCK"]);//是否对账
data.DUINO = Convert.ToString(reader["DUINO"]);//对账编号
data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]);//对账客户
data.STARTBILLINGDATE = Convert.ToString(reader["STARTBILLINGDATE"]);//开始计费日期
data.ENDBILLINGDATE = Convert.ToString(reader["ENDBILLINGDATE"]);//结束计费日期
data.REMARK = Convert.ToString(reader["REMARK"]);//备注
data.MAKEOUTUSER = Convert.ToString(reader["MAKEOUTUSER_NAME"]);//重新生成人
if (reader["MAKEOUTTIME"] != DBNull.Value)
data.MAKEOUTTIME = Convert.ToDateTime(reader["MAKEOUTTIME"]);//重新生成时间
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
#endregion
#region Inquery 仓储结算清单明细临时表([wms_settlement_detail_temp])
static public List<RptWmsSettlementDetailEntity> GetFeeDataList(string strCondition, string strCondition2, string strCondition3, string strCondition4,string strSTARTBILLINGDATE, string strENDBILLINGDATE, string strUserID, string strShowName, string strCompanyID, string orgcode, string sort)
{
string TableName_ls = "";//临时库存表名
#region 按计费日期计算仓储费用插入临时表
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
{
#region 基础变量设置
int iState = 0;
int result = 0;
TableName_ls = "temp_kc_" + Guid.NewGuid().ToString().Replace("-","");
//
if (!string.IsNullOrEmpty(strCondition))
{
strCondition = " and " + strCondition;
}
if (!string.IsNullOrEmpty(strCondition2))
{
strCondition2 = " and " + strCondition2;
}
if (!string.IsNullOrEmpty(strCondition3))
{
strCondition3 = " and " + strCondition3;
}
if (!string.IsNullOrEmpty(strCondition4))
{
strCondition4 = " and " + strCondition4;
}
#endregion
try
{
#region 清理临时表
String strSql = "delete from [wms_settlement_detail_rate_detail_temp] where CREATEUSER='" + strUserID + "'";
iState = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
if (iState < 0)
{
sqlTran.Rollback();
result = -3;
}
//
strSql = "delete from [wms_settlement_detail_temp] where CREATEUSER='" + strUserID + "' and CORPID='" + strCompanyID + "'";
iState = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
if (iState < 0)
{
sqlTran.Rollback();
result = -3;
}
#endregion
#region 5.生成临时【库存】表，表名 TableName_ls
strSql = "select * into [" + TableName_ls + "] from (select GID, WMSNO, WMSDATE, STARTBILLINGDATE, CUSTOMERNAME, BLNO, STORAGENAME, AREANAME, GOODSNAME, GOODSMODEL, ASSOCIATEDNO, CONTRACTNO, CHARGEUNIT, CUSTOMNO, GOODSGRADE, ISNULL(GOODSSLICE, 0) AS GOODSSLICE, GOODSSTANDARD, '' AS REMARK"
+ ", ISNULL(sum(GOODSRKSL), 0) AS hj_GOODSRKSL"//入库量
+ ", ISNULL(SUM(GOODSPFSL), 0) AS hj_GOODSPFSL_OUT"//出库量
+ ", ISNULL(SUM(GOODSPFSL_MONTH), 0) AS hj_GOODSPFSL_MONTH"//出库量_区间
+ ", ISNULL((ISNULL(sum(GOODSRKSL), 0)-ISNULL(SUM(GOODSPFSL), 0)),0) AS hj_GOODSSTOCK"//入库量-出库量=库存量
+ ", ISNULL(sum(GOODSPACK), 0) AS hj_GOODSPACK"//入库件数
+ ", ISNULL(SUM(GOODSPACKPFSL), 0) AS hj_GOODSPACKPFSL"//出库件数
+ ", ISNULL(SUM(GOODSPACKPFSL_MONTH), 0) AS hj_GOODSPACKPFSL_MONTH"//出库件数_区间
+ ", ISNULL((ISNULL(sum(GOODSPACK), 0)-ISNULL(SUM(GOODSPACKPFSL), 0)),0) AS hj_GOODSPACKSTOCK"//入库件数-出库件数=剩余件数
+ ", ISNULL(sum(GOODSPACKACTUAL), 0) AS hj_GOODSPACKACTUAL"//实际件数
+ ", ISNULL(sum(GOODSRKSLACTUAL), 0) AS hj_GOODSRKSLACTUAL"//实际数量
+ ", '" + strUserID + "' as CREATEUSER, '" + strCompanyID + "' as CORPID"
+ " from ("
+ "SELECT GID, WMSNO, CONVERT(char(10), WMSDATE, 120) AS WMSDATE, STARTBILLINGDATE, CUSTOMERNAME, BLNO, STORAGENAME, AREANAME, GOODSNAME, GOODSMODEL, ASSOCIATEDNO, CONTRACTNO, CHARGEUNIT, CUSTOMNO, GOODSSTANDARD, '' AS REMARK, '' as GOODSGRADE, 0 as GOODSSLICE"
+ ", GOODSRKSL"
+ ", isnull((select sum(GOODSPFSL) as GOODSPFSL from wms_out_detail where INBSNO=wms.GID " + strCondition4 + "),0) as GOODSPFSL"
+ ", isnull((select sum(GOODSPFSL) as GOODSPFSL from wms_out_detail where INBSNO=wms.GID " + strCondition2 + "),0) as GOODSPFSL_MONTH"
+ ", GOODSPACK"
+ ", isnull((select sum(GOODSPACKPFSL) as GOODSPACKPFSL from wms_out_detail where INBSNO=wms.GID " + strCondition4 + "),0) as GOODSPACKPFSL"
+ ", isnull((select sum(GOODSPACKPFSL) as GOODSPACKPFSL from wms_out_detail where INBSNO=wms.GID " + strCondition2 + "),0) as GOODSPACKPFSL_MONTH"
+ ", GOODSPACKACTUAL, GOODSRKSLACTUAL, CORPID "
+ " FROM wms WHERE CHARGEUNIT <> 'CBM' AND ISDELETE = 0 " + strCondition
+ ") as A where 1=1 " + strCondition3
+ " GROUP BY GID, WMSNO, CUSTOMERNAME, STORAGENAME, AREANAME, BLNO, GOODSNAME, GOODSMODEL, ASSOCIATEDNO, CONTRACTNO, CHARGEUNIT, CUSTOMNO, GOODSGRADE, GOODSSLICE, GOODSSTANDARD, GOODSRKSL, GOODSPACK, GOODSPACKACTUAL, GOODSRKSLACTUAL, WMSDATE, STARTBILLINGDATE"
+ " UNION ALL "
+ "select GID, WMSNO, WMSDATE, STARTBILLINGDATE, CUSTOMERNAME, BLNO, STORAGENAME, AREANAME, GOODSNAME, GOODSMODEL, ASSOCIATEDNO, CONTRACTNO, CHARGEUNIT, CUSTOMNO, GOODSGRADE, ISNULL(GOODSSLICE, 0) AS GOODSSLICE, GOODSSTANDARD, '' AS REMARK"
+ ", ISNULL(sum(GOODSRKSL), 0) AS hj_GOODSRKSL"//入库量
+ ", ISNULL(SUM(GOODSPFSL), 0) AS hj_GOODSPFSL_OUT"//出库量
+ ", ISNULL(SUM(GOODSPFSL_MONTH), 0) AS hj_GOODSPFSL_MONTH"//出库量_区间
+ ", ISNULL((ISNULL(sum(GOODSRKSL), 0)-ISNULL(SUM(GOODSPFSL), 0)),0) AS hj_GOODSSTOCK"//入库量-出库量=库存量
+ ", ISNULL(sum(GOODSPACK), 0) AS hj_GOODSPACK"//入库件数
+ ", ISNULL(SUM(GOODSPACKPFSL), 0) AS hj_GOODSPACKPFSL"//出库件数
+ ", ISNULL(SUM(GOODSPACKPFSL_MONTH), 0) AS hj_GOODSPACKPFSL_MONTH"//出库件数_区间
+ ", ISNULL((ISNULL(sum(GOODSPACK), 0)-ISNULL(SUM(GOODSPACKPFSL), 0)),0) AS hj_GOODSPACKSTOCK"//入库件数-出库件数=剩余件数
+ ", ISNULL(sum(GOODSPACKACTUAL), 0) AS hj_GOODSPACKACTUAL"//实际件数
+ ", ISNULL(sum(GOODSRKSLACTUAL), 0) AS hj_GOODSRKSLACTUAL"//实际数量
+ ", '" + strUserID + "' as CREATEUSER, '" + strCompanyID + "' as CORPID"
+ " from ("
+ "SELECT i.GID,isnull(i.BSNO,w.WMSNO) as WMSNO, CONVERT(char(10), w.WMSDATE, 120) AS WMSDATE, w.STARTBILLINGDATE"
+ ", w.CUSTOMERNAME, w.BLNO, w.STORAGENAME, w.AREANAME, w.GOODSNAME, i.GOODSMODEL, w.ASSOCIATEDNO, w.CONTRACTNO, w.CHARGEUNIT, w.CUSTOMNO, i.GOODSGRADE, i.GOODSSLICE, i.GOODSSTANDARD, '' as REMARK"
+ ", i.GOODSRKSL"
+ ", isnull((select sum(GOODSPFSL) as GOODSPFSL from wms_out_detail where INBSNO=i.GID " + strCondition4 + "),0) as GOODSPFSL"
+ ", isnull((select sum(GOODSPFSL) as GOODSPFSL from wms_out_detail where INBSNO=i.GID " + strCondition2 + "),0) as GOODSPFSL_MONTH"
+ ", i.GOODSPACK"
+ ", isnull((select sum(GOODSPACKPFSL) as GOODSPACKPFSL from wms_out_detail where INBSNO=i.GID " + strCondition4 + "),0) as GOODSPACKPFSL"
+ ", isnull((select sum(GOODSPACKPFSL) as GOODSPACKPFSL from wms_out_detail where INBSNO=i.GID " + strCondition2 + "),0) as GOODSPACKPFSL_MONTH"
+ ", i.GOODSPACKACTUAL, i.GOODSRKSLACTUAL, w.CORPID "
+ " FROM wms_in AS i LEFT OUTER JOIN wms AS w ON i.ASSOCIATEDNO = w.GID WHERE i.CHARGEUNIT = 'CBM' AND i.ISDELETE = 0 " + strCondition
+ ") as A where 1=1 " + strCondition3
+ " GROUP BY GID, WMSNO, CUSTOMERNAME, STORAGENAME, AREANAME, BLNO, GOODSNAME, GOODSMODEL, ASSOCIATEDNO, CONTRACTNO, CHARGEUNIT, CUSTOMNO, GOODSGRADE, GOODSSLICE, GOODSSTANDARD, GOODSRKSL, GOODSPACK, GOODSPACKACTUAL, GOODSRKSLACTUAL, WMSDATE, STARTBILLINGDATE) as a";
iState = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
if (iState < 0)
{
sqlTran.Rollback();
result = -10;
}
#endregion
#region 5.从【库存】中提取数据，生成【仓储明细表[wms_settlement_detail_rate_detail_temp]】
strSql = "select * from [" + TableName_ls + "] where CREATEUSER='" + strUserID + "' and CORPID='" + strCompanyID + "'";
DataSet dsKC = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, strSql, null);
if (dsKC != null)
{
foreach (DataRow dr in dsKC.Tables[0].Rows)
{
#region 计算应收仓储费
//判断已经生成的日期
string strSTARTBILLINGDATE_ls = strSTARTBILLINGDATE;
string strWSD = "select top 1 * from wms_settlement_date where LINKGID='" + dr["GID"].ToString().Trim() + "' and TABLENAME='wms' and ISDELETE=0 order by ENDBILLINGDATE desc";
DataSet dsWSD = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, strWSD, null);
if (dsWSD != null)
{
if (dsWSD.Tables[0].Rows.Count > 0)
{
if (DateTime.Parse(dsWSD.Tables[0].Rows[0]["ENDBILLINGDATE"].ToString().Trim()) > DateTime.Parse(strSTARTBILLINGDATE))
{
strSTARTBILLINGDATE_ls = DateTime.Parse(dsWSD.Tables[0].Rows[0]["ENDBILLINGDATE"].ToString().Trim()).AddDays(1).ToString().Trim();
}
}
else
{
//根据查询条件提取【日期】
strSTARTBILLINGDATE_ls = dr["STARTBILLINGDATE"].ToString().Trim();
}
}
else
{
//根据查询条件提取【日期】
strSTARTBILLINGDATE_ls = dr["STARTBILLINGDATE"].ToString().Trim();
}
//如果开始日期小于等于结束日期，则允许生成费用并统计
if (DateTime.Parse(strSTARTBILLINGDATE_ls) <= DateTime.Parse(strENDBILLINGDATE))
{
//计算应收仓储费
decimal outnums = decimal.Parse(dr["hj_GOODSSTOCK"].ToString().Trim());//库存量
getWmsRate(sqlTran, strSTARTBILLINGDATE_ls.Trim(), strENDBILLINGDATE.Trim(), dr["GID"].ToString().Trim(), dr["CUSTOMERNAME"].ToString().Trim(), outnums, dr["GID"].ToString().Trim(), 1, strUserID, strShowName, "现有库存计算应收仓储费", true, true);
//string getstr = getWmsRate(sqlTran, strSTARTBILLINGDATE_ls.Trim(), strENDBILLINGDATE.Trim(), dr["GID"].ToString().Trim(), dr["CUSTOMERNAME"].ToString().Trim(), outnums, dr["GID"].ToString().Trim(), 1, strUserID, strShowName, "现有库存计算应收仓储费", true, true);
}
#endregion
}
}
#endregion
#region 5.暂停使用，从【库存】中提取数据，关联【仓储明细表[wms_settlement_detail_rate_detail_temp]】，插入【仓储结算明细临时表[wms_settlement_detail_temp]】
//if (dsKC != null)
//{
// foreach (DataRow dr in dsKC.Tables[0].Rows)
// {
// //库存不为空
// if ( Decimal.Parse(dr["hj_GOODSSTOCK"].ToString().Trim()) != 0)
// {
// strSql = "insert into [wms_settlement_detail_temp]([GID],[BLNO],[CUSTOMNO],[CONTRACTNO],[WMSNO],[OUTBSNO],[CUSTOMERNAME],[STORAGENAME],[GOODSNAME],[GOODSMODEL],[GOODSSTANDARD],[GOODSGRADE],[WMSDATE],[DODATE],[WMSDAYS],[STARTBILLINGDATE_OUT],[ENDBILLINGDATE_OUT],[GOODSOUTFEE],[GOODSPACK],[GOODSPACKACTUAL],[GOODSPACKPFSL],[GOODSPACKSTOCK],[GOODSRKSL],[GOODSRKSLACTUAL],[GOODSPFSL],[GOODSSTOCK],[CHARGEUNIT],[REMARK_OUT],[FEETYPE],[FEEGRADE],[FEESCALE],[FEEPRICE],[STARTBILLINGDATE],[ENDBILLINGDATE],[FEEDAYS],[OUTFEE],[ARFEE],[CREATEUSER],[CREATETIME],[ISMAKEOUT],[MAKEOUTUSER],[MAKEOUTTIME],[CORPID],[LINENUM],[GID_LINK]) "
// + " select newid() as GID,'" + dr["BLNO"].ToString().Trim() + "','" + dr["CUSTOMNO"].ToString().Trim() + "','" + dr["CONTRACTNO"].ToString().Trim() + "','" + dr["WMSNO"].ToString().Trim() + "','','" + dr["CUSTOMERNAME"].ToString().Trim() + "','" + dr["STORAGENAME"].ToString().Trim() + "','" + dr["GOODSNAME"].ToString().Trim() + "','" + dr["GOODSMODEL"].ToString().Trim() + "','" + dr["GOODSSTANDARD"].ToString().Trim() + "','" + dr["GOODSGRADE"].ToString().Trim() + "','" + dr["WMSDATE"].ToString().Trim() + "',CONVERT(char(10), getdate(), 120),(DATEDIFF(DAY, '" + dr["WMSDATE"].ToString().Trim() + "', CONVERT(char(10), getdate(), 20)) + 1),'" + dr["STARTBILLINGDATE"].ToString().Trim() + "',CONVERT(char(10), getdate(), 120),0," + dr["hj_GOODSPACK"].ToString().Trim() + "," + dr["hj_GOODSPACKACTUAL"].ToString().Trim() + "," + dr["hj_GOODSPACKPFSL"].ToString().Trim() + "," + dr["hj_GOODSPACKSTOCK"].ToString().Trim() + "," + dr["hj_GOODSRKSL"].ToString().Trim() + "," + dr["hj_GOODSRKSLACTUAL"].ToString().Trim() + "," + dr["hj_GOODSPFSL_OUT"].ToString().Trim() + "," + dr["hj_GOODSSTOCK"].ToString().Trim() + ",'" + dr["CHARGEUNIT"].ToString().Trim() + "',''"
// + ",[FEETYPE],[FEEGRADE],[FEESCALE],[FEEPRICE],[STARTBILLINGDATE],[ENDBILLINGDATE],[FEESCALE],[OUTFEE],0"
// + ",'" + strUserID + "',getdate(),0,'" + strUserID + "',getdate(),'" + strCompanyID + "',5,'" + dr["GID"].ToString().Trim() + "'"
// + " from [wms_settlement_detail_rate_detail_temp] where ASSOCIATEDNO='" + dr["GID"].ToString().Trim() + "' and OUTFEE<>0 ORDER BY ASSOCIATEDNO,FEETYPE,FEEGRADE,CREATETIME";
// iState = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
// if (iState < 0)
// {
// sqlTran.Rollback();
// result = -4;
// }
// }
// }
//}
#endregion
#region 5.从【库存】中提取数据，关联【仓储明细表[wms_settlement_detail_rate_detail_temp]】，插入【仓储结算明细临时表[wms_settlement_detail_temp]】
strSql = "insert into [wms_settlement_detail_temp]([GID],[BLNO],[CUSTOMNO],[CONTRACTNO],[WMSNO],[OUTBSNO],[CUSTOMERNAME],[STORAGENAME],[GOODSNAME],[GOODSMODEL],[GOODSSTANDARD],[GOODSGRADE],[WMSDATE],[DODATE],[WMSDAYS],[STARTBILLINGDATE_OUT],[ENDBILLINGDATE_OUT],[GOODSOUTFEE],[GOODSPACK],[GOODSPACKACTUAL],[GOODSPACKPFSL],[GOODSPACKSTOCK],[GOODSRKSL],[GOODSRKSLACTUAL],[GOODSPFSL],[GOODSSTOCK],[CHARGEUNIT],[REMARK_OUT],[FEETYPE],[FEEGRADE],[FEESCALE],[FEEPRICE],[STARTBILLINGDATE],[ENDBILLINGDATE],[FEEDAYS],[OUTFEE],[ARFEE],[CREATEUSER],[CREATETIME],[ISMAKEOUT],[MAKEOUTUSER],[MAKEOUTTIME],[CORPID],[LINENUM],[GID_LINK]) "
+ " select newid() as GID,t.BLNO,t.CUSTOMNO,t.CONTRACTNO,t.WMSNO,'',t.CUSTOMERNAME,t.STORAGENAME,t.GOODSNAME,t.GOODSMODEL,t.GOODSSTANDARD,t.GOODSGRADE,t.WMSDATE,CONVERT(char(10), getdate(), 120),(DATEDIFF(DAY, t.WMSDATE, CONVERT(char(10), getdate(), 20)) + 1),t.STARTBILLINGDATE,CONVERT(char(10), getdate(), 120),0,t.hj_GOODSPACK,t.hj_GOODSPACKACTUAL,t.hj_GOODSPACKPFSL,t.hj_GOODSPACKSTOCK,t.hj_GOODSRKSL,t.hj_GOODSRKSLACTUAL,t.hj_GOODSPFSL_OUT,t.hj_GOODSSTOCK,t.CHARGEUNIT,''"
+ ",r.[FEETYPE],r.[FEEGRADE],r.[FEESCALE],r.[FEEPRICE],r.[STARTBILLINGDATE],r.[ENDBILLINGDATE],r.[FEESCALE],r.[OUTFEE],0"
+ ",'" + strUserID + "',getdate(),0,'" + strUserID + "',getdate(),'" + strCompanyID + "',5,t.GID"
+ " from [wms_settlement_detail_rate_detail_temp] as r INNER JOIN [" + TableName_ls + "] as t on r.ASSOCIATEDNO=t.GID where r.OUTFEE<>0 and t.hj_GOODSSTOCK<>0";
iState = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
if (iState < 0)
{
sqlTran.Rollback();
result = -4;
}
#endregion
#region 1.暂停使用，从【库存】中提取入库数据，插入【仓储结算明细临时表[wms_settlement_detail_temp]】
//if (dsKC != null)
//{
// foreach (DataRow dr in dsKC.Tables[0].Rows)
// {
// //入库与库存不相等
// if (Decimal.Parse(dr["hj_GOODSRKSL"].ToString().Trim()) != Decimal.Parse(dr["hj_GOODSSTOCK"].ToString().Trim()))
// {
// Decimal ls_GOODSSTOCK = Decimal.Parse(dr["hj_GOODSPFSL_MONTH"].ToString().Trim()) + Decimal.Parse(dr["hj_GOODSSTOCK"].ToString().Trim());
// strSql = "insert into [wms_settlement_detail_temp]([GID],[BLNO],[CUSTOMNO],[CONTRACTNO],[WMSNO],[CUSTOMERNAME],[STORAGENAME],[GOODSNAME],[GOODSMODEL],[GOODSSTANDARD],[GOODSGRADE],[WMSDATE],[STARTBILLINGDATE_OUT],[GOODSPACK],[GOODSPACKACTUAL],[GOODSRKSL],[GOODSRKSLACTUAL],[GOODSSTOCK],[CHARGEUNIT],[CREATEUSER],[CREATETIME],[ISMAKEOUT],[MAKEOUTUSER],[MAKEOUTTIME],[CORPID],[LINENUM],[GID_LINK]) "
// //+ " values(newid(),'" + dr["BLNO"].ToString().Trim() + "','" + dr["CUSTOMNO"].ToString().Trim() + "','" + dr["CONTRACTNO"].ToString().Trim() + "','" + dr["WMSNO"].ToString().Trim() + "','" + dr["CUSTOMERNAME"].ToString().Trim() + "','" + dr["STORAGENAME"].ToString().Trim() + "','" + dr["GOODSNAME"].ToString().Trim() + "','" + dr["GOODSMODEL"].ToString().Trim() + "','" + dr["GOODSSTANDARD"].ToString().Trim() + "','" + dr["GOODSGRADE"].ToString().Trim() + "','" + dr["WMSDATE"].ToString().Trim() + "','" + dr["STARTBILLINGDATE"].ToString().Trim() + "','" + dr["hj_GOODSPACK"].ToString().Trim() + "','" + dr["hj_GOODSPACKACTUAL"].ToString().Trim() + "','" + dr["hj_GOODSRKSL"].ToString().Trim() + "','" + dr["hj_GOODSRKSLACTUAL"].ToString().Trim() + "','" + ls_GOODSSTOCK + "','" + dr["CHARGEUNIT"].ToString().Trim() + "','" + strUserID + "',getdate(),0,'" + strUserID + "',getdate(),'" + strCompanyID + "',1,'" + dr["GID"].ToString().Trim() + "')";
// +" select newid() as GID,BLNO,CUSTOMNO,CONTRACTNO,WMSNO,CUSTOMERNAME,STORAGENAME,GOODSNAME,GOODSMODEL,GOODSSTANDARD,GOODSGRADE,WMSDATE,STARTBILLINGDATE,GOODSPACK,GOODSPACKACTUAL,GOODSRKSL,GOODSRKSLACTUAL,'" + ls_GOODSSTOCK + "' as GOODSSTOCK,CHARGEUNIT,'" + strUserID + "',getdate(),0,'" + strUserID + "',getdate(),'" + strCompanyID + "',1,GID"
// + " from VW_WMS_DETAIL_INNER"
// + " where 1=1 and NID not in (select LINKGID from wms_settlement_date where ISDELETE=0 and TABLENAME='wms_out_detail')"
// + " and WMS_OUT_GID not in (select isnull(WMSOUTBSNO,'') as WMSOUTBSNO from ch_fee)"
// + " and (gid in (select ASSOCIATEDNO from wms_settlement_detail_rate_detail_temp) or WMS_OUT_GID in (select ASSOCIATEDNO from wms_settlement_detail_rate_detail_temp))"
// + " and gid='" + dr["GID"].ToString().Trim() + "'"
// + strCondition + strCondition2 + strCondition3
// + " group by GID,BLNO,CUSTOMNO,CONTRACTNO,WMSNO,CUSTOMERNAME,STORAGENAME,GOODSNAME,GOODSMODEL,GOODSSTANDARD,GOODSGRADE,WMSDATE,STARTBILLINGDATE,GOODSPACK,GOODSPACKACTUAL,GOODSRKSL,GOODSRKSLACTUAL,CHARGEUNIT";
// iState = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
// if (iState < 0)
// {
// sqlTran.Rollback();
// result = -7;
// }
// }
// }
//}
#endregion
#region 2.从【出库明细视图VW_WMS_DETAIL_INNER】中提取出【货主=应收仓储客户】数据，生成【仓储明细表[wms_settlement_detail_rate_detail_temp]】
strSql = "select *,isChFee=isnull((select top 1 gid from ch_fee where WMSOUTBSNO=VW_WMS_DETAIL_INNER.WMS_OUT_GID and FEENAME='仓储费'),'0') from VW_WMS_DETAIL_INNER where CUSTOMERNAME=ARCLIENTWMSOUT and NID not in (select LINKGID from wms_settlement_date where ISDELETE=0 and TABLENAME='wms_out_detail')"
//+ " and WMS_OUT_GID not in (select isnull(WMSOUTBSNO,'') as WMSOUTBSNO from ch_fee) "
+ strCondition + strCondition2 + strCondition3;
DataSet ds = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, strSql, null);
if (ds != null)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
#region 计算应收仓储费
//判断已经生成的日期
string strSTARTBILLINGDATE_ls = strSTARTBILLINGDATE;
string strENDBILLINGDATE_ls = strENDBILLINGDATE;
//判断以前是否有已生成的出库
string strWSD = "select top 1 * from wms_settlement_date where LINKGID='" + dr["WMS_OUT_GID"].ToString().Trim() + "' and TABLENAME='wms_out' and ISDELETE=0 order by ENDBILLINGDATE desc";
DataSet dsWSD = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, strWSD, null);
if (dsWSD != null)
{
if (dsWSD.Tables[0].Rows.Count > 0)
{
//开始计费日期赋值
if (DateTime.Parse(dsWSD.Tables[0].Rows[0]["ENDBILLINGDATE"].ToString().Trim()) > DateTime.Parse(strSTARTBILLINGDATE))
{
strSTARTBILLINGDATE_ls = DateTime.Parse(dsWSD.Tables[0].Rows[0]["ENDBILLINGDATE"].ToString().Trim()).AddDays(1).ToString().Trim();
}
}
else
{
//判断以前是否有已生成的出库库存
string strWSD_IN = "select top 1 * from wms_settlement_date where LINKGID='" + dr["GID"].ToString().Trim() + "' and TABLENAME='wms' and ISDELETE=0 order by ENDBILLINGDATE desc";
DataSet dsWSD_IN = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, strWSD_IN, null);
if (dsWSD_IN != null)
{
if (dsWSD_IN.Tables[0].Rows.Count > 0)
{
if (DateTime.Parse(dsWSD_IN.Tables[0].Rows[0]["ENDBILLINGDATE"].ToString().Trim()) > DateTime.Parse(strSTARTBILLINGDATE_ls))
{
strSTARTBILLINGDATE_ls = DateTime.Parse(dsWSD_IN.Tables[0].Rows[0]["ENDBILLINGDATE"].ToString().Trim()).AddDays(1).ToString().Trim();
}
}
else
{
//开始计费日期赋值
strSTARTBILLINGDATE_ls = dr["STARTBILLINGDATE"].ToString().Trim();
}
}
else
{
//开始计费日期赋值
strSTARTBILLINGDATE_ls = dr["STARTBILLINGDATE"].ToString().Trim();
}
}
}
else
{
//判断以前是否有已生成的出库库存
string strWSD_IN = "select top 1 * from wms_settlement_date where LINKGID='" + dr["GID"].ToString().Trim() + "' and TABLENAME='wms' and ISDELETE=0 order by ENDBILLINGDATE desc";
DataSet dsWSD_IN = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, strWSD_IN, null);
if (dsWSD_IN != null)
{
if (dsWSD_IN.Tables[0].Rows.Count > 0)
{
if (DateTime.Parse(dsWSD_IN.Tables[0].Rows[0]["ENDBILLINGDATE"].ToString().Trim()) > DateTime.Parse(strSTARTBILLINGDATE_ls))
{
strSTARTBILLINGDATE_ls = DateTime.Parse(dsWSD_IN.Tables[0].Rows[0]["ENDBILLINGDATE"].ToString().Trim()).AddDays(1).ToString().Trim();
}
}
else
{
//开始计费日期赋值
strSTARTBILLINGDATE_ls = dr["STARTBILLINGDATE"].ToString().Trim();
}
}
else
{
//开始计费日期赋值
strSTARTBILLINGDATE_ls = dr["STARTBILLINGDATE"].ToString().Trim();
}
}
//结束计费日期赋值
if (DateTime.Parse(dr["ENDBILLINGDATE"].ToString().Trim()) < DateTime.Parse(strENDBILLINGDATE))
{
strENDBILLINGDATE_ls = dr["ENDBILLINGDATE"].ToString().Trim();
}
//如果开始日期小于等于结束日期，则允许生成费用并统计
if (DateTime.Parse(strSTARTBILLINGDATE_ls) <= DateTime.Parse(strENDBILLINGDATE_ls))
{
//计算应收仓储费
decimal outnums = decimal.Parse(dr["GOODSPFSL_OUT"].ToString().Trim());//出库量
getWmsRate(sqlTran, strSTARTBILLINGDATE_ls.Trim(), strENDBILLINGDATE_ls.Trim(), dr["GID"].ToString().Trim(), dr["CUSTOMERNAME"].ToString().Trim(), outnums, dr["GID_OUT"].ToString().Trim(), 1, strUserID, strShowName, "现有出库重新计算应收仓储费", true, true);
//string getstr = getWmsRate(sqlTran, strSTARTBILLINGDATE_ls.Trim(), strENDBILLINGDATE_ls.Trim(), dr["GID"].ToString().Trim(), dr["CUSTOMERNAME"].ToString().Trim(), outnums, dr["WMS_OUT_GID"].ToString().Trim(), 1, strUserID, strShowName, "现有出库重新计算应收仓储费", true, true);
}
#endregion
}
}
#endregion
#region 2.从【出库明细视图VW_WMS_DETAIL_INNER】中提取出【货主=应收仓储客户】数据，关联【仓储明细表[wms_settlement_detail_rate_detail_temp]】，插入【仓储结算明细临时表[wms_settlement_detail_temp]】
strSql = "insert into [wms_settlement_detail_temp]([GID],[BLNO],[CUSTOMNO],[CONTRACTNO],[WMSNO],[OUTBSNO],[CUSTOMERNAME],[STORAGENAME],[GOODSNAME],[GOODSMODEL],[GOODSSTANDARD],[GOODSGRADE],[WMSDATE],[DODATE],[WMSDAYS],[STARTBILLINGDATE_OUT],[ENDBILLINGDATE_OUT],[GOODSOUTFEE],[GOODSPACK],[GOODSPACKACTUAL],[GOODSPACKPFSL],[GOODSPACKSTOCK],[GOODSRKSL],[GOODSRKSLACTUAL],[GOODSPFSL],[GOODSSTOCK],[CHARGEUNIT],[REMARK_OUT],[FEETYPE],[FEEGRADE],[FEESCALE],[FEEPRICE],[STARTBILLINGDATE],[ENDBILLINGDATE],[FEEDAYS],[OUTFEE],[ARFEE],[CREATEUSER],[CREATETIME],[ISMAKEOUT],[MAKEOUTUSER],[MAKEOUTTIME],[CORPID],[LINENUM],[GID_LINK]) "
+ " select newid() as GID,i.BLNO,i.CUSTOMNO,i.CONTRACTNO,i.WMSNO,i.OUTBSNO_OUT,i.CUSTOMERNAME,i.STORAGENAME,i.GOODSNAME,i.GOODSMODEL,i.GOODSSTANDARD,i.GOODSGRADE,i.WMSDATE,i.DODATE as DODATE_OUT,i.WMSDAYS,i.STARTBILLINGDATE,i.ENDBILLINGDATE,i.GOODSOUTFEE_OUT,i.GOODSPACK,i.GOODSPACKACTUAL,i.GOODSPACKPFSL_OUT,i.GOODSPACKSTOCK,0 as GOODSRKSL,i.GOODSRKSLACTUAL,i.GOODSPFSL_OUT,0 as GOODSSTOCK,i.CHARGEUNIT,i.REMARK_OUT"
+ ",isnull(r.FEETYPE,1) as FEETYPE,isnull(r.FEEGRADE,1) as FEEGRADE,isnull(r.FEESCALE,0) as FEESCALE,isnull(r.FEEPRICE,0) as FEEPRICE,isnull(r.STARTBILLINGDATE,i.STARTBILLINGDATE) as STARTBILLINGDATE,isnull(r.ENDBILLINGDATE,i.ENDBILLINGDATE) as ENDBILLINGDATE,isnull(r.FEESCALE,0) as FEESCALE,(case when isChFee<>'0' then 0 else isnull(r.OUTFEE,0) end) as OUTFEE,0"
+ ",'" + strUserID + "',getdate(),0,'" + strUserID + "',getdate(),'" + strCompanyID + "',2,i.WMS_OUT_GID "
+ " from (select *,isChFee=isnull((select top 1 gid from ch_fee where WMSOUTBSNO=VW_WMS_DETAIL_INNER.WMS_OUT_GID and FEENAME='仓储费'),'0') from VW_WMS_DETAIL_INNER where CUSTOMERNAME=ARCLIENTWMSOUT and NID not in (select LINKGID from wms_settlement_date where ISDELETE=0 and TABLENAME='wms_out_detail')"
//+ " and WMS_OUT_GID not in (select isnull(WMSOUTBSNO,'') as WMSOUTBSNO from ch_fee) "
+ strCondition + strCondition2 + strCondition3 + ") as i LEFT JOIN [wms_settlement_detail_rate_detail_temp] as r on i.GID_OUT=r.ASSOCIATEDNO"
+ " where isnull(r.FEETYPE,1)=1 and r.CREATEUSER='" + strUserID + "'"
+ " ORDER BY r.ASSOCIATEDNO,r.FEETYPE,r.FEEGRADE,r.CREATETIME";
iState = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
if (iState < 0)
{
sqlTran.Rollback();
result = -5;
}
#endregion
#region 3.从【出库明细视图VW_WMS_DETAIL_INNER】中提取出【货主<>应收仓储客户】数据，生成【仓储明细表[wms_settlement_detail_rate_detail_temp]】
strSql = "select *,isChFee=isnull((select top 1 gid from ch_fee where WMSOUTBSNO=VW_WMS_DETAIL_INNER.WMS_OUT_GID and FEENAME='仓储费'),'0') from VW_WMS_DETAIL_INNER where CUSTOMERNAME<>ARCLIENTWMSOUT and NID not in (select LINKGID from wms_settlement_date where ISDELETE=0 and TABLENAME='wms_out_detail')"
//+ " and WMS_OUT_GID not in (select isnull(WMSOUTBSNO,'') as WMSOUTBSNO from ch_fee) "
+ strCondition + strCondition2 + strCondition3;
ds = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, strSql, null);
if (ds != null)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
#region 计算应收仓储费
//判断已经生成的日期
string strSTARTBILLINGDATE_ls = strSTARTBILLINGDATE;
string strENDBILLINGDATE_ls = strENDBILLINGDATE;
//判断以前是否有已生成的出库
string strWSD = "select top 1 * from wms_settlement_date where LINKGID='" + dr["WMS_OUT_GID"].ToString().Trim() + "' and TABLENAME='wms_out' and ISDELETE=0 order by ENDBILLINGDATE desc";
DataSet dsWSD = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, strWSD, null);
if (dsWSD != null)
{
if (dsWSD.Tables[0].Rows.Count > 0)
{
//开始计费日期赋值
if (DateTime.Parse(dsWSD.Tables[0].Rows[0]["ENDBILLINGDATE"].ToString().Trim()) > DateTime.Parse(strSTARTBILLINGDATE))
{
strSTARTBILLINGDATE_ls = DateTime.Parse(dsWSD.Tables[0].Rows[0]["ENDBILLINGDATE"].ToString().Trim()).AddDays(1).ToString().Trim();
}
}
else
{
//判断以前是否有已生成的出库库存
string strWSD_IN = "select top 1 * from wms_settlement_date where LINKGID='" + dr["GID"].ToString().Trim() + "' and TABLENAME='wms' and ISDELETE=0 order by ENDBILLINGDATE desc";
DataSet dsWSD_IN = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, strWSD_IN, null);
if (dsWSD_IN != null)
{
if (dsWSD_IN.Tables[0].Rows.Count > 0)
{
if (DateTime.Parse(dsWSD_IN.Tables[0].Rows[0]["ENDBILLINGDATE"].ToString().Trim()) > DateTime.Parse(strSTARTBILLINGDATE_ls))
{
strSTARTBILLINGDATE_ls = DateTime.Parse(dsWSD_IN.Tables[0].Rows[0]["ENDBILLINGDATE"].ToString().Trim()).AddDays(1).ToString().Trim();
}
}
else
{
//开始计费日期赋值
strSTARTBILLINGDATE_ls = dr["STARTBILLINGDATE"].ToString().Trim();
}
}
else
{
//开始计费日期赋值
strSTARTBILLINGDATE_ls = dr["STARTBILLINGDATE"].ToString().Trim();
}
}
}
else
{
//判断以前是否有已生成的出库库存
string strWSD_IN = "select top 1 * from wms_settlement_date where LINKGID='" + dr["GID"].ToString().Trim() + "' and TABLENAME='wms' and ISDELETE=0 order by ENDBILLINGDATE desc";
DataSet dsWSD_IN = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, strWSD_IN, null);
if (dsWSD_IN != null)
{
if (dsWSD_IN.Tables[0].Rows.Count > 0)
{
if (DateTime.Parse(dsWSD_IN.Tables[0].Rows[0]["ENDBILLINGDATE"].ToString().Trim()) > DateTime.Parse(strSTARTBILLINGDATE_ls))
{
strSTARTBILLINGDATE_ls = DateTime.Parse(dsWSD_IN.Tables[0].Rows[0]["ENDBILLINGDATE"].ToString().Trim()).AddDays(1).ToString().Trim();
}
}
else
{
//开始计费日期赋值
strSTARTBILLINGDATE_ls = dr["STARTBILLINGDATE"].ToString().Trim();
}
}
else
{
//开始计费日期赋值
strSTARTBILLINGDATE_ls = dr["STARTBILLINGDATE"].ToString().Trim();
}
}
//结束计费日期赋值
if (DateTime.Parse(dr["ENDBILLINGDATE"].ToString().Trim()) < DateTime.Parse(strENDBILLINGDATE))
{
strENDBILLINGDATE_ls = dr["ENDBILLINGDATE"].ToString().Trim();
}
//如果开始日期小于等于结束日期，则允许生成费用并统计
if (DateTime.Parse(strSTARTBILLINGDATE_ls) <= DateTime.Parse(strENDBILLINGDATE_ls))
{
//计算应收仓储费
decimal outnums = decimal.Parse(dr["GOODSPFSL_OUT"].ToString().Trim());//出库量
getWmsRate(sqlTran, strSTARTBILLINGDATE_ls.Trim(), strENDBILLINGDATE_ls.Trim(), dr["GID"].ToString().Trim(), dr["CUSTOMERNAME"].ToString().Trim(), outnums, dr["GID_OUT"].ToString().Trim(), 1, strUserID, strShowName, "现有出库重新计算应收仓储费", true, true);
//string getstr = getWmsRate(sqlTran, strSTARTBILLINGDATE_ls.Trim(), strENDBILLINGDATE_ls.Trim(), dr["GID"].ToString().Trim(), dr["CUSTOMERNAME"].ToString().Trim(), outnums, dr["WMS_OUT_GID"].ToString().Trim(), 1, strUserID, strShowName, "现有出库重新计算应收仓储费", true, true);
}
#endregion
}
}
#endregion
#region 3.从【出库明细视图VW_WMS_DETAIL_INNER】中提取出【货主<>应收仓储客户】数据，关联【仓储明细表[wms_settlement_detail_rate_detail_temp]】，插入【仓储结算明细临时表[wms_settlement_detail_temp]】
strSql = "insert into [wms_settlement_detail_temp]([GID],[BLNO],[CUSTOMNO],[CONTRACTNO],[WMSNO],[OUTBSNO],[CUSTOMERNAME],[STORAGENAME],[GOODSNAME],[GOODSMODEL],[GOODSSTANDARD],[GOODSGRADE],[WMSDATE],[DODATE],[WMSDAYS],[STARTBILLINGDATE_OUT],[ENDBILLINGDATE_OUT],[GOODSOUTFEE],[GOODSPACK],[GOODSPACKACTUAL],[GOODSPACKPFSL],[GOODSPACKSTOCK],[GOODSRKSL],[GOODSRKSLACTUAL],[GOODSPFSL],[GOODSSTOCK],[CHARGEUNIT],[REMARK_OUT],[FEETYPE],[FEEGRADE],[FEESCALE],[FEEPRICE],[STARTBILLINGDATE],[ENDBILLINGDATE],[FEEDAYS],[OUTFEE],[ARFEE],[CREATEUSER],[CREATETIME],[ISMAKEOUT],[MAKEOUTUSER],[MAKEOUTTIME],[CORPID],[LINENUM],[GID_LINK]) "
+ " select newid() as GID,i.BLNO,i.CUSTOMNO,i.CONTRACTNO,i.WMSNO,i.OUTBSNO_OUT,i.CUSTOMERNAME,i.STORAGENAME,i.GOODSNAME,i.GOODSMODEL,i.GOODSSTANDARD,i.GOODSGRADE,i.WMSDATE,i.DODATE as DODATE_OUT,i.WMSDAYS,i.STARTBILLINGDATE,i.ENDBILLINGDATE,i.GOODSOUTFEE_OUT,i.GOODSPACK,i.GOODSPACKACTUAL,i.GOODSPACKPFSL_OUT,i.GOODSPACKSTOCK,0 as GOODSRKSL,i.GOODSRKSLACTUAL,i.GOODSPFSL_OUT,0 as GOODSSTOCK,i.CHARGEUNIT,(i.REMARK_OUT+' 由 '+i.ARCLIENTWMSOUT+' 代收仓储费；') as REMARK_OUT"
+ ",isnull(r.FEETYPE,1) as FEETYPE,isnull(r.FEEGRADE,1) as FEEGRADE,isnull(r.FEESCALE,0) as FEESCALE,isnull(r.FEEPRICE,0) as FEEPRICE,isnull(r.STARTBILLINGDATE,i.STARTBILLINGDATE) as STARTBILLINGDATE,isnull(r.ENDBILLINGDATE,i.ENDBILLINGDATE) as ENDBILLINGDATE,isnull(r.FEESCALE,0) as FEESCALE,0,0"
+ ",'" + strUserID + "',getdate(),0,'" + strUserID + "',getdate(),'" + strCompanyID + "',3,i.WMS_OUT_GID "
+ " from (select *,isChFee=isnull((select top 1 gid from ch_fee where WMSOUTBSNO=VW_WMS_DETAIL_INNER.WMS_OUT_GID and FEENAME='仓储费'),'0') from VW_WMS_DETAIL_INNER where CUSTOMERNAME<>ARCLIENTWMSOUT and NID not in (select LINKGID from wms_settlement_date where ISDELETE=0 and TABLENAME='wms_out_detail')"
//+ " and WMS_OUT_GID not in (select isnull(WMSOUTBSNO,'') as WMSOUTBSNO from ch_fee) "
+ strCondition + strCondition2 + strCondition3 + ") as i LEFT JOIN [wms_settlement_detail_rate_detail_temp] as r on i.GID_OUT=r.ASSOCIATEDNO"
+ " where isnull(r.FEETYPE,1)=1 and r.CREATEUSER='" + strUserID + "'"
+ " ORDER BY r.ASSOCIATEDNO,r.FEETYPE,r.FEEGRADE,r.CREATETIME";
iState = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
if (iState < 0)
{
sqlTran.Rollback();
result = -6;
}
#endregion
#region 4.从【出库明细视图VW_WMS_DETAIL_INNER】中提取出【货主<>应收仓储客户】数据，生成【仓储明细表[wms_settlement_detail_rate_detail_temp]】
string strCondition_ls = strCondition.Replace("CUSTOMERNAME", "ARCLIENTWMSOUT");
string strCondition2_ls = strCondition2.Replace("CUSTOMERNAME", "ARCLIENTWMSOUT");
string strCondition3_ls = strCondition3.Replace("CUSTOMERNAME", "ARCLIENTWMSOUT");
strSql = "select *,isChFee=isnull((select top 1 gid from ch_fee where WMSOUTBSNO=VW_WMS_DETAIL_INNER.WMS_OUT_GID and FEENAME='仓储费'),'0') from VW_WMS_DETAIL_INNER where CUSTOMERNAME<>ARCLIENTWMSOUT and NID not in (select LINKGID from wms_settlement_date where ISDELETE=0 and TABLENAME='wms_out_detail')"
//+ " and WMS_OUT_GID not in (select isnull(WMSOUTBSNO,'') as WMSOUTBSNO from ch_fee) "
+ strCondition_ls + strCondition2_ls + strCondition3_ls;
ds = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, strSql, null);
if (ds != null)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
#region 计算应收仓储费
//判断已经生成的日期
string strSTARTBILLINGDATE_ls = strSTARTBILLINGDATE;
string strENDBILLINGDATE_ls = strENDBILLINGDATE;
//判断以前是否有已生成的出库
string strWSD = "select top 1 * from wms_settlement_date where LINKGID='" + dr["WMS_OUT_GID"].ToString().Trim() + "' and TABLENAME='wms_out' and ISDELETE=0 order by ENDBILLINGDATE desc";
DataSet dsWSD = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, strWSD, null);
if (dsWSD != null)
{
if (dsWSD.Tables[0].Rows.Count > 0)
{
//开始计费日期赋值
if (DateTime.Parse(dsWSD.Tables[0].Rows[0]["ENDBILLINGDATE"].ToString().Trim()) > DateTime.Parse(strSTARTBILLINGDATE))
{
strSTARTBILLINGDATE_ls = DateTime.Parse(dsWSD.Tables[0].Rows[0]["ENDBILLINGDATE"].ToString().Trim()).AddDays(1).ToString().Trim();
}
}
else
{
//判断以前是否有已生成的出库库存
string strWSD_IN = "select top 1 * from wms_settlement_date where LINKGID='" + dr["GID"].ToString().Trim() + "' and TABLENAME='wms' and ISDELETE=0 order by ENDBILLINGDATE desc";
DataSet dsWSD_IN = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, strWSD_IN, null);
if (dsWSD_IN != null)
{
if (dsWSD_IN.Tables[0].Rows.Count > 0)
{
if (DateTime.Parse(dsWSD_IN.Tables[0].Rows[0]["ENDBILLINGDATE"].ToString().Trim()) > DateTime.Parse(strSTARTBILLINGDATE_ls))
{
strSTARTBILLINGDATE_ls = DateTime.Parse(dsWSD_IN.Tables[0].Rows[0]["ENDBILLINGDATE"].ToString().Trim()).AddDays(1).ToString().Trim();
}
}
else
{
//开始计费日期赋值
strSTARTBILLINGDATE_ls = dr["STARTBILLINGDATE"].ToString().Trim();
}
}
else
{
//开始计费日期赋值
strSTARTBILLINGDATE_ls = dr["STARTBILLINGDATE"].ToString().Trim();
}
}
}
else
{
//判断以前是否有已生成的出库库存
string strWSD_IN = "select top 1 * from wms_settlement_date where LINKGID='" + dr["GID"].ToString().Trim() + "' and TABLENAME='wms' and ISDELETE=0 order by ENDBILLINGDATE desc";
DataSet dsWSD_IN = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, strWSD_IN, null);
if (dsWSD_IN != null)
{
if (dsWSD_IN.Tables[0].Rows.Count > 0)
{
if (DateTime.Parse(dsWSD_IN.Tables[0].Rows[0]["ENDBILLINGDATE"].ToString().Trim()) > DateTime.Parse(strSTARTBILLINGDATE_ls))
{
strSTARTBILLINGDATE_ls = DateTime.Parse(dsWSD_IN.Tables[0].Rows[0]["ENDBILLINGDATE"].ToString().Trim()).AddDays(1).ToString().Trim();
}
}
else
{
//开始计费日期赋值
strSTARTBILLINGDATE_ls = dr["STARTBILLINGDATE"].ToString().Trim();
}
}
else
{
//开始计费日期赋值
strSTARTBILLINGDATE_ls = dr["STARTBILLINGDATE"].ToString().Trim();
}
}
//结束计费日期赋值
if (DateTime.Parse(dr["ENDBILLINGDATE"].ToString().Trim()) < DateTime.Parse(strENDBILLINGDATE))
{
strENDBILLINGDATE_ls = dr["ENDBILLINGDATE"].ToString().Trim();
}
//如果开始日期小于等于结束日期，则允许生成费用并统计
if (DateTime.Parse(strSTARTBILLINGDATE_ls) <= DateTime.Parse(strENDBILLINGDATE_ls))
{
//计算应收仓储费
decimal outnums = decimal.Parse(dr["GOODSPFSL_OUT"].ToString().Trim());//出库量
getWmsRate(sqlTran, strSTARTBILLINGDATE_ls.Trim(), strENDBILLINGDATE_ls.Trim(), dr["GID"].ToString().Trim(), dr["CUSTOMERNAME"].ToString().Trim(), outnums, dr["GID_OUT"].ToString().Trim(), 1, strUserID, strShowName, "现有出库重新计算应收仓储费", true, true);
//string getstr = getWmsRate(sqlTran, strSTARTBILLINGDATE_ls.Trim(), strENDBILLINGDATE_ls.Trim(), dr["GID"].ToString().Trim(), dr["CUSTOMERNAME"].ToString().Trim(), outnums, dr["WMS_OUT_GID"].ToString().Trim(), 1, strUserID, strShowName, "现有出库重新计算应收仓储费", true, true);
}
#endregion
}
}
#endregion
#region 4.从【出库明细视图VW_WMS_DETAIL_INNER】中提取出【货主<>应收仓储客户】数据，把查询条件中的【货主】改为【应收仓储客户】，关联【仓储明细表】，插入【仓储结算明细临时表】
strSql = "insert into [wms_settlement_detail_temp]([GID],[BLNO],[CUSTOMNO],[CONTRACTNO],[WMSNO],[OUTBSNO],[CUSTOMERNAME],[STORAGENAME],[GOODSNAME],[GOODSMODEL],[GOODSSTANDARD],[GOODSGRADE],[WMSDATE],[DODATE],[WMSDAYS],[STARTBILLINGDATE_OUT],[ENDBILLINGDATE_OUT],[GOODSOUTFEE],[GOODSPACK],[GOODSPACKACTUAL],[GOODSPACKPFSL],[GOODSPACKSTOCK],[GOODSRKSL],[GOODSRKSLACTUAL],[GOODSPFSL],[GOODSSTOCK],[CHARGEUNIT],[REMARK_OUT],[FEETYPE],[FEEGRADE],[FEESCALE],[FEEPRICE],[STARTBILLINGDATE],[ENDBILLINGDATE],[FEEDAYS],[OUTFEE],[ARFEE],[CREATEUSER],[CREATETIME],[ISMAKEOUT],[MAKEOUTUSER],[MAKEOUTTIME],[CORPID],[LINENUM],[GID_LINK]) "
+ " select newid() as GID,i.BLNO,i.CUSTOMNO,i.CONTRACTNO,i.WMSNO,i.OUTBSNO_OUT,i.ARCLIENTWMSOUT,i.STORAGENAME,i.GOODSNAME,i.GOODSMODEL,i.GOODSSTANDARD,i.GOODSGRADE,i.WMSDATE,i.DODATE as DODATE_OUT,i.WMSDAYS,i.STARTBILLINGDATE,i.ENDBILLINGDATE,i.GOODSOUTFEE_OUT,i.GOODSPACK,i.GOODSPACKACTUAL,i.GOODSPACKPFSL_OUT,i.GOODSPACKSTOCK,0 as GOODSRKSL,i.GOODSRKSLACTUAL,i.GOODSPFSL_OUT,0 as GOODSSTOCK,i.CHARGEUNIT,(i.REMARK_OUT+' 代 '+i.CUSTOMERNAME+' 付仓储费；') as REMARK_OUT"
+ ",isnull(r.FEETYPE,1) as FEETYPE,isnull(r.FEEGRADE,1) as FEEGRADE,isnull(r.FEESCALE,0) as FEESCALE,isnull(r.FEEPRICE,0) as FEEPRICE,isnull(r.STARTBILLINGDATE,i.STARTBILLINGDATE) as STARTBILLINGDATE,isnull(r.ENDBILLINGDATE,i.ENDBILLINGDATE) as ENDBILLINGDATE,isnull(r.FEESCALE,0) as FEESCALE,(case when isChFee<>'0' then 0 else isnull(r.OUTFEE,0) end) as OUTFEE,0"
+ ",'" + strUserID + "',getdate(),0,'" + strUserID + "',getdate(),'" + strCompanyID + "',4,i.WMS_OUT_GID "
+ " from (select *,isChFee=isnull((select top 1 gid from ch_fee where WMSOUTBSNO=VW_WMS_DETAIL_INNER.WMS_OUT_GID and FEENAME='仓储费'),'0') from VW_WMS_DETAIL_INNER where CUSTOMERNAME<>ARCLIENTWMSOUT and NID not in (select LINKGID from wms_settlement_date where ISDELETE=0 and TABLENAME='wms_out_detail')"
//+ " and WMS_OUT_GID not in (select isnull(WMSOUTBSNO,'') as WMSOUTBSNO from ch_fee) "
+ strCondition_ls + strCondition2_ls + strCondition3_ls + ") as i LEFT JOIN [wms_settlement_detail_rate_detail_temp] as r on i.GID_OUT=r.ASSOCIATEDNO"
+ " where isnull(r.FEETYPE,1)=1 and r.CREATEUSER='" + strUserID + "'"
+ " ORDER BY r.ASSOCIATEDNO,r.FEETYPE,r.FEEGRADE,r.CREATETIME";
iState = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
if (iState < 0)
{
sqlTran.Rollback();
result = -6;
}
#endregion
#region 1.因需要计算剩余库存，用库存数据集出数，所以此语句暂时作废。从【出库明细视图VW_WMS_DETAIL_INNER】中提取入库数据，插入【仓储结算明细临时表[wms_settlement_detail_temp]】
//strSql = "insert into [wms_settlement_detail_temp]([GID],[BLNO],[CUSTOMNO],[CONTRACTNO],[WMSNO],[CUSTOMERNAME],[STORAGENAME],[GOODSNAME],[GOODSMODEL],[GOODSSTANDARD],[GOODSGRADE],[WMSDATE],[STARTBILLINGDATE_OUT],[GOODSPACK],[GOODSPACKACTUAL],[GOODSRKSL],[GOODSRKSLACTUAL],[GOODSSTOCK],[CHARGEUNIT],[CREATEUSER],[CREATETIME],[ISMAKEOUT],[MAKEOUTUSER],[MAKEOUTTIME],[CORPID],[LINENUM],[GID_LINK]) "
//+ " select newid() as GID,BLNO,CUSTOMNO,CONTRACTNO,WMSNO,CUSTOMERNAME,STORAGENAME,GOODSNAME,GOODSMODEL,GOODSSTANDARD,GOODSGRADE,WMSDATE,STARTBILLINGDATE,GOODSPACK,GOODSPACKACTUAL,GOODSRKSL,GOODSRKSLACTUAL,isnull(sum(GOODSPFSL_OUT),0) as GOODSSTOCK,CHARGEUNIT,'" + strUserID + "',getdate(),0,'" + strUserID + "',getdate(),'" + strCompanyID + "',1,GID from VW_WMS_DETAIL_INNER where 1=1 and NID not in (select LINKGID from wms_settlement_date where ISDELETE=0 and TABLENAME='wms_out_detail') and WMS_OUT_GID not in (select isnull(WMSOUTBSNO,'') as WMSOUTBSNO from ch_fee) and (gid in (select ASSOCIATEDNO from wms_settlement_detail_rate_detail_temp) or WMS_OUT_GID in (select ASSOCIATEDNO from wms_settlement_detail_rate_detail_temp))" + strCondition + strCondition2 + strCondition3
//+ " group by GID,BLNO,CUSTOMNO,CONTRACTNO,WMSNO,CUSTOMERNAME,STORAGENAME,GOODSNAME,GOODSMODEL,GOODSSTANDARD,GOODSGRADE,WMSDATE,STARTBILLINGDATE,GOODSPACK,GOODSPACKACTUAL,GOODSRKSL,GOODSRKSLACTUAL,CHARGEUNIT order by WMSNO";
//iState = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
//if (iState < 0)
//{
// sqlTran.Rollback();
// result = -7;
//}
#endregion
#region 2、3、4.从【仓储结算明细临时表[wms_settlement_detail_temp]】中提取数据，循环插入出入库费用
strSql = "update [wms_settlement_detail_temp] set ARFEE=isnull((select sum(isnull(AMOUNT,0)) as AMOUNT from ch_fee where isnull(WMSOUTBSNO,'')=[wms_settlement_detail_temp].GID_LINK and isnull(CUSTOMERNAME,'')=[wms_settlement_detail_temp].CUSTOMERNAME and feename<>'仓储费' and gid not in (select LINKGID from wms_settlement_date where ISDELETE=0 and TABLENAME='ch_fee')),0) where CREATEUSER='" + strUserID + "' and CORPID='" + strCompanyID + "' and (LINENUM=2 or LINENUM=3 or LINENUM=4) and FEETYPE=1 and FEEGRADE=1";
iState = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
if (iState < 0)
{
sqlTran.Rollback();
result = -8;
}
#endregion
#region 2、3、4.从【仓储结算明细临时表[wms_settlement_detail_temp]】中提取数据，循环插入出入库费用名称到备注
strSql = "select * from [wms_settlement_detail_temp] where CREATEUSER='" + strUserID + "' and CORPID='" + strCompanyID + "' and (LINENUM=2 or LINENUM=3 or LINENUM=4) and FEETYPE=1 and FEEGRADE=1 and ARFEE<>0";
ds = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, strSql, null);
if (ds != null)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
strSql = "select * from ch_fee where isnull(WMSOUTBSNO,'')='" + dr["GID_LINK"].ToString().Trim() + "' and isnull(CUSTOMERNAME,'')='" + dr["CUSTOMERNAME"].ToString().Trim() + "' and feename<>'仓储费' and gid not in (select LINKGID from wms_settlement_date where ISDELETE=0 and TABLENAME='ch_fee')";
DataSet dsChFee = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, strSql, null);
if (dsChFee != null)
{
string ls = "";
foreach (DataRow drChFee in dsChFee.Tables[0].Rows)
{
ls += "、" + drChFee["FEENAME"].ToString().Trim();
}
if (ls != "")
{
ls = "出库费包含：" + ls.Substring(1) + "；";
//
strSql = "update [wms_settlement_detail_temp] set REMARK_OUT=REMARK_OUT+' " + ls + "' where CREATEUSER='" + strUserID + "' and CORPID='" + strCompanyID + "' and gid='" + dr["GID"].ToString().Trim() + "'";
iState = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
if (iState < 0)
{
sqlTran.Rollback();
result = -8;
}
}
}
}
}
#endregion
#region 1.从【库存】中提取入库数据，插入【仓储结算明细临时表[wms_settlement_detail_temp]】
strSql = "insert into [wms_settlement_detail_temp]([GID],[BLNO],[CUSTOMNO],[CONTRACTNO],[WMSNO],[CUSTOMERNAME],[STORAGENAME],[GOODSNAME],[GOODSMODEL],[GOODSSTANDARD],[GOODSGRADE],[WMSDATE],[STARTBILLINGDATE_OUT],[GOODSPACK],[GOODSPACKACTUAL],[GOODSRKSL],[GOODSRKSLACTUAL],[GOODSSTOCK],[CHARGEUNIT],[CREATEUSER],[CREATETIME],[ISMAKEOUT],[MAKEOUTUSER],[MAKEOUTTIME],[CORPID],[LINENUM],[GID_LINK]) "
+ " select newid() as GID,BLNO,CUSTOMNO,CONTRACTNO,WMSNO,CUSTOMERNAME,STORAGENAME,GOODSNAME,GOODSMODEL,GOODSSTANDARD,GOODSGRADE,WMSDATE,STARTBILLINGDATE,GOODSPACK,GOODSPACKACTUAL,GOODSRKSL,GOODSRKSLACTUAL"
+ ",GOODSSTOCK=(select isnull((hj_GOODSPFSL_MONTH+hj_GOODSSTOCK),0) as ls_GOODSSTOCK from [" + TableName_ls + "] where GID=VW_WMS_DETAIL_INNER.GID and hj_GOODSRKSL<>hj_GOODSSTOCK)"
+ ",CHARGEUNIT,'" + strUserID + "',getdate(),0,'" + strUserID + "',getdate(),'" + strCompanyID + "',1,GID"
+ " from VW_WMS_DETAIL_INNER "
+ " where 1=1 and NID not in (select LINKGID from wms_settlement_date where ISDELETE=0 and TABLENAME='wms_out_detail')"
//+ " and WMS_OUT_GID not in (select isnull(WMSOUTBSNO,'') as WMSOUTBSNO from ch_fee)"
+ " and (gid in (select ASSOCIATEDNO from wms_settlement_detail_rate_detail_temp) or WMS_OUT_GID in (select ASSOCIATEDNO from wms_settlement_detail_rate_detail_temp))"
+ " and WMSNO in (select WMSNO from wms_settlement_detail_temp where WMSNO=VW_WMS_DETAIL_INNER.WMSNO and (LINENUM=2 or LINENUM=3 or LINENUM=4))"//因此查询条件所以必须放到最后
+ strCondition + strCondition2 + strCondition3
+ " group by GID,BLNO,CUSTOMNO,CONTRACTNO,WMSNO,CUSTOMERNAME,STORAGENAME,GOODSNAME,GOODSMODEL,GOODSSTANDARD,GOODSGRADE,WMSDATE,STARTBILLINGDATE,GOODSPACK,GOODSPACKACTUAL,GOODSRKSL,GOODSRKSLACTUAL,CHARGEUNIT";
iState = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
if (iState < 0)
{
sqlTran.Rollback();
result = -7;
}
#endregion
//
result = 1;//状态为1表示删除成功
sqlTran.Commit();
}
catch (Exception execError)
{
result = -8;//有异常,插入失败
sqlTran.Rollback();
result = -9;//插入异常,事务已回滚成功
throw execError;
}
finally
{
SqlHelper.CloseConnection();
sqlTran.Dispose();
}
}
#endregion
#region 清理临时表
//T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA();
//string strSql1 = "DROP TABLE [" + TableName_ls + "]";
//bool bl = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, strSql1);
#endregion
#region 从【仓储结算明细临时表[wms_settlement_detail_temp]】中提取数据列表，并返回js页面
//按提单号合计出一部分数据
string strSql2 = "SELECT newid() as GID,DUIGID,DUINO,BLNO,CUSTOMNO,CONTRACTNO,CUSTOMERNAME,GOODSNAME FROM [wms_settlement_detail_temp] where CREATEUSER='" + strUserID + "' and CORPID='" + strCompanyID + "' group by DUIGID,DUINO,BLNO,CUSTOMNO,CONTRACTNO,CUSTOMERNAME,GOODSNAME,CREATEUSER,CORPID";
var sortstring = DatasetSort.Getsortstring(sort);
if (!string.IsNullOrEmpty(sortstring))
{
strSql2 += " order by " + sortstring;
}
else
{
strSql2 += " order by BLNO,CUSTOMERNAME";
}
return SetFeeData(strSql2);
#endregion
}
private static List<RptWmsSettlementDetailEntity> SetFeeData(String strSql)
{
var headList = new List<RptWmsSettlementDetailEntity>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
RptWmsSettlementDetailEntity data = new RptWmsSettlementDetailEntity();
#region Set DB data to Object
data.GID = Convert.ToString(reader["GID"]);
data.DUIGID = Convert.ToString(reader["DUIGID"]);
data.DUINO = Convert.ToString(reader["DUINO"]);
data.BLNO = Convert.ToString(reader["BLNO"]);
data.CUSTOMNO = Convert.ToString(reader["CUSTOMNO"]);
data.CONTRACTNO = Convert.ToString(reader["CONTRACTNO"]);
data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]);
data.GOODSNAME = Convert.ToString(reader["GOODSNAME"]);
//data.WMSNO = Convert.ToString(reader["WMSNO"]);
//data.OUTBSNO = Convert.ToString(reader["OUTBSNO"]);
//data.STORAGENAME = Convert.ToString(reader["STORAGENAME"]);
//data.GOODSMODEL = Convert.ToString(reader["GOODSMODEL"]);
//data.GOODSSTANDARD = Convert.ToString(reader["GOODSSTANDARD"]);
//data.GOODSGRADE = Convert.ToString(reader["GOODSGRADE"]);
//data.WMSDATE = Convert.ToString(reader["WMSDATE"]);
//data.DODATE = Convert.ToString(reader["DODATE"]);
//data.WMSDAYS = Convert.ToInt32(reader["WMSDAYS"]);
//data.STARTBILLINGDATE_OUT = Convert.ToString(reader["STARTBILLINGDATE_OUT"]);
//data.ENDBILLINGDATE_OUT = Convert.ToString(reader["ENDBILLINGDATE_OUT"]);
//data.GOODSOUTFEE = Convert.ToDecimal(reader["GOODSOUTFEE"]);
//data.GOODSPACK = Convert.ToDecimal(reader["GOODSPACK"]);
//data.GOODSPACKACTUAL = Convert.ToDecimal(reader["GOODSPACKACTUAL"]);
//data.GOODSPACKPFSL = Convert.ToDecimal(reader["GOODSPACKPFSL"]);
//data.GOODSPACKSTOCK = Convert.ToDecimal(reader["GOODSPACKSTOCK"]);
//data.GOODSRKSL = Convert.ToDecimal(reader["GOODSRKSL"]);
//data.GOODSRKSLACTUAL = Convert.ToDecimal(reader["GOODSRKSLACTUAL"]);
//data.GOODSPFSL = Convert.ToDecimal(reader["GOODSPFSL"]);
//data.GOODSSTOCK = Convert.ToDecimal(reader["GOODSSTOCK"]);
//data.CHARGEUNIT = Convert.ToString(reader["CHARGEUNIT"]);
//data.REMARK_OUT = Convert.ToString(reader["REMARK_OUT"]);
//data.FEETYPE = Convert.ToInt32(reader["FEETYPE"]);
//data.FEEGRADE = Convert.ToInt32(reader["FEEGRADE"]);
//data.FEESCALE = Convert.ToInt32(reader["FEESCALE"]);
//data.FEEPRICE = Convert.ToDecimal(reader["FEEPRICE"]);
//data.STARTBILLINGDATE = Convert.ToString(reader["STARTBILLINGDATE"]);
//data.ENDBILLINGDATE = Convert.ToString(reader["ENDBILLINGDATE"]);
//data.FEEDAYS = Convert.ToInt32(reader["FEEDAYS"]);
//data.OUTFEE = Convert.ToDecimal(reader["OUTFEE"]);
//data.ARFEE = Convert.ToDecimal(reader["ARFEE"]);
//data.CREATEUSER = Convert.ToString(reader["CREATEUSER"]);
//if (reader["CREATETIME"] != DBNull.Value)
// data.MAKEOUTTIME = Convert.ToDateTime(reader["CREATETIME"]);
//data.ISMAKEOUT = Convert.ToBoolean(reader["ISMAKEOUT"]);
//data.MAKEOUTUSER = Convert.ToString(reader["MAKEOUTUSER"]);
//if (reader["MAKEOUTTIME"] != DBNull.Value)
// data.MAKEOUTTIME = Convert.ToDateTime(reader["MAKEOUTTIME"]);
//data.CORPID = Convert.ToString(reader["CORPID"]);
//data.LINENUM = Convert.ToInt32(reader["LINENUM"]);
//data.GID_LINK = Convert.ToString(reader["GID_LINK"]);
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
#endregion
#region 保存
public static DBResult Save(RptWmsSettlementListEntity model, string condition, string strUserID, string strCompanyID)
{
DBResult result = new DBResult();
if (model == null)
{
result.Success = false;
result.Message = "没有输入任何需要修改的数据";
}
List<ModelObjectBase> dataList = new List<ModelObjectBase>();
dataList.Add(model);
//
Database db = DatabaseFactory.CreateDatabase();
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
{
try
{
#region 保存主表wms_settlement_list
String strSql = "insert into wms_settlement_list(GID,DUINO,CUSTOMERNAME,{0}{2}REMARK,CORPID,CREATEUSER,CREATETIME,ISMAKEOUT,MAKEOUTUSER,MAKEOUTTIME) values (@GID,@DUINO,@CUSTOMERNAME,{1}{3}@REMARK,@CORPID,@CREATEUSER,getdate(),@ISMAKEOUT,@MAKEOUTUSER,getdate())";
SqlParameter[] parameters = {
new SqlParameter("@GID", SqlDbType.VarChar,36) ,
new SqlParameter("@DUINO", SqlDbType.VarChar,100) ,
new SqlParameter("@CUSTOMERNAME", SqlDbType.VarChar,20) ,
new SqlParameter("@REMARK", SqlDbType.VarChar,1024) ,
new SqlParameter("@CORPID", SqlDbType.VarChar,36) ,
new SqlParameter("@CREATEUSER", SqlDbType.VarChar,36) ,
new SqlParameter("@ISMAKEOUT", SqlDbType.Bit,1) ,
new SqlParameter("@MAKEOUTUSER", SqlDbType.VarChar,36)
};
parameters[0].Value = model.GID;
parameters[1].Value = model.DUINO;
parameters[2].Value = model.CUSTOMERNAME;
parameters[3].Value = model.REMARK;
parameters[4].Value = strCompanyID;
parameters[5].Value = strUserID;
parameters[6].Value = false;
parameters[7].Value = strUserID;
//操作时间
string strSTARTBILLINGDATE = model.STARTBILLINGDATE.ToString().IndexOf("0001") > -1 ? "" : "'" + model.STARTBILLINGDATE.ToString() + "',";
string strSTARTBILLINGDATE_name = model.STARTBILLINGDATE.ToString().IndexOf("0001") > -1 ? "" : "STARTBILLINGDATE,";
//
string strENDBILLINGDATE = model.ENDBILLINGDATE.ToString().IndexOf("0001") > -1 ? "" : "'" + model.ENDBILLINGDATE.ToString() + "',";
string strENDBILLINGDATE_name = model.ENDBILLINGDATE.ToString().IndexOf("0001") > -1 ? "" : "ENDBILLINGDATE,";
//
strSql = String.Format(strSql, strSTARTBILLINGDATE_name, strSTARTBILLINGDATE, strENDBILLINGDATE_name, strENDBILLINGDATE);
//
int existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), parameters);
#endregion
#region 保存明细表[wms_settlement_detail_temp]
strSql = "insert into wms_settlement_detail([GID],[DUIGID],[DUINO],[BLNO],[CUSTOMNO],[CONTRACTNO],[WMSNO],[OUTBSNO],[CUSTOMERNAME],[STORAGENAME],[GOODSNAME],[GOODSMODEL],[GOODSSTANDARD],[GOODSGRADE],[WMSDATE],[DODATE],[WMSDAYS],[STARTBILLINGDATE_OUT],[ENDBILLINGDATE_OUT],[GOODSOUTFEE],[GOODSPACK],[GOODSPACKACTUAL],[GOODSPACKPFSL],[GOODSPACKSTOCK],[GOODSRKSL],[GOODSRKSLACTUAL],[GOODSPFSL],[GOODSSTOCK],[CHARGEUNIT],[REMARK_OUT],[FEETYPE],[FEEGRADE],[FEESCALE],[FEEPRICE],[STARTBILLINGDATE],[ENDBILLINGDATE],[FEEDAYS],[OUTFEE],[ARFEE],[CREATEUSER],[CREATETIME],[ISMAKEOUT],[MAKEOUTUSER],[MAKEOUTTIME],[CORPID],[LINENUM],[GID_LINK]) "
+ "select [GID],'" + model.GID.ToString().Trim() + "' as DUIGID,'" + model.DUINO.ToString().Trim() + "' as [DUINO],[BLNO],[CUSTOMNO],[CONTRACTNO],[WMSNO],[OUTBSNO],[CUSTOMERNAME],[STORAGENAME],[GOODSNAME],[GOODSMODEL],[GOODSSTANDARD],[GOODSGRADE],[WMSDATE],[DODATE],[WMSDAYS],[STARTBILLINGDATE_OUT],[ENDBILLINGDATE_OUT],[GOODSOUTFEE],[GOODSPACK],[GOODSPACKACTUAL],[GOODSPACKPFSL],[GOODSPACKSTOCK],[GOODSRKSL],[GOODSRKSLACTUAL],[GOODSPFSL],[GOODSSTOCK],[CHARGEUNIT],[REMARK_OUT],[FEETYPE],[FEEGRADE],[FEESCALE],[FEEPRICE],[STARTBILLINGDATE],[ENDBILLINGDATE],[FEEDAYS],[OUTFEE],[ARFEE],[CREATEUSER],getdate(),[ISMAKEOUT],[MAKEOUTUSER],getdate(),[CORPID],[LINENUM],[GID_LINK]"
+ " from [wms_settlement_detail_temp] where CREATEUSER='" + strUserID + "' and CORPID='" + strCompanyID + "'" + condition;
existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
#region 保存入库GID到关联表
strSql = "insert into wms_settlement_date([GID],[DUIGID],[DUINO],[LINKGID],[TABLENAME],[STARTBILLINGDATE],[ENDBILLINGDATE],[CREATEUSER],[CREATETIME]) "
+ "select newid() as GID,'" + model.GID.ToString().Trim() + "' as DUIGID,'" + model.DUINO.ToString().Trim() + "' as DUINO,GID_LINK as LINKGID,'wms' as TABLENAME," + strSTARTBILLINGDATE + strENDBILLINGDATE + "CREATEUSER,getdate() from [wms_settlement_detail_temp] where CREATEUSER='" + strUserID + "' and CORPID='" + strCompanyID + "' and (LINENUM=1 or LINENUM=5) " + condition + " group by LINENUM,GID_LINK,CREATEUSER,CORPID";
existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
#region 保存出库GID到关联表
strSql = "insert into wms_settlement_date([GID],[DUIGID],[DUINO],[LINKGID],[TABLENAME],[STARTBILLINGDATE],[ENDBILLINGDATE],[CREATEUSER],[CREATETIME]) "
+ "select newid() as GID,'" + model.GID.ToString().Trim() + "' as DUIGID,'" + model.DUINO.ToString().Trim() + "' as DUINO,GID_LINK as LINKGID,'wms_out' as TABLENAME," + strSTARTBILLINGDATE + strENDBILLINGDATE + "CREATEUSER,getdate() from [wms_settlement_detail_temp] where CREATEUSER='" + strUserID + "' and CORPID='" + strCompanyID + "' and (LINENUM=2 or LINENUM=3 or LINENUM=4) " + condition + " group by LINENUM,GID_LINK,CREATEUSER,CORPID";
existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
#region 保存ch_fee的GID到关联表
strSql = "insert into wms_settlement_date([GID],[DUIGID],[DUINO],[LINKGID],[TABLENAME],[STARTBILLINGDATE],[ENDBILLINGDATE],[CREATEUSER],[CREATETIME]) "
+ "select newid() as GID,'" + model.GID.ToString().Trim() + "' as DUIGID,'" + model.DUINO.ToString().Trim() + "' as DUINO,GID as LINKGID,'ch_fee' as TABLENAME," + strSTARTBILLINGDATE + strENDBILLINGDATE + "'" + strUserID + "' as CREATEUSER,getdate() from ch_fee where isnull(WMSOUTBSNO,'') in (select GID_LINK from [wms_settlement_detail_temp] where CREATEUSER='" + strUserID + "' and CORPID='" + strCompanyID + "' and (LINENUM=2 or LINENUM=3 or LINENUM=4) " + condition + ") and (FEESTATUS<>9 or FEESTATUS<>8)";
existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
#region 从【仓储结算明细临时表[wms_settlement_detail_temp]】中提取数据，按提单号循环插入ch_Fee，因为添加锁定功能，则暂时屏蔽
//strSql = "insert into ch_fee(GID,BSNO,FEETYPE,FEENAME,CUSTOMERNAME,UNIT,UNITPRICE,QUANTITY,AMOUNT,EXCHANGERATE,CURRENCY,COMMISSIONRATE,FEEFRT,REMARK,ISADVANCEDPAY,ENTEROPERATOR,ENTERDATE,FEESTATUS,WMSOUTBSNO,LINENUM) "
//+ "select newid() as GID,bsno=(select top 1 bsno from op_seai where mblno=[wms_settlement_detail_temp].blno),1,'仓储费',CUSTOMERNAME,'单票',sum(isnull(OUTFEE,0)),1,sum(isnull(OUTFEE,0)),1,'RMB',0,'PP','仓储结算清单“" + model.DUINO.ToString().Trim() + "”中的单票仓储费汇总',0,'" + strUserID + "',getdate(),1,'" + model.GID.ToString().Trim() + "',LINENUM=(select top 1 LINENUM+1 from ch_fee where bsno=(select top 1 bsno from op_seai where mblno=[wms_settlement_detail_temp].blno) and feetype=1 order by LINENUM desc)"
//+ " from [wms_settlement_detail_temp] where CREATEUSER='" + strUserID + "' and CORPID='" + strCompanyID + "' and isnull(OUTFEE,0)<>0 " + condition + " group by blno,CUSTOMERNAME,CREATEUSER,CORPID";
//existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
sqlTran.Commit();
}
catch (Exception)
{
sqlTran.Rollback();
result.Success = false;
result.Message = "保存出现错误，请重试或联系系统管理员";
return result;
}
finally
{
SqlHelper.CloseConnection();
sqlTran.Dispose();
}
}
result.Success = true;
result.Message = "保存成功";
return result;
}
#endregion
#region 保存
public static DBResult Save(string opstatus, string data, string condition, string strUserID, string strCompanyID)
{
DBResult result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
{
try
{
string strSql = "select CUSTOMERNAME from [wms_settlement_detail_temp] where CREATEUSER='" + strUserID + "' and CORPID='" + strCompanyID + "'" + condition + " group by CUSTOMERNAME";
DataSet ds = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, strSql, null);
if (ds != null)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
string condition2 = " and CUSTOMERNAME='" + dr["CUSTOMERNAME"].ToString().Trim() + "'";
//
#region 保存主表wms_settlement_list
var model = JsonConvert.Deserialize<RptWmsSettlementListEntity>(data);
if (opstatus == "add")
{
model.GID = "WMSD" + Guid.NewGuid().ToString().Replace("-", "");
model.DUINO = PubSysDAL.GetBillNo("0302");//生成编号
model.CUSTOMERNAME = dr["CUSTOMERNAME"].ToString().Trim();
model.CREATEUSER = strUserID;
model.CREATETIME = DateTime.Now;
model.DbOperationType = DbOperationType.DbotIns;
}
else if (opstatus == "edit")
{
model.DbOperationType = DbOperationType.DbotUpd;
}
else
{
model.DbOperationType = DbOperationType.DbotDel;
}
//
if (model == null)
{
result.Success = false;
result.Message = "没有输入任何需要修改的数据";
}
List<ModelObjectBase> dataList = new List<ModelObjectBase>();
dataList.Add(model);
//
strSql = "insert into wms_settlement_list(GID,DUINO,CUSTOMERNAME,{0}{2}REMARK,CORPID,CREATEUSER,CREATETIME,ISMAKEOUT,MAKEOUTUSER,MAKEOUTTIME) values (@GID,@DUINO,@CUSTOMERNAME,{1}{3}@REMARK,@CORPID,@CREATEUSER,getdate(),@ISMAKEOUT,@MAKEOUTUSER,getdate())";
SqlParameter[] parameters = {
new SqlParameter("@GID", SqlDbType.VarChar,36) ,
new SqlParameter("@DUINO", SqlDbType.VarChar,100) ,
new SqlParameter("@CUSTOMERNAME", SqlDbType.VarChar,20) ,
new SqlParameter("@REMARK", SqlDbType.VarChar,1024) ,
new SqlParameter("@CORPID", SqlDbType.VarChar,36) ,
new SqlParameter("@CREATEUSER", SqlDbType.VarChar,36) ,
new SqlParameter("@ISMAKEOUT", SqlDbType.Bit,1) ,
new SqlParameter("@MAKEOUTUSER", SqlDbType.VarChar,36)
};
parameters[0].Value = model.GID;
parameters[1].Value = model.DUINO;
parameters[2].Value = model.CUSTOMERNAME;
parameters[3].Value = model.REMARK;
parameters[4].Value = strCompanyID;
parameters[5].Value = strUserID;
parameters[6].Value = false;
parameters[7].Value = strUserID;
//操作时间
string strSTARTBILLINGDATE = model.STARTBILLINGDATE.ToString().IndexOf("0001") > -1 ? "" : "'" + model.STARTBILLINGDATE.ToString() + "',";
string strSTARTBILLINGDATE_name = model.STARTBILLINGDATE.ToString().IndexOf("0001") > -1 ? "" : "STARTBILLINGDATE,";
//
string strENDBILLINGDATE = model.ENDBILLINGDATE.ToString().IndexOf("0001") > -1 ? "" : "'" + model.ENDBILLINGDATE.ToString() + "',";
string strENDBILLINGDATE_name = model.ENDBILLINGDATE.ToString().IndexOf("0001") > -1 ? "" : "ENDBILLINGDATE,";
//
strSql = String.Format(strSql, strSTARTBILLINGDATE_name, strSTARTBILLINGDATE, strENDBILLINGDATE_name, strENDBILLINGDATE);
//
int existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), parameters);
#endregion
#region 保存明细表[wms_settlement_detail_temp]
strSql = "insert into wms_settlement_detail([GID],[DUIGID],[DUINO],[BLNO],[CUSTOMNO],[CONTRACTNO],[WMSNO],[OUTBSNO],[CUSTOMERNAME],[STORAGENAME],[GOODSNAME],[GOODSMODEL],[GOODSSTANDARD],[GOODSGRADE],[WMSDATE],[DODATE],[WMSDAYS],[STARTBILLINGDATE_OUT],[ENDBILLINGDATE_OUT],[GOODSOUTFEE],[GOODSPACK],[GOODSPACKACTUAL],[GOODSPACKPFSL],[GOODSPACKSTOCK],[GOODSRKSL],[GOODSRKSLACTUAL],[GOODSPFSL],[GOODSSTOCK],[CHARGEUNIT],[REMARK_OUT],[FEETYPE],[FEEGRADE],[FEESCALE],[FEEPRICE],[STARTBILLINGDATE],[ENDBILLINGDATE],[FEEDAYS],[OUTFEE],[ARFEE],[CREATEUSER],[CREATETIME],[ISMAKEOUT],[MAKEOUTUSER],[MAKEOUTTIME],[CORPID],[LINENUM],[GID_LINK]) "
+ "select [GID],'" + model.GID.ToString().Trim() + "' as DUIGID,'" + model.DUINO.ToString().Trim() + "' as [DUINO],[BLNO],[CUSTOMNO],[CONTRACTNO],[WMSNO],[OUTBSNO],[CUSTOMERNAME],[STORAGENAME],[GOODSNAME],[GOODSMODEL],[GOODSSTANDARD],[GOODSGRADE],[WMSDATE],[DODATE],[WMSDAYS],[STARTBILLINGDATE_OUT],[ENDBILLINGDATE_OUT],[GOODSOUTFEE],[GOODSPACK],[GOODSPACKACTUAL],[GOODSPACKPFSL],[GOODSPACKSTOCK],[GOODSRKSL],[GOODSRKSLACTUAL],[GOODSPFSL],[GOODSSTOCK],[CHARGEUNIT],[REMARK_OUT],[FEETYPE],[FEEGRADE],[FEESCALE],[FEEPRICE],[STARTBILLINGDATE],[ENDBILLINGDATE],[FEEDAYS],[OUTFEE],[ARFEE],[CREATEUSER],getdate(),[ISMAKEOUT],[MAKEOUTUSER],getdate(),[CORPID],[LINENUM],[GID_LINK]"
+ " from [wms_settlement_detail_temp] where CREATEUSER='" + strUserID + "' and CORPID='" + strCompanyID + "'" + condition + condition2;
existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
#region 保存入库GID到关联表
strSql = "insert into wms_settlement_date([GID],[DUIGID],[DUINO],[LINKGID],[TABLENAME],[STARTBILLINGDATE],[ENDBILLINGDATE],[CREATEUSER],[CREATETIME]) "
+ "select newid() as GID,'" + model.GID.ToString().Trim() + "' as DUIGID,'" + model.DUINO.ToString().Trim() + "' as DUINO,GID_LINK as LINKGID,'wms' as TABLENAME," + strSTARTBILLINGDATE + strENDBILLINGDATE + "CREATEUSER,getdate() from [wms_settlement_detail_temp] where CREATEUSER='" + strUserID + "' and CORPID='" + strCompanyID + "' and (LINENUM=1 or LINENUM=5) " + condition + condition2 + " group by LINENUM,GID_LINK,CREATEUSER,CORPID";
existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
#region 保存出库GID到关联表
strSql = "insert into wms_settlement_date([GID],[DUIGID],[DUINO],[LINKGID],[TABLENAME],[STARTBILLINGDATE],[ENDBILLINGDATE],[CREATEUSER],[CREATETIME]) "
+ "select newid() as GID,'" + model.GID.ToString().Trim() + "' as DUIGID,'" + model.DUINO.ToString().Trim() + "' as DUINO,GID_LINK as LINKGID,'wms_out' as TABLENAME," + strSTARTBILLINGDATE + strENDBILLINGDATE + "CREATEUSER,getdate() from [wms_settlement_detail_temp] where CREATEUSER='" + strUserID + "' and CORPID='" + strCompanyID + "' and (LINENUM=2 or LINENUM=3 or LINENUM=4) " + condition + condition2 + " group by LINENUM,GID_LINK,CREATEUSER,CORPID";
existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
#region 保存ch_fee的GID到关联表
strSql = "insert into wms_settlement_date([GID],[DUIGID],[DUINO],[LINKGID],[TABLENAME],[STARTBILLINGDATE],[ENDBILLINGDATE],[CREATEUSER],[CREATETIME]) "
+ "select newid() as GID,'" + model.GID.ToString().Trim() + "' as DUIGID,'" + model.DUINO.ToString().Trim() + "' as DUINO,GID as LINKGID,'ch_fee' as TABLENAME," + strSTARTBILLINGDATE + strENDBILLINGDATE + "'" + strUserID + "' as CREATEUSER,getdate() from ch_fee where isnull(WMSOUTBSNO,'') in (select GID_LINK from [wms_settlement_detail_temp] where CREATEUSER='" + strUserID + "' and CORPID='" + strCompanyID + "' and (LINENUM=2 or LINENUM=3 or LINENUM=4) " + condition + condition2 + ") and (FEESTATUS<>9 or FEESTATUS<>8)";
existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
#region 从【仓储结算明细临时表[wms_settlement_detail_temp]】中提取数据，按提单号循环插入ch_Fee，因为添加锁定功能，则暂时屏蔽
//strSql = "insert into ch_fee(GID,BSNO,FEETYPE,FEENAME,CUSTOMERNAME,UNIT,UNITPRICE,QUANTITY,AMOUNT,EXCHANGERATE,CURRENCY,COMMISSIONRATE,FEEFRT,REMARK,ISADVANCEDPAY,ENTEROPERATOR,ENTERDATE,FEESTATUS,WMSOUTBSNO,LINENUM) "
//+ "select newid() as GID,bsno=(select top 1 bsno from op_seai where mblno=[wms_settlement_detail_temp].blno),1,'仓储费',CUSTOMERNAME,'单票',sum(isnull(OUTFEE,0)),1,sum(isnull(OUTFEE,0)),1,'RMB',0,'PP','仓储结算清单“" + model.DUINO.ToString().Trim() + "”中的单票仓储费汇总',0,'" + strUserID + "',getdate(),1,'" + model.GID.ToString().Trim() + "',LINENUM=(select top 1 LINENUM+1 from ch_fee where bsno=(select top 1 bsno from op_seai where mblno=[wms_settlement_detail_temp].blno) and feetype=1 order by LINENUM desc)"
//+ " from [wms_settlement_detail_temp] where CREATEUSER='" + strUserID + "' and CORPID='" + strCompanyID + "' and isnull(OUTFEE,0)<>0 " + condition + condition2 + " group by blno,CUSTOMERNAME,CREATEUSER,CORPID";
//existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
//
}
}
sqlTran.Commit();
}
catch (Exception)
{
sqlTran.Rollback();
result.Success = false;
result.Message = "保存出现错误，请重试或联系系统管理员";
return result;
}
finally
{
SqlHelper.CloseConnection();
sqlTran.Dispose();
}
}
result.Success = true;
result.Message = "保存成功";
return result;
}
#endregion
#region feedetail DataList 明细表关联的出库费
static public List<ChFeeDetail> GetFeeDetailList(string strCondition)
{
string strSql = "SELECT * from ch_fee where gid not in (select LINKGID from wms_settlement_date where ISDELETE=0 and TABLENAME='ch_fee')";// ISDEBIT=0 and
if (!string.IsNullOrEmpty(strCondition))
{
strSql += strCondition;
}
return SetFeeDetailData(strSql);
}
private static List<ChFeeDetail> SetFeeDetailData(String strSql)
{
var headList = new List<ChFeeDetail>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql))
{
while (reader.Read())
{
ChFeeDetail data = new ChFeeDetail();
#region Set DB data to Object
data.GID = Convert.ToString(reader["GID"]);
//
if (Convert.ToString(reader["FEESTATUS"]) == "0")
{
data.FEESTATUS = "审核通过";
}
else if (Convert.ToString(reader["FEESTATUS"]) == "1")
{
data.FEESTATUS = "录入状态";
}
else if (Convert.ToString(reader["FEESTATUS"]) == "2")
{
data.FEESTATUS = "提交审核";
}
else if (Convert.ToString(reader["FEESTATUS"]) == "3")
{
data.FEESTATUS = "申请修改";
}
else if (Convert.ToString(reader["FEESTATUS"]) == "4")
{
data.FEESTATUS = "申请删除";
}
else if (Convert.ToString(reader["FEESTATUS"]) == "5")
{
data.FEESTATUS = "取消申请";
}
else if (Convert.ToString(reader["FEESTATUS"]) == "6")
{
data.FEESTATUS = "驳回提交";
}
else if (Convert.ToString(reader["FEESTATUS"]) == "7")
{
data.FEESTATUS = "驳回申请";
}
else if (Convert.ToString(reader["FEESTATUS"]) == "8")
{
data.FEESTATUS = "部分结算";
}
else if (Convert.ToString(reader["FEESTATUS"]) == "9")
{
data.FEESTATUS = "结算完毕";
}
//
if(Convert.ToString(reader["FEETYPE"])=="1")
{
data.FEETYPE = "收";
}
else
{
data.FEETYPE = "付";
}
data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]);
data.FEENAME = Convert.ToString(reader["FEENAME"]);
data.AMOUNT = Convert.ToDecimal(reader["AMOUNT"]);
data.CURRENCY = Convert.ToString(reader["CURRENCY"]);
data.SETTLEMENT = Convert.ToDecimal(reader["SETTLEMENT"]);
data.EXCHANGERATE = Convert.ToDecimal(reader["EXCHANGERATE"]);
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
#endregion
#region Inquery 仓储结算清单明细临时表([wms_settlement_detail_temp])根据提单号查询
static public List<RptWmsSettlementDetailEntity> GetMblnoDetailList(string strCondition, string strUserID, string strCompanyID, string sort = null)
{
string strSql2 = "SELECT GID,BLNO,CUSTOMNO,CONTRACTNO,WMSNO,OUTBSNO,CUSTOMERNAME,STORAGENAME,GOODSNAME,GOODSMODEL,GOODSSTANDARD,GOODSGRADE,dbo.trimdate(WMSDATE) as WMSDATE,dbo.trimdate(DODATE) as DODATE,WMSDAYS,dbo.trimdate(STARTBILLINGDATE_OUT) as STARTBILLINGDATE_OUT,dbo.trimdate(ENDBILLINGDATE_OUT) as ENDBILLINGDATE_OUT,GOODSOUTFEE,GOODSPACK,GOODSPACKACTUAL,GOODSPACKPFSL,GOODSPACKSTOCK,GOODSRKSL,GOODSRKSLACTUAL,GOODSPFSL,GOODSSTOCK,CHARGEUNIT,REMARK_OUT,FEETYPE,FEEGRADE,FEESCALE,FEEPRICE,dbo.trimdate(STARTBILLINGDATE) as STARTBILLINGDATE,dbo.trimdate(ENDBILLINGDATE) as ENDBILLINGDATE,FEEDAYS,OUTFEE,[ARFEE],CREATEUSER,CREATETIME,ISMAKEOUT,MAKEOUTUSER,MAKEOUTTIME,CORPID,LINENUM,GID_LINK FROM [wms_settlement_detail_temp] where CREATEUSER='" + strUserID + "' and CORPID='" + strCompanyID + "'" + strCondition;
var sortstring = DatasetSort.Getsortstring(sort);
if (!string.IsNullOrEmpty(sortstring))
{
strSql2 += " order by " + sortstring;
}
else
{
strSql2 += " order by CUSTOMERNAME,BLNO,WMSNO,LINENUM,OUTBSNO,STARTBILLINGDATE,FEEGRADE";
}
return SetMblnoDetailData(strSql2);
}
private static List<RptWmsSettlementDetailEntity> SetMblnoDetailData(String strSql)
{
var headList = new List<RptWmsSettlementDetailEntity>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
RptWmsSettlementDetailEntity data = new RptWmsSettlementDetailEntity();
#region Set DB data to Object
data.GID = Convert.ToString(reader["GID"]);
data.WMSNO = Convert.ToString(reader["WMSNO"]);
data.OUTBSNO = Convert.ToString(reader["OUTBSNO"]);
data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]);
data.STORAGENAME = Convert.ToString(reader["STORAGENAME"]);
data.GOODSNAME = Convert.ToString(reader["GOODSNAME"]);
data.GOODSMODEL = Convert.ToString(reader["GOODSMODEL"]);
data.GOODSSTANDARD = Convert.ToString(reader["GOODSSTANDARD"]);
data.GOODSGRADE = Convert.ToString(reader["GOODSGRADE"]);
data.WMSDATE = Convert.ToString(reader["WMSDATE"]);
data.DODATE = Convert.ToString(reader["DODATE"]);
data.WMSDAYS = Convert.ToInt32(reader["WMSDAYS"]);
data.STARTBILLINGDATE_OUT = Convert.ToString(reader["STARTBILLINGDATE_OUT"]);
data.ENDBILLINGDATE_OUT = Convert.ToString(reader["ENDBILLINGDATE_OUT"]);
data.GOODSOUTFEE = Convert.ToDecimal(reader["GOODSOUTFEE"]);
data.GOODSPACK = Convert.ToDecimal(reader["GOODSPACK"]);
data.GOODSPACKACTUAL = Convert.ToDecimal(reader["GOODSPACKACTUAL"]);
data.GOODSPACKPFSL = Convert.ToDecimal(reader["GOODSPACKPFSL"]);
data.GOODSPACKSTOCK = Convert.ToDecimal(reader["GOODSPACKSTOCK"]);
data.GOODSRKSL = Convert.ToDecimal(reader["GOODSRKSL"]);
data.GOODSRKSLACTUAL = Convert.ToDecimal(reader["GOODSRKSLACTUAL"]);
data.GOODSPFSL = Convert.ToDecimal(reader["GOODSPFSL"]);
data.GOODSSTOCK = Convert.ToDecimal(reader["GOODSSTOCK"]);
data.CHARGEUNIT = Convert.ToString(reader["CHARGEUNIT"]);
data.REMARK_OUT = Convert.ToString(reader["REMARK_OUT"]);
data.FEETYPE = Convert.ToInt32(reader["FEETYPE"]);
data.FEEGRADE = Convert.ToInt32(reader["FEEGRADE"]);
data.FEESCALE = Convert.ToInt32(reader["FEESCALE"]);
data.FEEPRICE = Convert.ToDecimal(reader["FEEPRICE"]);
data.STARTBILLINGDATE = Convert.ToString(reader["STARTBILLINGDATE"]);
data.ENDBILLINGDATE = Convert.ToString(reader["ENDBILLINGDATE"]);
data.FEEDAYS = Convert.ToInt32(reader["FEEDAYS"]);
data.OUTFEE = Convert.ToDecimal(reader["OUTFEE"]);
data.ARFEE = Convert.ToDecimal(reader["ARFEE"]);
data.CREATEUSER = Convert.ToString(reader["CREATEUSER"]);
if (reader["CREATETIME"] != DBNull.Value)
data.MAKEOUTTIME = Convert.ToDateTime(reader["CREATETIME"]);
data.ISMAKEOUT = Convert.ToBoolean(reader["ISMAKEOUT"]);
data.MAKEOUTUSER = Convert.ToString(reader["MAKEOUTUSER"]);
if (reader["MAKEOUTTIME"] != DBNull.Value)
data.MAKEOUTTIME = Convert.ToDateTime(reader["MAKEOUTTIME"]);
data.CORPID = Convert.ToString(reader["CORPID"]);
data.LINENUM = Convert.ToInt32(reader["LINENUM"]);
data.GID_LINK = Convert.ToString(reader["GID_LINK"]);
#endregion
//20220420 锦亿鹏鸿需求
//对于 未出库 且仓储日期0的那条数据 不想看到
if (string.IsNullOrWhiteSpace(data.OUTBSNO) && data.WMSDAYS == 0)
continue;
headList.Add(data);
}
reader.Close();
}
return headList;
}
#endregion
#region Inquery 仓储结算清单明细表(wms_settlement_detail)根据提单号查询
static public List<RptWmsSettlementDetailEntity> GetMblnoDetailListEdit(string strCondition, string strUserID, string strCompanyID, string sort = null)
{
string strSql2 = "SELECT GID,BLNO,CUSTOMNO,CONTRACTNO,WMSNO,OUTBSNO,CUSTOMERNAME,STORAGENAME,GOODSNAME,GOODSMODEL,GOODSSTANDARD,GOODSGRADE,dbo.trimdate(WMSDATE) as WMSDATE,dbo.trimdate(DODATE) as DODATE,WMSDAYS,dbo.trimdate(STARTBILLINGDATE_OUT) as STARTBILLINGDATE_OUT,dbo.trimdate(ENDBILLINGDATE_OUT) as ENDBILLINGDATE_OUT,GOODSOUTFEE,GOODSPACK,GOODSPACKACTUAL,GOODSPACKPFSL,GOODSPACKSTOCK,GOODSRKSL,GOODSRKSLACTUAL,GOODSPFSL,GOODSSTOCK,CHARGEUNIT,REMARK_OUT,FEETYPE,FEEGRADE,FEESCALE,FEEPRICE,dbo.trimdate(STARTBILLINGDATE) as STARTBILLINGDATE,dbo.trimdate(ENDBILLINGDATE) as ENDBILLINGDATE,FEEDAYS,OUTFEE,[ARFEE],CREATEUSER,CREATETIME,ISMAKEOUT,MAKEOUTUSER,MAKEOUTTIME,CORPID,LINENUM,GID_LINK FROM wms_settlement_detail where CORPID='" + strCompanyID + "' and ISDELETE=0" + strCondition;//CREATEUSER='" + strUserID + "' and
var sortstring = DatasetSort.Getsortstring(sort);
if (!string.IsNullOrEmpty(sortstring))
{
strSql2 += " order by " + sortstring;
}
else
{
strSql2 += " order by CUSTOMERNAME,BLNO,WMSNO,LINENUM,OUTBSNO,STARTBILLINGDATE,FEEGRADE";
}
return SetMblnoDetailData(strSql2);
}
#endregion
#region Inquery 明细表 wms_settlement_detail
static public List<RptWmsSettlementDetailEntity> GetDataDetailListEdit(string strCondition, string strUserID, string strCompanyID, string sort = null)
{
string strSql = "SELECT newid() as GID,DUIGID,DUINO,BLNO,CUSTOMNO,CONTRACTNO,CUSTOMERNAME,GOODSNAME FROM wms_settlement_detail where CORPID='" + strCompanyID + "' and ISDELETE=0";// CREATEUSER='" + strUserID + "' and
if (!string.IsNullOrEmpty(strCondition))
{
strSql += " and " + strCondition;
}
strSql += "group by DUIGID,DUINO,BLNO,CUSTOMNO,CONTRACTNO,CUSTOMERNAME,GOODSNAME,CREATEUSER,CORPID,ISDELETE";
//
var sortstring = DatasetSort.Getsortstring(sort);
if (!string.IsNullOrEmpty(sortstring))
{
strSql += " order by " + sortstring;
}
else
{
strSql += " order by BLNO,CUSTOMERNAME";
}
return SetFeeData(strSql);
}
#endregion
#region 获取List合计数
//获取List合计数
public static List<RptWmsSettlementDetailEntity> GetDataListSum(string tablename, string condition, string strUserID, string strCompanyID, string hjname)
{
string strSql = "SELECT newid() as GID, '" + hjname + "'as CUSTOMERNAME, isnull(sum(OUTFEE),0) as OUTFEE, isnull(sum(ARFEE),0) as ARFEE FROM " + tablename + " where CORPID='" + strCompanyID + "' and ISDELETE=0 " + condition + " group by CREATEUSER,CORPID,DUIGID";//CREATEUSER='" + strUserID + "' and
return SetDataSum(strSql);
}
//实体类
public static List<RptWmsSettlementDetailEntity> SetDataSum(String strSql)
{
var headList = new List<RptWmsSettlementDetailEntity>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql))
{
while (reader.Read())
{
var data = new RptWmsSettlementDetailEntity();
#region Set DB data to Object
data.GID = Convert.ToString(reader["GID"]);
data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]);
data.OUTFEE = Convert.ToDecimal(reader["OUTFEE"]);
data.ARFEE = Convert.ToDecimal(reader["ARFEE"]);
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
#endregion
#region Delete 删除主表
public static DBResult Delete_Main(string gids, string strUserID, string strCompanyID)
{
DBResult result = new DBResult();
if (gids.Trim() == "")
{
result.Success = false;
result.Message = "没有输入任何需要修改的数据";
}
gids = "'" + gids.Trim().Replace(",","','") + "'";
//
Database db = DatabaseFactory.CreateDatabase();
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
{
try
{
#region 判断【ch_fee】中的费用状态，是否允许删除
string sbool = "0";
T_ALL_DA T_ALL_DA = new T_ALL_DA();
string ls = "select count(gid) nums from ch_fee where WMSOUTBSNO in (" + gids + ") and (ISINVOICE=1 or AUDITSTATUS=1 or ORDERINVOICE<>0.00 or DEBITNO is not null or FEESTATUS<>1)";
sbool = T_ALL_DA.GetStrSQL("nums", ls);
if (sbool.Trim() == "0")
{
ls = "select count(gid) nums from ch_fee_do where feeid in (select gid from ch_fee where WMSOUTBSNO in (" + gids + "))";
sbool = T_ALL_DA.GetStrSQL("nums", ls);
if (sbool.Trim() != "0")
{
sqlTran.Rollback();
result.Success = false;
result.Message = "有“已申请开票、已开发票或已对帐”的费用，不允许删除，请重新操作！";
return result;
}
}
else
{
sqlTran.Rollback();
result.Success = false;
result.Message = "有“已申请开票、已开发票、已对帐”的费用，不允许删除，请重新操作！";
return result;
}
#endregion
#region 判断是否有下一个阶段汇总的数据
string strError = "";
int iNum = 0;
string strSql = "select *,STARTBILLINGDATE_MAIN=(select top 1 STARTBILLINGDATE from wms_settlement_list where gid=wms_settlement_detail.DUIGID),ENDBILLINGDATE_MAIN=(select top 1 ENDBILLINGDATE from wms_settlement_list where gid=wms_settlement_detail.DUIGID) from wms_settlement_detail where ISDELETE=0 and DUIGID in (" + gids + ")";
DataSet ds = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, strSql, null);
if (ds != null)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
strSql = "select count(*) from wms_settlement_detail where BLNO='" + dr["BLNO"].ToString().Trim() + "' and DUIGID in (select gid from wms_settlement_list where STARTBILLINGDATE>'" + dr["ENDBILLINGDATE_MAIN"].ToString().Trim() + "')";
object statusObj = SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, strSql, null);
if (statusObj != null)
{
iNum = int.Parse(statusObj.ToString().Trim());
}
if (iNum > 0)
{
strError += "、" + dr["BLNO"].ToString().Trim();
}
}
}
if (strError.Trim() != "")
{
strError = strError.Trim().Substring(1);
result.Success = false;
result.Message = "提单号" + strError + "已经有下一个阶段汇总的数据，因此不允许删除此账单！";
return result;
}
#endregion
#region 把【ch_fee】中WMSOUTBSNO与【仓储结算主表wms_settlement_list】GID关联的数据删除
strSql = "delete from ch_fee where WMSOUTBSNO in (" + gids + ")";
int existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
#region 把【仓储结算关联表wms_settlement_date】中数据【ISDELETE】设为【1】
strSql = "update wms_settlement_date set ISDELETE=1,DELETEUSER='" + strUserID + "',DELETETIME=getdate() where DUIGID in (" + gids + ")";
existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
#region 把【仓储结算明细表wms_settlement_detail】中数据【ISDELETE】设为【1】
strSql = "update wms_settlement_detail set ISDELETE=1,DELETEUSER='" + strUserID + "',DELETETIME=getdate() where DUIGID in (" + gids + ")";
existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
#region 把【仓储结算主表wms_settlement_list】中数据【ISDELETE】设为【1】
strSql = "update wms_settlement_list set ISDELETE=1,DELETEUSER='" + strUserID + "',DELETETIME=getdate() where gid in (" + gids + ")";
existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
sqlTran.Commit();
}
catch (Exception)
{
sqlTran.Rollback();
result.Success = false;
result.Message = "删除出现错误，请重试或联系系统管理员";
return result;
}
finally
{
SqlHelper.CloseConnection();
sqlTran.Dispose();
}
}
result.Success = true;
result.Message = "删除数据成功";
return result;
}
#endregion
#region Delete 删除明细表
public static DBResult Delete_Detail(RptWmsSettlementDetailEntity model, string DUIGID, string strBLNOs, string strUserID)
{
DBResult result = new DBResult();
if (model == null)
{
result.Success = false;
result.Message = "没有输入任何需要修改的数据";
}
List<ModelObjectBase> dataList = new List<ModelObjectBase>();
dataList.Add(model);
//
string strError = "";
Database db = DatabaseFactory.CreateDatabase();
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
{
try
{
#region 判断【ch_fee】中的费用状态，是否允许删除
//string sbool = "0";
//T_ALL_DA T_ALL_DA = new T_ALL_DA();
//string ls = "select count(gid) nums from ch_fee where WMSOUTBSNO=(select top 1 GID_LINK from wms_settlement_detail where ISDELETE=0 " + strBLNOs + ") and (ISINVOICE=1 or AUDITSTATUS=1 or ORDERINVOICE<>0.00 or DEBITNO is not null or FEESTATUS<>1)";
//sbool = T_ALL_DA.GetStrSQL("nums", ls);
//if (sbool.Trim() == "0")
//{
// ls = "select count(gid) nums from ch_fee_do where feeid in (select gid from ch_fee where WMSOUTBSNO=(select top 1 GID_LINK from wms_settlement_detail where ISDELETE=0 " + strBLNOs + "))";
// sbool = T_ALL_DA.GetStrSQL("nums", ls);
// if (sbool.Trim() != "0")
// {
// sqlTran.Rollback();
// result.Success = false;
// result.Message = "有“已申请开票、已开发票或已对帐”的费用，不允许删除，请重新操作！";
// return result;
// }
//}
//else
//{
// sqlTran.Rollback();
// result.Success = false;
// result.Message = "有“已申请开票、已开发票、已对帐”的费用，不允许删除，请重新操作！";
// return result;
//}
#endregion
#region 判断是否有下一个阶段汇总的数据
int iNum = 0;
string strSql = "select *,STARTBILLINGDATE_MAIN=(select top 1 STARTBILLINGDATE from wms_settlement_list where gid=wms_settlement_detail.DUIGID),ENDBILLINGDATE_MAIN=(select top 1 ENDBILLINGDATE from wms_settlement_list where gid=wms_settlement_detail.DUIGID) from wms_settlement_detail where ISDELETE=0 " + strBLNOs;
DataSet ds = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, strSql, null);
if (ds != null)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
strSql = "select count(*) from wms_settlement_detail where BLNO='" + dr["BLNO"].ToString().Trim() + "' and DUIGID in (select gid from wms_settlement_list where STARTBILLINGDATE>'" + dr["ENDBILLINGDATE_MAIN"].ToString().Trim() + "')";
object statusObj = SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, strSql, null);
if (statusObj != null)
{
iNum = int.Parse(statusObj.ToString().Trim());
}
if (iNum > 0)
{
strError += "、" + dr["BLNO"].ToString().Trim();
}
}
}
#endregion
#region 把【ch_fee】中与【仓储结算明细表[wms_settlement_detail]】中关联的数据删除，因为添加锁定功能，则暂时屏蔽
//string strSql = "delete from ch_fee where WMSOUTBSNO in (select gid from wms_settlement_detail where 1=1 " + DUIGID + strBLNOs + ")";
//int existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
#region 把【仓储结算关联表wms_settlement_date】中数据【ISDELETE】设为【1】
strSql = "update wms_settlement_date set ISDELETE=1,DELETEUSER='" + strUserID + "',DELETETIME=getdate() where 1=1" + DUIGID + " and LINKGID in (select GID_LINK from wms_settlement_detail where ISDELETE=0 " + strBLNOs + ")";
int existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
#region 把【仓储结算明细表wms_settlement_detail】中数据【ISDELETE】设为【1】
strSql = "update wms_settlement_detail set ISDELETE=1,DELETEUSER='" + strUserID + "',DELETETIME=getdate() where 1=1 " + DUIGID + strBLNOs;
existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
sqlTran.Commit();
}
catch (Exception)
{
sqlTran.Rollback();
result.Success = false;
result.Message = "删除出现错误，请重试或联系系统管理员";
return result;
}
finally
{
SqlHelper.CloseConnection();
sqlTran.Dispose();
}
}
if (strError.Trim() != "")
{
strError = strError.Trim().Substring(1);
result.Success = false;
result.Message = "删除数据成功，但部分提单号" + strError + "已经有下一个阶段汇总的数据不允许删除！";
return result;
}
else
{
result.Success = true;
result.Message = "删除数据成功";
return result;
}
}
#endregion
#region 费用入账
public static DBResult setislock(string gids, string strUserID, string strCompanyID, string strDEPTNAME)
{
DBResult result = new DBResult();
if (gids.Trim() == "")
{
result.Success = false;
result.Message = "没有输入任何需要修改的数据";
}
#region 权限操作范围
RangeDA RangeDA = new EntityDA.RangeDA();
string strRange = RangeDA.GetOPERATERANGE(strUserID.Trim(), "modRptWmsSettlementListIndex");//仓储结算清单列表
if (strRange.Trim().Equals("0"))//全部
{
strRange = "";
}
else if (strRange.Trim().Equals("1"))//分公司
{
strRange = RangeDA.GetRptWmsSettlementListIndexCompany(strCompanyID.Trim());
}
else if (strRange.Trim().Equals("2"))//部门
{
strRange = RangeDA.GetRptWmsSettlementListIndexDEPT(strCompanyID, strUserID, strDEPTNAME);
}
else if (strRange.Trim().Equals("3"))//个人
{
strRange = RangeDA.GetRptWmsSettlementListIndexPerson(strCompanyID, strUserID);
}
else if (strRange.Trim().Equals("4"))//无
{
strRange = " and 1<0";
}
else//空
{
strRange = " and 1<0";
}
#endregion
//
Database db = DatabaseFactory.CreateDatabase();
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
{
try
{
gids = "'" + gids.Trim().Replace(",","','") + "'";
#region 从【仓储结算明细临时表wms_settlement_detail】中提取数据，按提单号循环插入ch_Fee，因为添加锁定功能，则暂时屏蔽
string strSql = "insert into ch_fee(GID,BSNO,FEETYPE,FEENAME,CUSTOMERNAME,UNIT,UNITPRICE,QUANTITY,AMOUNT,EXCHANGERATE,CURRENCY,COMMISSIONRATE,FEEFRT,REMARK,ISADVANCEDPAY,ENTEROPERATOR,ENTERDATE,FEESTATUS,WMSOUTBSNO,LINENUM,[TAXRATE],[NOTAXAMOUNT],[TAX],[ORDERSETTLEMENT],[TAXUNITPRICE],[ORDERINVSETTLEMENT]) "
+ "select newid() as GID,bsno=(select top 1 bsno from V_op_bill where mblno=[wms_settlement_detail].blno),1,'仓储费',CUSTOMERNAME,'单票',sum(isnull(OUTFEE,0)),1,sum(isnull(OUTFEE,0)),1,'RMB',0,'PP',((select top 1 CONVERT(char(10), STARTBILLINGDATE, 20) AS STARTBILLINGDATE from wms_settlement_detail where blno=wms_settlement_detail.blno and STARTBILLINGDATE is not null and CORPID='" + strCompanyID + "' and ISLOCK=0 and ISDELETE=0 and isnull(OUTFEE,0)<>0 and DUIGID in (" + gids + ")" + strRange + " order by STARTBILLINGDATE)+'至'+(select top 1 CONVERT(char(10), ENDBILLINGDATE, 20) AS ENDBILLINGDATE from wms_settlement_detail where blno=wms_settlement_detail.blno and ENDBILLINGDATE is not null and CORPID='" + strCompanyID + "' and ISLOCK=0 and ISDELETE=0 and isnull(OUTFEE,0)<>0 and DUIGID in (" + gids + ")" + strRange + " order by ENDBILLINGDATE desc)+'仓储结算清单'+DUINO+'”中的单票仓储费汇总'),0,'" + strUserID + "',getdate(),1,DUIGID,LINENUM=(select top 1 LINENUM=isnull((select top 1 LINENUM from ch_fee where bsno=(select top 1 bsno from v_op_bill where mblno=[wms_settlement_detail].blno) and feetype=1 order by LINENUM desc),0)+1),0,sum(isnull(OUTFEE,0)),0,0,sum(isnull(OUTFEE,0)),0"
+ " from [wms_settlement_detail] where CORPID='" + strCompanyID + "' and ISLOCK=0 and ISDELETE=0 and isnull(OUTFEE,0)<>0 and DUIGID in (" + gids + ")" + strRange + " group by blno,CUSTOMERNAME,CREATEUSER,CORPID,DUINO,DUIGID";//CREATEUSER='" + strUserID + "' and
int existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
#region 把【仓储结算明细表wms_settlement_detail】中的指定数据锁定
strSql = "update wms_settlement_detail set ISLOCK=1,LOCKUSER='" + strUserID + "',LOCKTIME=getdate() where ISLOCK=0 and DUIGID in (" + gids + ")" + strRange;
existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
#region 把【仓储结算主表wms_settlement_detail_temp】中的指定数据锁定
strSql = "update wms_settlement_list set ISLOCK=1,LOCKUSER='" + strUserID + "',LOCKTIME=getdate() where ISLOCK=0 and gid in (" + gids + ")" + strRange;
existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
sqlTran.Commit();
}
catch (Exception)
{
sqlTran.Rollback();
result.Success = false;
result.Message = "操作出现错误，请重试或联系系统管理员";
return result;
}
finally
{
SqlHelper.CloseConnection();
sqlTran.Dispose();
}
}
result.Success = true;
result.Message = "操作成功！（注意如果用户权限不够，将不予以入账！）";
return result;
}
#endregion
#region 取消入账
public static DBResult setnotlock(string gids, string strUserID, string strCompanyID, string strDEPTNAME)
{
DBResult result = new DBResult();
if (gids.Trim() == "")
{
result.Success = false;
result.Message = "没有输入任何需要修改的数据";
}
#region 权限操作范围
RangeDA RangeDA = new EntityDA.RangeDA();
string strRange = RangeDA.GetOPERATERANGE(strUserID.Trim(), "modRptWmsSettlementListIndex");//仓储结算清单列表
if (strRange.Trim().Equals("0"))//全部
{
strRange = "";
}
else if (strRange.Trim().Equals("1"))//分公司
{
strRange = RangeDA.GetRptWmsSettlementListIndexCompany(strCompanyID.Trim());
}
else if (strRange.Trim().Equals("2"))//部门
{
strRange = RangeDA.GetRptWmsSettlementListIndexDEPT(strCompanyID, strUserID, strDEPTNAME);
}
else if (strRange.Trim().Equals("3"))//个人
{
strRange = RangeDA.GetRptWmsSettlementListIndexPerson(strCompanyID, strUserID);
}
else if (strRange.Trim().Equals("4"))//无
{
strRange = " and 1<0";
}
else//空
{
strRange = " and 1<0";
}
#endregion
//
Database db = DatabaseFactory.CreateDatabase();
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
{
try
{
gids = "'" + gids.Trim().Replace(",", "','") + "'";
#region 判断【ch_fee】中的费用状态，是否允许取消入账
string sbool = "0";
T_ALL_DA T_ALL_DA = new T_ALL_DA();
//string ls = "select count(gid) nums from ch_fee where WMSOUTBSNO in (select gid from wms_settlement_list where ISLOCK=1 and gid in(" + gids + ") " + strRange + ") and (ISINVOICE=1 or AUDITSTATUS=1 or ORDERINVOICE<>0.00 or DEBITNO is not null or FEESTATUS<>1)";
string ls = "select count(gid) nums from ch_fee where WMSOUTBSNO in (select gid from wms_settlement_list where ISLOCK=1 and gid in(" + gids + ") " + strRange + ") and (ISINVOICE=1 or ORDERINVOICE<>0.00 or DEBITNO is not null or (FEESTATUS<>1 and FEESTATUS<>6))";
sbool = T_ALL_DA.GetStrSQL("nums", ls);
if (sbool.Trim() == "0")
{
ls = "select count(gid) nums from ch_fee_do where feeid in (select gid from ch_fee where WMSOUTBSNO in (select gid from wms_settlement_list where ISLOCK=1 and gid in(" + gids + ") " + strRange + "))";
sbool = T_ALL_DA.GetStrSQL("nums", ls);
if (sbool.Trim() != "0")
{
sqlTran.Rollback();
result.Success = false;
result.Message = "有“已申请开票、已开发票或已对帐”的费用，不允许取消入账，请重新操作！";
return result;
}
}
else
{
sqlTran.Rollback();
result.Success = false;
result.Message = "有“已申请开票、已开发票、已对帐”的费用，不允许取消入账，请重新操作！";
return result;
}
#endregion
#region 从【仓储结算明细临时表wms_settlement_detail】中提取数据，按提单号循环插入ch_Fee，因为添加锁定功能，则暂时屏蔽
string strSql = "delete from ch_fee where WMSOUTBSNO in (select gid from wms_settlement_list where ISLOCK=1 and gid in(" + gids + ") " + strRange + ")";
int existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
#region 把【仓储结算明细表wms_settlement_detail】中的指定数据锁定
strSql = "update wms_settlement_detail set ISLOCK=0,LOCKUSER='" + strUserID + "',LOCKTIME=getdate() where ISLOCK=1 and DUIGID in (" + gids + ")" + strRange;
existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
#region 把【仓储结算主表wms_settlement_detail_temp】中的指定数据锁定
strSql = "update wms_settlement_list set ISLOCK=0,LOCKUSER='" + strUserID + "',LOCKTIME=getdate() where ISLOCK=1 and gid in (" + gids + ")" + strRange;
existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null);
#endregion
sqlTran.Commit();
}
catch (Exception)
{
sqlTran.Rollback();
result.Success = false;
result.Message = "入账出现错误，请重试或联系系统管理员";
return result;
}
finally
{
SqlHelper.CloseConnection();
sqlTran.Dispose();
}
}
result.Success = true;
result.Message = "入账数据成功";
return result;
}
#endregion
#region 计算仓储费
/// <summary>
/// 计算仓储费
/// </summary>
/// <param name="sqlTran">事务</param>
/// <param name="strSTARTBILLINGDATE">开始计费日期</param>
/// <param name="strOLDCUSTFEEDATE">上家承担仓储期限</param>
/// <param name="strINBSNO">出库明细关联的入库单wms或wms_in的BSNO</param>
/// <param name="strCUSTOMERNAME">出库主表的客户名称</param>
/// <param name="outnums">出库量</param>
/// <param name="strASSOCIATEDNO">关联wms_out_detail的GID</param>
/// <param name="strFEETYPE">区别费用收、付类型</param>
/// <param name="strUserID">登陆用户GID</param>
/// <param name="strShowName">登陆用户名</param>
/// <param name="sBillno">出库单号或其他单据号</param>
/// <param name="ck_jsfee">是否计算仓储费</param>
/// <returns></returns>
public static String getWmsRate2(SqlTransaction sqlTran, string strSTARTBILLINGDATE, string strOLDCUSTFEEDATE, string strINBSNO, string strCUSTOMERNAME, Decimal outnums, string strASSOCIATEDNO, int strFEETYPE, string strUserID, string strShowName, string sBillno, bool ck_jsfee, bool isKuCun)
{
string sR = "";
int iState = 0;
int result = 0;
string Sqlstr = "";
#region 计算仓储费
WmsOutDetailDA WmsOutDetailDA = new EntityDA.WmsOutDetailDA();
IList<WmsRateDetailEntity> tempWmsRateDetailEntities = new List<WmsRateDetailEntity>();
int totalfeedays = 0;//总计费区间仓储天数
int feedays = 0;//区间仓储数
int prescale = 0;//总计费区间仓储数
int feeMonth = 0;//区间仓储月数
Decimal sumfee = 0;//总仓储费
string strSTARTBILLINGDATE2 = strSTARTBILLINGDATE;//按区间计费的开始计费日期
#region 删除费率出库明细表的区间仓储费
Sqlstr = "delete from [wms_settlement_detail_rate_detail_temp] where ASSOCIATEDNO = '" + strASSOCIATEDNO.Trim() + "' and FEETYPE=" + strFEETYPE;
iState = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, Sqlstr, null);
if (iState < 0)
{
sqlTran.Rollback();
result = -9;
//Clearh();
return result.ToString();
}
#endregion
if (ck_jsfee)//是否计算仓储费
{
#region 如果入库有免仓储期，则出库时插入费率出库明细表
//按仓储费率计算仓储费
Sqlstr = "SELECT top 1 * FROM wms where GID='" + strINBSNO + "' or gid in (select ASSOCIATEDNO from wms_in where gid='" + strINBSNO + "')";
DataSet dsrate = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, Sqlstr, null);
//if (dsrate != null)
//{
// if (dsrate.Tables[0].Rows.Count > 0)
// {
// string sAP = dsrate.Tables[0].Rows[0]["FREESTORAGEPERIOD"].ToString().Trim();
// if (strFEETYPE == 2)
// {
// sAP = dsrate.Tables[0].Rows[0]["FREESTORAGEPERIODAP"].ToString().Trim();
// }
// if (sAP != "0")
// {
// WmsRateDetailEntity model3 = new WmsRateDetailEntity();
// model3.FEEPRICE = 0;
// model3.GOODSPFSL = outnums;
// model3.OUTFEE = 0;
// model3.FEEUNIT = dsrate.Tables[0].Rows[0]["CHARGEUNIT"].ToString().Trim() + "天";
// model3.STARTBILLINGDATE = DateTime.Parse(dsrate.Tables[0].Rows[0]["WMSDATE"].ToString().Trim());
// DateTime dt = DateTime.Parse(dsrate.Tables[0].Rows[0]["STARTBILLINGDATE"].ToString().Trim()).AddDays(-1);
// if (DateTime.Parse(strOLDCUSTFEEDATE) < dt)
// {
// dt = DateTime.Parse(strOLDCUSTFEEDATE);
// }
// model3.ENDBILLINGDATE = dt;
// model3.FEESCALE = int.Parse(WmsOutDetailDA.GetDaysCount("day", model3.STARTBILLINGDATE.ToString().Trim(), dt.ToString().Trim()).ToString());
// model3.FEEGRADE = 0;
// model3.REMARK = sBillno;
// model3.CREATEUSER = strUserID;
// model3.MODIFIEDUSER = strUserID;
// model3.ASSOCIATEDNO = strASSOCIATEDNO;
// model3.CUSTOMERNAME = strCUSTOMERNAME;
// model3.FEETYPE = strFEETYPE;
// tempWmsRateDetailEntities.Add(model3);
// }
// }
//}
#endregion
feedays = WmsOutDetailDA.GetDaysCount("day", strSTARTBILLINGDATE, strOLDCUSTFEEDATE.Trim());//WmsOutEntity.DODATE.ToString() 出库-开始计费日期+1
if (feedays > 0)
{
totalfeedays = feedays;
//按仓储费率计算仓储费
Sqlstr = "SELECT GID,FEEGRADE,FEETYPE,FEESCALE,FEEPRICE,ADDPRICE,FEEUNIT,convert(char(10),ENDBILLINGDATE,20) AS ENDBILLINGDATE,REMARK,CREATEUSER,CREATETIME,BSNO,CAPPRICE FROM wms_rate where (BSNO='" + strINBSNO + "' or BSNO in (select ASSOCIATEDNO from wms_in where gid='" + strINBSNO + "')) and FEETYPE=" + strFEETYPE + " order by FEEGRADE ";
dsrate = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, Sqlstr, null);
if (dsrate != null)
{
if (dsrate.Tables[0].Rows.Count > 0)
{
int nowscale = 0;//区间计费区间(仓储月数/天数)
Decimal price = 0;//计费单价
Decimal gradefee = 0;//区间仓储费
int nowgrade = 1;
for (int i = 0; i < dsrate.Tables[0].Rows.Count; i++)
{
if (isKuCun)
{
if (DateTime.Parse(strSTARTBILLINGDATE) > DateTime.Parse(dsrate.Tables[0].Rows[i]["ENDBILLINGDATE"].ToString()))
{
continue;
}
}
WmsRateDetailEntity model = new WmsRateDetailEntity();
//nowgrade = int.Parse(dsrate.Tables[0].Rows[i]["FEEGRADE"].ToString());//计费等级
nowscale = int.Parse(dsrate.Tables[0].Rows[i]["FEESCALE"].ToString());//计费区间
price = Decimal.Parse(dsrate.Tables[0].Rows[i]["FEEPRICE"].ToString());//计费单价
Decimal ADDPRICE = Decimal.Parse(dsrate.Tables[0].Rows[i]["ADDPRICE"].ToString());//增值价
Decimal CAPPRICE = Decimal.Parse(dsrate.Tables[0].Rows[i]["CAPPRICE"].ToString());//
string FEEUNIT = dsrate.Tables[0].Rows[i]["FEEUNIT"].ToString();//计费单位
string ENDBILLINGDATE = dsrate.Tables[0].Rows[i]["ENDBILLINGDATE"].ToString();//结束计费日期
//计算区间是按天或按月
string DorM = "day";
if (FEEUNIT.IndexOf("月") > -1)
{
DorM = "month";
}
#region 按区间计算仓储费
feedays = WmsOutDetailDA.GetDaysCount(DorM, strSTARTBILLINGDATE2, strOLDCUSTFEEDATE.Trim());//总仓储月数
if (feedays > nowscale) //总仓储月数 > 区间计费区间(仓储月数/天数)
{
feeMonth = WmsOutDetailDA.GetDaysCount(DorM, strSTARTBILLINGDATE2, ENDBILLINGDATE.Trim());//i区间仓储月数
if (feeMonth > nowscale)//i区间仓储月数 > 区间计费区间(仓储月数/天数)
{
int sjMonth = 0;//实际仓储月数
int sjMonth2 = 0;//实际仓储月数，如果是最后一条的判断数
if (feedays > feeMonth)//总仓储月数 > i区间仓储月数
{
sjMonth = nowscale;// feeMonth;// (feeMonth - nowscale);//实际仓储月数
sjMonth2 = feeMonth;//实际仓储月数，如果是最后一条的判断数
if ((i + 1) == dsrate.Tables[0].Rows.Count)//即最后一条时，以后的时间都按最后一条取值
{
if (ADDPRICE <= 0)//增值价>0，则求增值费率
{
sjMonth = feedays;//(feedays - nowscale);//实际仓储月数
ENDBILLINGDATE = strOLDCUSTFEEDATE;
}
sjMonth2 = feedays;// (feedays - nowscale);//实际仓储月数
}
}
else//总仓储月数 <= i区间仓储月数
{
sjMonth = feedays;//(feedays - nowscale);//实际仓储月数
ENDBILLINGDATE = strOLDCUSTFEEDATE;
}
//
#region 增值价>0，则求增值费率
if (ADDPRICE > 0)//增值价>0，则求增值费率
{
//int idiv = (int)(sjMonth / nowscale);
//int imod = (int)(sjMonth % nowscale);
int idiv = (int)(sjMonth2 / nowscale);
int imod = (int)(sjMonth2 % nowscale);
Decimal dprice = price;//计费单价
for (int j = 0; j < idiv; j++)//循环求增值费率
{
//--- BEGIN --- 如果是月则计算开始和结束日期之间的天数
DateTime dtSTARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
DateTime dtENDBILLINGDATE = dtSTARTBILLINGDATE.AddDays(nowscale - 1);
int dtDays = sjMonth;// nowscale;
string FEEUNIT2 = FEEUNIT;
if (FEEUNIT.IndexOf("月") > -1)
{
dtENDBILLINGDATE = dtSTARTBILLINGDATE.AddMonths(nowscale).AddDays(-1);
if (dtENDBILLINGDATE > DateTime.Parse(ENDBILLINGDATE))//strOLDCUSTFEEDATE
{
if ((i + 1) == dsrate.Tables[0].Rows.Count && dtENDBILLINGDATE > DateTime.Parse(strOLDCUSTFEEDATE))//即最后一条时，以后的时间都按最后一条取值
{
dtENDBILLINGDATE = DateTime.Parse(strOLDCUSTFEEDATE);
}
else
{
dtENDBILLINGDATE = DateTime.Parse(ENDBILLINGDATE);//strOLDCUSTFEEDATE
}
}
FEEUNIT2 = FEEUNIT.Trim().Substring(0, 1) + "天";
dtDays = WmsOutDetailDA.GetDaysCount("day", dtSTARTBILLINGDATE.ToString().Trim(), dtENDBILLINGDATE.ToString().Trim());//按月的区间天数
}
//--- END ---
gradefee = outnums * dprice * dtDays;// nowscale;//出库量*计费单价*实际仓储月数
sumfee += gradefee;
prescale += dtDays;//nowscale;//累计天数
//
WmsRateDetailEntity model1 = new WmsRateDetailEntity();
model1.FEEPRICE = dprice;
model1.GOODSPFSL = outnums;
model1.FEESCALE = dtDays;// nowscale;
model1.OUTFEE = gradefee;
model1.FEEUNIT = FEEUNIT2;
model1.STARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
model1.ENDBILLINGDATE = dtENDBILLINGDATE;
//model1.ENDBILLINGDATE = model1.STARTBILLINGDATE.AddDays(nowscale - 1);
//if (FEEUNIT.IndexOf("月") > -1)
//{
// DateTime dt = model1.STARTBILLINGDATE.AddMonths(nowscale).AddDays(-1);
// model1.ENDBILLINGDATE = dt;
// if (dt > DateTime.Parse(strOLDCUSTFEEDATE))
// {
// model1.ENDBILLINGDATE = DateTime.Parse(strOLDCUSTFEEDATE);
// }
//}
model1.FEEGRADE = nowgrade;
model1.REMARK = sBillno;
model1.CREATEUSER = strUserID;
model1.MODIFIEDUSER = strUserID;
model1.ASSOCIATEDNO = strASSOCIATEDNO;
model1.CUSTOMERNAME = strCUSTOMERNAME;
model1.FEETYPE = strFEETYPE;
tempWmsRateDetailEntities.Add(model1);
//
nowgrade++;
if (CAPPRICE != 0)
{
if (dprice < CAPPRICE)
{
dprice += ADDPRICE;
}
else
{
dprice = CAPPRICE;
}
}
else
{
dprice += ADDPRICE;
}
strSTARTBILLINGDATE2 = (model1.ENDBILLINGDATE.AddDays(1)).ToString();//按区间计费的开始计费日期
}
//
if (imod > 0)//增值费率余数大于0，则实际仓储月份等于imod
{
//--- BEGIN --- 如果是月则计算开始和结束日期之间的天数
DateTime dtSTARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
DateTime dtENDBILLINGDATE = dtSTARTBILLINGDATE.AddDays(imod - 1);
int dtDays = imod;// nowscale;
string FEEUNIT2 = FEEUNIT;
if (FEEUNIT.IndexOf("月") > -1)
{
dtENDBILLINGDATE = dtSTARTBILLINGDATE.AddMonths(imod).AddDays(-1);
if (dtENDBILLINGDATE > DateTime.Parse(ENDBILLINGDATE))//strOLDCUSTFEEDATE
{
if ((i + 1) == dsrate.Tables[0].Rows.Count && dtENDBILLINGDATE > DateTime.Parse(strOLDCUSTFEEDATE))//即最后一条时，以后的时间都按最后一条取值
{
dtENDBILLINGDATE = DateTime.Parse(strOLDCUSTFEEDATE);
}
else
{
dtENDBILLINGDATE = DateTime.Parse(ENDBILLINGDATE);//strOLDCUSTFEEDATE
}
}
FEEUNIT2 = FEEUNIT.Trim().Substring(0, 1) + "天";
dtDays = WmsOutDetailDA.GetDaysCount("day", dtSTARTBILLINGDATE.ToString().Trim(), dtENDBILLINGDATE.ToString().Trim());//按月的区间天数
}
//--- END ---
//dprice += ADDPRICE;
gradefee = outnums * dprice * dtDays;// imod;//出库量*计费单价*实际仓储月数
sumfee += gradefee;
prescale += dtDays;//imod;//累计天数
//
model.FEEPRICE = dprice;
model.GOODSPFSL = outnums;
model.FEESCALE = dtDays;//imod;
model.OUTFEE = gradefee;
model.FEEUNIT = FEEUNIT2;
model.STARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
model.ENDBILLINGDATE = dtENDBILLINGDATE;
//model.ENDBILLINGDATE = model.STARTBILLINGDATE.AddDays(imod - 1);
//if (FEEUNIT.IndexOf("月") > -1)
//{
// DateTime dt = model.STARTBILLINGDATE.AddMonths(imod).AddDays(-1);
// model.ENDBILLINGDATE = dt;
// if (dt > DateTime.Parse(strOLDCUSTFEEDATE))
// {
// model.ENDBILLINGDATE = DateTime.Parse(strOLDCUSTFEEDATE);
// }
//}
model.FEEGRADE = nowgrade;
model.REMARK = sBillno;
model.CREATEUSER = strUserID;
model.MODIFIEDUSER = strUserID;
model.ASSOCIATEDNO = strASSOCIATEDNO;
model.CUSTOMERNAME = strCUSTOMERNAME;
model.FEETYPE = strFEETYPE;
tempWmsRateDetailEntities.Add(model);
//
nowgrade++;
strSTARTBILLINGDATE2 = (model.ENDBILLINGDATE.AddDays(1)).ToString();//按区间计费的开始计费日期
}
}
else
{
//--- BEGIN --- 如果是月则计算开始和结束日期之间的天数
DateTime dtSTARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
DateTime dtENDBILLINGDATE = DateTime.Parse(ENDBILLINGDATE);
int dtDays = sjMonth;// nowscale;
string FEEUNIT2 = FEEUNIT;
if (FEEUNIT.IndexOf("月") > -1)
{
FEEUNIT2 = FEEUNIT.Trim().Substring(0, 1) + "天";
dtDays = WmsOutDetailDA.GetDaysCount("day", dtSTARTBILLINGDATE.ToString().Trim(), dtENDBILLINGDATE.ToString().Trim());//按月的区间天数
}
//--- END ---
gradefee = outnums * price * dtDays;// sjMonth;//出库量*计费单价*实际仓储月数
sumfee += gradefee;
prescale += dtDays;//sjMonth;//累计天数
//
model.FEEPRICE = price;
model.GOODSPFSL = outnums;
model.FEESCALE = dtDays;//sjMonth;
model.OUTFEE = gradefee;
model.FEEUNIT = FEEUNIT2;
model.STARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
model.ENDBILLINGDATE = DateTime.Parse(ENDBILLINGDATE);
model.FEEGRADE = nowgrade;
model.REMARK = sBillno;
model.CREATEUSER = strUserID;
model.MODIFIEDUSER = strUserID;
model.ASSOCIATEDNO = strASSOCIATEDNO;
model.CUSTOMERNAME = strCUSTOMERNAME;
model.FEETYPE = strFEETYPE;
tempWmsRateDetailEntities.Add(model);
//
nowgrade++;
strSTARTBILLINGDATE2 = (DateTime.Parse(ENDBILLINGDATE).AddDays(1)).ToString();//按区间计费的开始计费日期
}
#endregion
//结束费率区间的循环
if (feedays <= feeMonth)//总仓储月数 <= i区间仓储月数
{
break;//结束费率区间的循环
}
}
else//i区间仓储月数 <= 区间计费区间(仓储月数/天数)
{
int sjMonth = 0;//实际仓储月数
int sjMonth2 = 0;//实际仓储月数，如果是最后一条的判断数
if (feedays > feeMonth)//总仓储月数 > i区间仓储月数
{
sjMonth = feeMonth;//实际仓储月数
sjMonth2 = feeMonth;//实际仓储月数，如果是最后一条的判断数
if ((i + 1) == dsrate.Tables[0].Rows.Count)//即最后一条时，以后的时间都按最后一条取值
{
if (ADDPRICE <= 0)//增值价>0，则求增值费率
{
sjMonth = feedays;// (feedays - nowscale);//实际仓储月数
ENDBILLINGDATE = strOLDCUSTFEEDATE;
}
sjMonth2 = feedays;// (feedays - nowscale);//实际仓储月数，如果是最后一条的判断数
}
}
else//总仓储月数 <= i区间仓储月数
{
sjMonth = feedays;//(feedays - nowscale);//实际仓储月数
ENDBILLINGDATE = strOLDCUSTFEEDATE;
}
//
#region 增值价>0，则求增值费率
if (ADDPRICE > 0)//增值价>0，则求增值费率
{
//int idiv = (int)(sjMonth / nowscale);
//int imod = (int)(sjMonth % nowscale);
int idiv = (int)(sjMonth2 / nowscale);
int imod = (int)(sjMonth2 % nowscale);
Decimal dprice = price;//计费单价
for (int j = 0; j < idiv; j++)//循环求增值费率
{
//--- BEGIN --- 如果是月则计算开始和结束日期之间的天数
DateTime dtSTARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
DateTime dtENDBILLINGDATE = dtSTARTBILLINGDATE.AddDays(nowscale - 1);
int dtDays = sjMonth;// nowscale;
string FEEUNIT2 = FEEUNIT;
if (FEEUNIT.IndexOf("月") > -1)
{
dtENDBILLINGDATE = dtSTARTBILLINGDATE.AddMonths(nowscale).AddDays(-1);
if (dtENDBILLINGDATE > DateTime.Parse(strOLDCUSTFEEDATE))
{
dtENDBILLINGDATE = DateTime.Parse(strOLDCUSTFEEDATE);
}
FEEUNIT2 = FEEUNIT.Trim().Substring(0, 1) + "天";
dtDays = WmsOutDetailDA.GetDaysCount("day", dtSTARTBILLINGDATE.ToString().Trim(), dtENDBILLINGDATE.ToString().Trim());//按月的区间天数
}
//--- END ---
gradefee = outnums * dprice * dtDays;// nowscale;//出库量*计费单价*实际仓储月数
sumfee += gradefee;
prescale += dtDays;//nowscale;//累计天数
//
WmsRateDetailEntity model1 = new WmsRateDetailEntity();
model1.FEEPRICE = dprice;
model1.GOODSPFSL = outnums;
model1.FEESCALE = dtDays;//nowscale;
model1.OUTFEE = gradefee;
model1.FEEUNIT = FEEUNIT2;
model1.STARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
model1.ENDBILLINGDATE = dtENDBILLINGDATE;
//model1.ENDBILLINGDATE = model1.STARTBILLINGDATE.AddDays(nowscale - 1);
//if (FEEUNIT.IndexOf("月") > -1)
//{
// DateTime dt = model1.STARTBILLINGDATE.AddMonths(nowscale).AddDays(-1);
// model1.ENDBILLINGDATE = dt;
// if (dt > DateTime.Parse(strOLDCUSTFEEDATE))
// {
// model1.ENDBILLINGDATE = DateTime.Parse(strOLDCUSTFEEDATE);
// }
//}
model1.FEEGRADE = nowgrade;
model1.REMARK = sBillno;
model1.CREATEUSER = strUserID;
model1.MODIFIEDUSER = strUserID;
model1.ASSOCIATEDNO = strASSOCIATEDNO;
model1.CUSTOMERNAME = strCUSTOMERNAME;
model1.FEETYPE = strFEETYPE;
tempWmsRateDetailEntities.Add(model1);
//
nowgrade++;
if (CAPPRICE != 0)
{
if (dprice < CAPPRICE)
{
dprice += ADDPRICE;
}
else
{
dprice = CAPPRICE;
}
}
else
{
dprice += ADDPRICE;
}
strSTARTBILLINGDATE2 = (model1.ENDBILLINGDATE.AddDays(1)).ToString();//按区间计费的开始计费日期
}
//
if (imod > 0)//增值费率余数大于0，则实际仓储月份等于imod
{
//--- BEGIN --- 如果是月则计算开始和结束日期之间的天数
DateTime dtSTARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
DateTime dtENDBILLINGDATE = dtSTARTBILLINGDATE.AddDays(imod - 1);
int dtDays = imod;// nowscale;
string FEEUNIT2 = FEEUNIT;
if (FEEUNIT.IndexOf("月") > -1)
{
dtENDBILLINGDATE = dtSTARTBILLINGDATE.AddMonths(imod).AddDays(-1);
if (dtENDBILLINGDATE > DateTime.Parse(strOLDCUSTFEEDATE))
{
dtENDBILLINGDATE = DateTime.Parse(strOLDCUSTFEEDATE);
}
FEEUNIT2 = FEEUNIT.Trim().Substring(0, 1) + "天";
dtDays = WmsOutDetailDA.GetDaysCount("day", dtSTARTBILLINGDATE.ToString().Trim(), dtENDBILLINGDATE.ToString().Trim());//按月的区间天数
}
//--- END ---
//dprice += ADDPRICE;
gradefee = outnums * dprice * dtDays;// imod;//出库量*计费单价*实际仓储月数
sumfee += gradefee;
prescale += dtDays;//imod;//累计天数
//
model.FEEPRICE = dprice;
model.GOODSPFSL = outnums;
model.FEESCALE = dtDays;//imod;
model.OUTFEE = gradefee;
model.FEEUNIT = FEEUNIT2;
model.STARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
model.ENDBILLINGDATE = dtENDBILLINGDATE;
//model.ENDBILLINGDATE = model.STARTBILLINGDATE.AddDays(imod - 1);
//if (FEEUNIT.IndexOf("月") > -1)
//{
// DateTime dt = model.STARTBILLINGDATE.AddMonths(imod).AddDays(-1);
// model.ENDBILLINGDATE = dt;
// if (dt > DateTime.Parse(strOLDCUSTFEEDATE))
// {
// model.ENDBILLINGDATE = DateTime.Parse(strOLDCUSTFEEDATE);
// }
//}
model.FEEGRADE = nowgrade;
model.REMARK = sBillno;
model.CREATEUSER = strUserID;
model.MODIFIEDUSER = strUserID;
model.ASSOCIATEDNO = strASSOCIATEDNO;
model.CUSTOMERNAME = strCUSTOMERNAME;
model.FEETYPE = strFEETYPE;
tempWmsRateDetailEntities.Add(model);
//
nowgrade++;
strSTARTBILLINGDATE2 = (model.ENDBILLINGDATE.AddDays(1)).ToString();//按区间计费的开始计费日期
}
}
else
{
//--- BEGIN --- 如果是月则计算开始和结束日期之间的天数
DateTime dtSTARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
DateTime dtENDBILLINGDATE = DateTime.Parse(ENDBILLINGDATE);
int dtDays = sjMonth;//nowscale;
string FEEUNIT2 = FEEUNIT;
if (FEEUNIT.IndexOf("月") > -1)
{
FEEUNIT2 = FEEUNIT.Trim().Substring(0, 1) + "天";
dtDays = WmsOutDetailDA.GetDaysCount("day", dtSTARTBILLINGDATE.ToString().Trim(), dtENDBILLINGDATE.ToString().Trim());//按月的区间天数
}
//--- END ---
gradefee = outnums * price * dtDays;//sjMonth;//出库量*计费单价*实际仓储月数
sumfee += gradefee;
prescale += dtDays;//sjMonth;//累计天数
//
model.FEEPRICE = price;
model.GOODSPFSL = outnums;
model.FEESCALE = dtDays;//sjMonth;
model.OUTFEE = gradefee;
model.FEEUNIT = FEEUNIT2;
model.STARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
model.ENDBILLINGDATE = DateTime.Parse(ENDBILLINGDATE);
model.FEEGRADE = nowgrade;
model.REMARK = sBillno;
model.CREATEUSER = strUserID;
model.MODIFIEDUSER = strUserID;
model.ASSOCIATEDNO = strASSOCIATEDNO;
model.CUSTOMERNAME = strCUSTOMERNAME;
model.FEETYPE = strFEETYPE;
tempWmsRateDetailEntities.Add(model);
//
nowgrade++;
strSTARTBILLINGDATE2 = (DateTime.Parse(ENDBILLINGDATE).AddDays(1)).ToString();//按区间计费的开始计费日期
}
#endregion
//结束费率区间的循环
if (feedays <= feeMonth)//总仓储月数 <= i区间仓储月数
{
break;//结束费率区间的循环
}
}
}
else//总仓储月数 <= 区间计费区间(仓储月数/天数)
{
//--- BEGIN --- 如果是月则计算开始和结束日期之间的天数
DateTime dtSTARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
DateTime dtENDBILLINGDATE = DateTime.Parse(strOLDCUSTFEEDATE);
int dtDays = feedays;
string FEEUNIT2 = FEEUNIT;
if (FEEUNIT.IndexOf("月") > -1)
{
FEEUNIT2 = FEEUNIT.Trim().Substring(0, 1) + "天";
dtDays = WmsOutDetailDA.GetDaysCount("day", dtSTARTBILLINGDATE.ToString().Trim(), dtENDBILLINGDATE.ToString().Trim());//按月的区间天数
}
//--- END ---
gradefee = outnums * price * dtDays;//feedays;//出库量*计费单价*仓储区间（//仓储天数-上一个计费区间(0)）
sumfee += gradefee;
prescale += dtDays;//feedays;//累计天数
//
model.FEEPRICE = price;
model.GOODSPFSL = outnums;
model.FEESCALE = dtDays;//feedays;
model.OUTFEE = gradefee;
model.FEEUNIT = FEEUNIT2;
model.STARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
model.ENDBILLINGDATE = DateTime.Parse(strOLDCUSTFEEDATE);
model.FEEGRADE = nowgrade;
model.REMARK = sBillno;
model.CREATEUSER = strUserID;
model.MODIFIEDUSER = strUserID;
model.ASSOCIATEDNO = strASSOCIATEDNO;
model.CUSTOMERNAME = strCUSTOMERNAME;
model.FEETYPE = strFEETYPE;
tempWmsRateDetailEntities.Add(model);
//
nowgrade++;
strSTARTBILLINGDATE2 = (DateTime.Parse(strOLDCUSTFEEDATE).AddDays(1)).ToString();//按区间计费的开始计费日期
break;//结束费率区间的循环
}
#endregion
}
}
else
{
//仓储费率没有设定
//sqlTran.Rollback();
//result = -2;
//Page.ClientScript.RegisterStartupScript(this.GetType(), "key0", "<script>alert('仓储费率没有设置');</script>");
//Clearh();
//return result;
totalfeedays = 0;
feedays = 0;
sumfee = 0;
}
}
else
{
totalfeedays = 0;
feedays = 0;
sumfee = 0;
}
}
else
{
totalfeedays = 0;
feedays = 0;
sumfee = 0;
}
//
#region 按区间计算仓储费，插入费率出库明细表
if (tempWmsRateDetailEntities != null)//是否有要入库的仓储费
{
if (tempWmsRateDetailEntities.Count > 0)
{
foreach (WmsRateDetailEntity model2 in tempWmsRateDetailEntities)
{
Sqlstr = String.Format("insert into [wms_settlement_detail_rate_detail_temp](GID,FEEPRICE,GOODSPFSL,OUTFEE,REMARK,CREATEUSER,CREATETIME,ASSOCIATEDNO,CUSTOMERNAME,FEETYPE,FEEGRADE,STARTBILLINGDATE,ENDBILLINGDATE,FEESCALE,FEEUNIT,MODIFIEDUSER)"
+ " values(newid(),'{0}','{1}','{2}','{3}','{4}',getdate(),'{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}')"
, model2.FEEPRICE, model2.GOODSPFSL, model2.OUTFEE, model2.REMARK, model2.CREATEUSER, model2.ASSOCIATEDNO, model2.CUSTOMERNAME, model2.FEETYPE, model2.FEEGRADE, model2.STARTBILLINGDATE, model2.ENDBILLINGDATE, model2.FEESCALE, model2.FEEUNIT, model2.MODIFIEDUSER);
iState = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, Sqlstr, null);
if (iState != 1)
{
sqlTran.Rollback();
result = -1;
//Clearh();
return result.ToString();
}
}
}
}
#endregion
}
else
{
tempWmsRateDetailEntities = null;
totalfeedays = 0;
feedays = 0;
sumfee = 0;
}
#endregion
sR = sumfee.ToString() + "，" + totalfeedays.ToString();
return sR;
}
#endregion
#region 计算仓储费_按时间段判断
/// <summary>
/// 计算仓储费
/// </summary>
/// <param name="sqlTran">事务</param>
/// <param name="strSTARTBILLINGDATE">开始计费日期</param>
/// <param name="strOLDCUSTFEEDATE">上家承担仓储期限</param>
/// <param name="strINBSNO">出库明细关联的入库单wms或wms_in的BSNO</param>
/// <param name="strCUSTOMERNAME">出库主表的客户名称</param>
/// <param name="outnums">出库量</param>
/// <param name="strASSOCIATEDNO">关联wms_out_detail的GID</param>
/// <param name="strFEETYPE">区别费用收、付类型</param>
/// <param name="strUserID">登陆用户GID</param>
/// <param name="strShowName">登陆用户名</param>
/// <param name="sBillno">出库单号或其他单据号</param>
/// <param name="ck_jsfee">是否计算仓储费</param>
/// <returns></returns>
public static void getWmsRate(SqlTransaction sqlTran, string strSTARTBILLINGDATE, string strOLDCUSTFEEDATE, string strINBSNO, string strCUSTOMERNAME, Decimal outnums, string strASSOCIATEDNO, int strFEETYPE, string strUserID, string strShowName, string sBillno, bool ck_jsfee, bool isKuCun)
{
int iState = 0;
int result = 0;
string Sqlstr = "";
#region 计算仓储费
WmsOutDetailDA WmsOutDetailDA = new EntityDA.WmsOutDetailDA();
IList<WmsRateDetailEntity> tempWmsRateDetailEntities = new List<WmsRateDetailEntity>();
int feedays = 0;//区间仓储数；
int feeMonth = 0;//区间仓储月数
string strSTARTBILLINGDATE2 = strSTARTBILLINGDATE;//按区间计费的开始计费日期
#region 删除费率出库明细表的区间仓储费
Sqlstr = "delete from [wms_settlement_detail_rate_detail_temp] where ASSOCIATEDNO = '" + strASSOCIATEDNO.Trim() + "' and FEETYPE=" + strFEETYPE;
iState = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, Sqlstr, null);
if (iState < 0)
{
sqlTran.Rollback();
result = -9;
//Clearh();
//return result.ToString();
}
#endregion
if (ck_jsfee)//是否计算仓储费
{
#region 如果入库有免仓储期，则出库时插入费率出库明细表
//按仓储费率计算仓储费
Sqlstr = "SELECT top 1 * FROM wms where GID='" + strINBSNO + "' or gid in (select ASSOCIATEDNO from wms_in where gid='" + strINBSNO + "')";
DataSet dsrate = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, Sqlstr, null);
#endregion
feedays = WmsOutDetailDA.GetDaysCount("day", strSTARTBILLINGDATE, strOLDCUSTFEEDATE.Trim());//WmsOutEntity.DODATE.ToString() 出库-开始计费日期+1
if (feedays > 0)
{
//按仓储费率计算仓储费
Sqlstr = "SELECT GID,FEEGRADE,FEETYPE,FEESCALE,FEEPRICE,ADDPRICE,FEEUNIT,convert(char(10),ENDBILLINGDATE,20) AS ENDBILLINGDATE,REMARK,CREATEUSER,CREATETIME,BSNO,CAPPRICE FROM wms_rate where (BSNO='" + strINBSNO + "' or BSNO in (select ASSOCIATEDNO from wms_in where gid='" + strINBSNO + "')) and FEETYPE=" + strFEETYPE + " order by FEEGRADE ";
dsrate = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, Sqlstr, null);
if (dsrate != null)
{
if (dsrate.Tables[0].Rows.Count > 0)
{
#region 基本信息
int iCount = dsrate.Tables[0].Rows.Count;
int nowscale = 0;//区间计费区间(仓储月数/天数)
Decimal price = 0;//计费单价
Decimal gradefee = 0;//区间仓储费
int nowgrade = 1;
#endregion
for (int i = 0; i < iCount; i++)
{
#region 基本信息
WmsRateDetailEntity model = new WmsRateDetailEntity();
//nowgrade = int.Parse(dsrate.Tables[0].Rows[i]["FEEGRADE"].ToString());//计费等级
nowscale = int.Parse(dsrate.Tables[0].Rows[i]["FEESCALE"].ToString());//计费区间
price = Decimal.Parse(dsrate.Tables[0].Rows[i]["FEEPRICE"].ToString());//计费单价
Decimal ADDPRICE = Decimal.Parse(dsrate.Tables[0].Rows[i]["ADDPRICE"].ToString());//增值价
Decimal CAPPRICE = Decimal.Parse(dsrate.Tables[0].Rows[i]["CAPPRICE"].ToString());//封顶价
string FEEUNIT = dsrate.Tables[0].Rows[i]["FEEUNIT"].ToString();//计费单位
string ENDBILLINGDATE = dsrate.Tables[0].Rows[i]["ENDBILLINGDATE"].ToString();//结束计费日期
//计算区间是按天或按月
string DorM = "day";
if (FEEUNIT.IndexOf("月") > -1)
{
DorM = "month";
}
#endregion
if (i == (iCount - 1))//为最后一条费率
{
feeMonth = WmsOutDetailDA.GetDaysCount(DorM, strSTARTBILLINGDATE2, strOLDCUSTFEEDATE.Trim());//根据结束日期计算出的区间天数
if (feeMonth > 0)
{
if (ADDPRICE > 0)
{
#region 有增值价
Decimal dprice = price;//计费单价
if (isKuCun && DateTime.Parse(strSTARTBILLINGDATE2) > DateTime.Parse(dsrate.Tables[0].Rows[i]["ENDBILLINGDATE"].ToString()))
{
#region 有增值价时，如果开始计费日期大于当前区间的结束计费日期，则需计算开始计费的单价
//int cedays = WmsOutDetailDA.GetDaysCount(DorM, dsrate.Tables[0].Rows[i]["ENDBILLINGDATE"].ToString(), strSTARTBILLINGDATE2.Trim());//与要计费的开始计费日期的差额
int cedays = WmsOutDetailDA.GetDaysCount(DorM, dsrate.Tables[0].Rows[i]["ENDBILLINGDATE"].ToString(), DateTime.Parse(strSTARTBILLINGDATE2).AddDays(- 1).ToString());//与要计费的开始计费日期的差额
int cediv = (int)(cedays / nowscale);
int cemod = (int)(cedays % nowscale);
int ceday = nowscale - cemod;
if (cemod != 0)
{
cediv = cediv + 1;
}
for (int n = 0; n < cediv; n++)
{
dprice = dprice + ADDPRICE;//计费单价
}
if (CAPPRICE != 0)//如果封顶价不为0
{
if (dprice > CAPPRICE)
{
dprice = CAPPRICE;
}
}
#endregion
#region 如果有与要计费的开始计费日期的差额，则天数差额也要计算
if (ceday > 0)
{
DateTime dtSTARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
//DateTime dtENDBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2).AddDays(ceday - 1);
DateTime dtENDBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2).AddDays(ceday);
//if (DateTime.Parse(dsrate.Tables[0].Rows[i]["ENDBILLINGDATE"].ToString()) < dtENDBILLINGDATE)
if (DateTime.Parse(strOLDCUSTFEEDATE.Trim()) < dtENDBILLINGDATE)
{
dtENDBILLINGDATE = DateTime.Parse(strOLDCUSTFEEDATE.Trim());
}
//int dtDays = ceday;
int dtDays = WmsOutDetailDA.GetDaysCount(DorM, dtSTARTBILLINGDATE.ToString(), dtENDBILLINGDATE.ToString());//与要计费的开始计费日期的差额
//判断单位和最后日期
string FEEUNIT2 = FEEUNIT;
if (FEEUNIT.IndexOf("月") > -1)
{
dtENDBILLINGDATE = dtSTARTBILLINGDATE.AddMonths(nowscale).AddDays(-1);
if (dtENDBILLINGDATE > DateTime.Parse(ENDBILLINGDATE))
{
if (dtENDBILLINGDATE > DateTime.Parse(strOLDCUSTFEEDATE))//即最后一条时，以后的时间都按最后一条取值
{
dtENDBILLINGDATE = DateTime.Parse(strOLDCUSTFEEDATE);
}
else
{
dtENDBILLINGDATE = DateTime.Parse(ENDBILLINGDATE);//strOLDCUSTFEEDATE
}
}
FEEUNIT2 = FEEUNIT.Trim().Substring(0, 1) + "天";
dtDays = WmsOutDetailDA.GetDaysCount("day", dtSTARTBILLINGDATE.ToString().Trim(), dtENDBILLINGDATE.ToString().Trim());//按月的区间天数
}
//
gradefee = outnums * dprice * dtDays;//出库量*计费单价*实际仓储月数
//
WmsRateDetailEntity model1 = new WmsRateDetailEntity();
model1.FEEPRICE = dprice;
model1.GOODSPFSL = outnums;
model1.FEESCALE = dtDays;// nowscale;
model1.OUTFEE = gradefee;
model1.FEEUNIT = FEEUNIT2;
model1.STARTBILLINGDATE = dtSTARTBILLINGDATE;
model1.ENDBILLINGDATE = dtENDBILLINGDATE;
model1.FEEGRADE = nowgrade;
model1.REMARK = sBillno;
model1.CREATEUSER = strUserID;
model1.MODIFIEDUSER = strUserID;
model1.ASSOCIATEDNO = strASSOCIATEDNO;
model1.CUSTOMERNAME = strCUSTOMERNAME;
model1.FEETYPE = strFEETYPE;
tempWmsRateDetailEntities.Add(model1);
//
if (CAPPRICE != 0)
{
if (dprice < CAPPRICE)
{
dprice += ADDPRICE;
}
else
{
dprice = CAPPRICE;
}
}
else
{
dprice += ADDPRICE;
}
//
nowgrade++;
strSTARTBILLINGDATE2 = (model1.ENDBILLINGDATE.AddDays(1)).ToString();//按区间计费的开始计费日期
}
#endregion
}
if (DateTime.Parse(strSTARTBILLINGDATE2) <= DateTime.Parse(strOLDCUSTFEEDATE))
{
#region 有增值价时，如果开始计费日期大于当前区间的结束计费日期，则需计算开始计费的单价
if (DateTime.Parse(strSTARTBILLINGDATE2) <= DateTime.Parse(ENDBILLINGDATE.Trim()))
{
if (DateTime.Parse(strOLDCUSTFEEDATE) < DateTime.Parse(ENDBILLINGDATE))
{
ENDBILLINGDATE = strOLDCUSTFEEDATE;
}
//
int days_1 = WmsOutDetailDA.GetDaysCount(DorM, strSTARTBILLINGDATE2, ENDBILLINGDATE.Trim());//根据结束日期计算出区间内的天数
int sjMonth2 = WmsOutDetailDA.GetDaysCount(DorM, ENDBILLINGDATE, strOLDCUSTFEEDATE.Trim()) - 1;//根据结束日期计算出区间外的天数
int idiv = (int)(sjMonth2 / nowscale) + 1;
int imod = (int)(sjMonth2 % nowscale);
#region 循环求增值费率
for (int j = 0; j < idiv; j++)
{
DateTime dtSTARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
DateTime dtENDBILLINGDATE = dtSTARTBILLINGDATE.AddDays(nowscale - 1);
int dtDays = nowscale;
if (j == 0)
{
dtDays = days_1;
dtENDBILLINGDATE = dtSTARTBILLINGDATE.AddDays(days_1 - 1);
}
//判断单位和最后日期
string FEEUNIT2 = FEEUNIT;
if (FEEUNIT.IndexOf("月") > -1)
{
dtENDBILLINGDATE = dtSTARTBILLINGDATE.AddMonths(nowscale).AddDays(-1);
if (dtENDBILLINGDATE > DateTime.Parse(ENDBILLINGDATE))
{
if (dtENDBILLINGDATE > DateTime.Parse(strOLDCUSTFEEDATE))//即最后一条时，以后的时间都按最后一条取值
{
dtENDBILLINGDATE = DateTime.Parse(strOLDCUSTFEEDATE);
}
else
{
dtENDBILLINGDATE = DateTime.Parse(ENDBILLINGDATE);//strOLDCUSTFEEDATE
}
}
FEEUNIT2 = FEEUNIT.Trim().Substring(0, 1) + "天";
dtDays = WmsOutDetailDA.GetDaysCount("day", dtSTARTBILLINGDATE.ToString().Trim(), dtENDBILLINGDATE.ToString().Trim());//按月的区间天数
}
//
gradefee = outnums * dprice * dtDays;//出库量*计费单价*实际仓储月数
//
WmsRateDetailEntity model1 = new WmsRateDetailEntity();
model1.FEEPRICE = dprice;
model1.GOODSPFSL = outnums;
model1.FEESCALE = dtDays;// nowscale;
model1.OUTFEE = gradefee;
model1.FEEUNIT = FEEUNIT2;
model1.STARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
model1.ENDBILLINGDATE = dtENDBILLINGDATE;
model1.FEEGRADE = nowgrade;
model1.REMARK = sBillno;
model1.CREATEUSER = strUserID;
model1.MODIFIEDUSER = strUserID;
model1.ASSOCIATEDNO = strASSOCIATEDNO;
model1.CUSTOMERNAME = strCUSTOMERNAME;
model1.FEETYPE = strFEETYPE;
tempWmsRateDetailEntities.Add(model1);
//
if (CAPPRICE != 0)
{
if (dprice < CAPPRICE)
{
dprice += ADDPRICE;
}
else
{
dprice = CAPPRICE;
}
}
else
{
dprice += ADDPRICE;
}
//
nowgrade++;
strSTARTBILLINGDATE2 = (model1.ENDBILLINGDATE.AddDays(1)).ToString();//按区间计费的开始计费日期
}
#endregion
#region 循环求增值费率
if (imod > 0)//增值费率余数大于0，则实际仓储月份等于imod
{
DateTime dtSTARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
DateTime dtENDBILLINGDATE = dtSTARTBILLINGDATE.AddDays(imod - 1);
int dtDays = imod;
//判断单位
string FEEUNIT2 = FEEUNIT;
if (FEEUNIT.IndexOf("月") > -1)
{
dtENDBILLINGDATE = dtSTARTBILLINGDATE.AddMonths(imod).AddDays(-1);
if (dtENDBILLINGDATE > DateTime.Parse(ENDBILLINGDATE))//strOLDCUSTFEEDATE
{
if (dtENDBILLINGDATE > DateTime.Parse(strOLDCUSTFEEDATE))//即最后一条时，以后的时间都按最后一条取值
{
dtENDBILLINGDATE = DateTime.Parse(strOLDCUSTFEEDATE);
}
else
{
dtENDBILLINGDATE = DateTime.Parse(ENDBILLINGDATE);//strOLDCUSTFEEDATE
}
}
FEEUNIT2 = FEEUNIT.Trim().Substring(0, 1) + "天";
dtDays = WmsOutDetailDA.GetDaysCount("day", dtSTARTBILLINGDATE.ToString().Trim(), dtENDBILLINGDATE.ToString().Trim());//按月的区间天数
}
//
gradefee = outnums * dprice * dtDays;//出库量*计费单价*实际仓储月数
//
model.FEEPRICE = dprice;
model.GOODSPFSL = outnums;
model.FEESCALE = dtDays;
model.OUTFEE = gradefee;
model.FEEUNIT = FEEUNIT2;
model.STARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
model.ENDBILLINGDATE = dtENDBILLINGDATE;
model.FEEGRADE = nowgrade;
model.REMARK = sBillno;
model.CREATEUSER = strUserID;
model.MODIFIEDUSER = strUserID;
model.ASSOCIATEDNO = strASSOCIATEDNO;
model.CUSTOMERNAME = strCUSTOMERNAME;
model.FEETYPE = strFEETYPE;
tempWmsRateDetailEntities.Add(model);
//
nowgrade++;
strSTARTBILLINGDATE2 = (model.ENDBILLINGDATE.AddDays(1)).ToString();//按区间计费的开始计费日期
}
#endregion
}
else
{
if (DateTime.Parse(strOLDCUSTFEEDATE) > DateTime.Parse(ENDBILLINGDATE))
{
ENDBILLINGDATE = strOLDCUSTFEEDATE;
}
int sjMonth2 = WmsOutDetailDA.GetDaysCount(DorM, strSTARTBILLINGDATE2.Trim(), ENDBILLINGDATE);//根据结束日期计算出区间外的天数
//
//int idiv = (int)(feeMonth / nowscale);
//int imod = (int)(feeMonth % nowscale);
int idiv = (int)(sjMonth2 / nowscale);
int imod = (int)(sjMonth2 % nowscale);
#region 循环求增值费率
for (int j = 0; j < idiv; j++)
{
DateTime dtSTARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
DateTime dtENDBILLINGDATE = dtSTARTBILLINGDATE.AddDays(nowscale - 1);
int dtDays = nowscale;
//判断单位和最后日期
string FEEUNIT2 = FEEUNIT;
if (FEEUNIT.IndexOf("月") > -1)
{
dtENDBILLINGDATE = dtSTARTBILLINGDATE.AddMonths(nowscale).AddDays(-1);
if (dtENDBILLINGDATE > DateTime.Parse(ENDBILLINGDATE))
{
if (dtENDBILLINGDATE > DateTime.Parse(strOLDCUSTFEEDATE))//即最后一条时，以后的时间都按最后一条取值
{
dtENDBILLINGDATE = DateTime.Parse(strOLDCUSTFEEDATE);
}
else
{
dtENDBILLINGDATE = DateTime.Parse(ENDBILLINGDATE);//strOLDCUSTFEEDATE
}
}
FEEUNIT2 = FEEUNIT.Trim().Substring(0, 1) + "天";
dtDays = WmsOutDetailDA.GetDaysCount("day", dtSTARTBILLINGDATE.ToString().Trim(), dtENDBILLINGDATE.ToString().Trim());//按月的区间天数
}
//
gradefee = outnums * dprice * dtDays;//出库量*计费单价*实际仓储月数
//
WmsRateDetailEntity model1 = new WmsRateDetailEntity();
model1.FEEPRICE = dprice;
model1.GOODSPFSL = outnums;
model1.FEESCALE = dtDays;// nowscale;
model1.OUTFEE = gradefee;
model1.FEEUNIT = FEEUNIT2;
model1.STARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
model1.ENDBILLINGDATE = dtENDBILLINGDATE;
model1.FEEGRADE = nowgrade;
model1.REMARK = sBillno;
model1.CREATEUSER = strUserID;
model1.MODIFIEDUSER = strUserID;
model1.ASSOCIATEDNO = strASSOCIATEDNO;
model1.CUSTOMERNAME = strCUSTOMERNAME;
model1.FEETYPE = strFEETYPE;
tempWmsRateDetailEntities.Add(model1);
//
if (CAPPRICE != 0)
{
if (dprice < CAPPRICE)
{
dprice += ADDPRICE;
}
else
{
dprice = CAPPRICE;
}
}
else
{
dprice += ADDPRICE;
}
//
nowgrade++;
strSTARTBILLINGDATE2 = (model1.ENDBILLINGDATE.AddDays(1)).ToString();//按区间计费的开始计费日期
}
#endregion
#region 循环求增值费率
if (imod > 0)//增值费率余数大于0，则实际仓储月份等于imod
{
DateTime dtSTARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
DateTime dtENDBILLINGDATE = dtSTARTBILLINGDATE.AddDays(imod - 1);
int dtDays = imod;
//判断单位
string FEEUNIT2 = FEEUNIT;
if (FEEUNIT.IndexOf("月") > -1)
{
dtENDBILLINGDATE = dtSTARTBILLINGDATE.AddMonths(imod).AddDays(-1);
if (dtENDBILLINGDATE > DateTime.Parse(ENDBILLINGDATE))//strOLDCUSTFEEDATE
{
if (dtENDBILLINGDATE > DateTime.Parse(strOLDCUSTFEEDATE))//即最后一条时，以后的时间都按最后一条取值
{
dtENDBILLINGDATE = DateTime.Parse(strOLDCUSTFEEDATE);
}
else
{
dtENDBILLINGDATE = DateTime.Parse(ENDBILLINGDATE);//strOLDCUSTFEEDATE
}
}
FEEUNIT2 = FEEUNIT.Trim().Substring(0, 1) + "天";
dtDays = WmsOutDetailDA.GetDaysCount("day", dtSTARTBILLINGDATE.ToString().Trim(), dtENDBILLINGDATE.ToString().Trim());//按月的区间天数
}
//
gradefee = outnums * dprice * dtDays;//出库量*计费单价*实际仓储月数
//
model.FEEPRICE = dprice;
model.GOODSPFSL = outnums;
model.FEESCALE = dtDays;
model.OUTFEE = gradefee;
model.FEEUNIT = FEEUNIT2;
model.STARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
model.ENDBILLINGDATE = dtENDBILLINGDATE;
model.FEEGRADE = nowgrade;
model.REMARK = sBillno;
model.CREATEUSER = strUserID;
model.MODIFIEDUSER = strUserID;
model.ASSOCIATEDNO = strASSOCIATEDNO;
model.CUSTOMERNAME = strCUSTOMERNAME;
model.FEETYPE = strFEETYPE;
tempWmsRateDetailEntities.Add(model);
//
nowgrade++;
strSTARTBILLINGDATE2 = (model.ENDBILLINGDATE.AddDays(1)).ToString();//按区间计费的开始计费日期
}
#endregion
}
#endregion
}
#endregion
}
else
{
#region 没有增值价
gradefee = outnums * price * feeMonth;//出库量*计费单价*实际仓储月数
//
WmsRateDetailEntity model1 = new WmsRateDetailEntity();
model1.FEEPRICE = price;
model1.GOODSPFSL = outnums;
model1.FEESCALE = feeMonth;// nowscale;
model1.OUTFEE = gradefee;
model1.FEEUNIT = FEEUNIT;
model1.STARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
model1.ENDBILLINGDATE = DateTime.Parse(strOLDCUSTFEEDATE.Trim());
model1.FEEGRADE = nowgrade;
model1.REMARK = sBillno;
model1.CREATEUSER = strUserID;
model1.MODIFIEDUSER = strUserID;
model1.ASSOCIATEDNO = strASSOCIATEDNO;
model1.CUSTOMERNAME = strCUSTOMERNAME;
model1.FEETYPE = strFEETYPE;
tempWmsRateDetailEntities.Add(model1);
//
nowgrade++;
strSTARTBILLINGDATE2 = (model1.ENDBILLINGDATE.AddDays(1)).ToString();//按区间计费的开始计费日期
#endregion
}
}
}
else
{
#region 不是最后一条，因此不判断增值价
if (isKuCun && DateTime.Parse(strSTARTBILLINGDATE2) > DateTime.Parse(dsrate.Tables[0].Rows[i]["ENDBILLINGDATE"].ToString()))
{
continue;
}
//判断最后日期
if (DateTime.Parse(strOLDCUSTFEEDATE) < DateTime.Parse(ENDBILLINGDATE))
{
ENDBILLINGDATE = strOLDCUSTFEEDATE;
}
//
feeMonth = WmsOutDetailDA.GetDaysCount(DorM, strSTARTBILLINGDATE2, ENDBILLINGDATE.Trim());//根据结束日期计算出的区间天数
if (feeMonth > 0)
{
gradefee = outnums * price * feeMonth;//出库量*计费单价*实际仓储月数
//
WmsRateDetailEntity model1 = new WmsRateDetailEntity();
model1.FEEPRICE = price;
model1.GOODSPFSL = outnums;
model1.FEESCALE = feeMonth;// nowscale;
model1.OUTFEE = gradefee;
model1.FEEUNIT = FEEUNIT;
model1.STARTBILLINGDATE = DateTime.Parse(strSTARTBILLINGDATE2);
model1.ENDBILLINGDATE = DateTime.Parse(ENDBILLINGDATE.Trim());
model1.FEEGRADE = nowgrade;
model1.REMARK = sBillno;
model1.CREATEUSER = strUserID;
model1.MODIFIEDUSER = strUserID;
model1.ASSOCIATEDNO = strASSOCIATEDNO;
model1.CUSTOMERNAME = strCUSTOMERNAME;
model1.FEETYPE = strFEETYPE;
tempWmsRateDetailEntities.Add(model1);
//
nowgrade++;
strSTARTBILLINGDATE2 = (model1.ENDBILLINGDATE.AddDays(1)).ToString();//按区间计费的开始计费日期
}
#endregion
}
}
}
}
}
//
#region 按区间计算仓储费，插入费率出库明细表
if (tempWmsRateDetailEntities != null)//是否有要入库的仓储费
{
if (tempWmsRateDetailEntities.Count > 0)
{
foreach (WmsRateDetailEntity model2 in tempWmsRateDetailEntities)
{
Sqlstr = String.Format("insert into [wms_settlement_detail_rate_detail_temp](GID,FEEPRICE,GOODSPFSL,OUTFEE,REMARK,CREATEUSER,CREATETIME,ASSOCIATEDNO,CUSTOMERNAME,FEETYPE,FEEGRADE,STARTBILLINGDATE,ENDBILLINGDATE,FEESCALE,FEEUNIT,MODIFIEDUSER)"
+ " values(newid(),'{0}','{1}','{2}','{3}','{4}',getdate(),'{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}')"
, model2.FEEPRICE, model2.GOODSPFSL, model2.OUTFEE, model2.REMARK, model2.CREATEUSER, model2.ASSOCIATEDNO, model2.CUSTOMERNAME, model2.FEETYPE, model2.FEEGRADE, model2.STARTBILLINGDATE, model2.ENDBILLINGDATE, model2.FEESCALE, model2.FEEUNIT, model2.MODIFIEDUSER);
iState = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, Sqlstr, null);
if (iState != 1)
{
sqlTran.Rollback();
result = -1;
//Clearh();
//return result.ToString();
}
}
}
}
#endregion
}
else
{
tempWmsRateDetailEntities = null;
}
#endregion
}
#endregion
//
}
}
Reference in New Issue View Git Blame Copy Permalink