|
|
using System;
|
|
|
using System.Data;
|
|
|
using System.Text;
|
|
|
using System.Configuration;
|
|
|
using System.Web;
|
|
|
using System.Web.Security;
|
|
|
using System.Web.UI;
|
|
|
using System.Web.UI.HtmlControls;
|
|
|
using System.Web.UI.WebControls;
|
|
|
using System.Web.UI.WebControls.WebParts;
|
|
|
using DSWeb.Models;
|
|
|
using System.Data.SqlClient;
|
|
|
using System.Collections.Generic;
|
|
|
using System.Collections;
|
|
|
using WebSqlHelper;
|
|
|
using System.Linq;
|
|
|
using System.Xml.Linq;
|
|
|
|
|
|
namespace DSWeb.EntityDA
|
|
|
{
|
|
|
public class WmsDA
|
|
|
{
|
|
|
public WmsDA()
|
|
|
{
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 增加一条数据
|
|
|
/// </summary>
|
|
|
public int Add(WmsEntity model)
|
|
|
{
|
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
strSql.Append("insert into wms(");
|
|
|
strSql.Append("GID,WMSNO,WMSDATE,CUSTOMERNAME,WMSOP,STOREMAN,MODIFIEDUSER,MODIFIEDTIME,ISDELETE,DELETEUSER,DELETETIME,TRUCKNAME,GOODSPACK,GOODSPACKSTOCK,GOODSRKSL,GOODSSTOCK,ARFEE,APFEE,ASSOCIATEDNO,BLNO,FREESTORAGEPERIOD,CONTRACTNO,GOODSNAME,GOODSMODEL,CLIENTCODEOLD,CLIENTNAMEOLD,STORAGENAME,CHARGEUNIT,STARTBILLINGDATE,CUSTOMNO,CUSTNO,GOODSSTANDARD,GOODSPACKACTUAL,GOODSRKSLACTUAL,ARCLIENT,FREESTORAGEPERIODAP,STARTBILLINGDATEAP,ISCHANGE,CORPID,AREANAME,BANKVALUE,MARKETVALUE,BZTCHNO,CREATEUSER,CREATETIME,CLIENTBSNO");
|
|
|
strSql.Append(") values (");
|
|
|
strSql.Append("@GID,@WMSNO,@WMSDATE,@CUSTOMERNAME,@WMSOP,@STOREMAN,@MODIFIEDUSER,getdate(),0,'',getdate(),@TRUCKNAME,@GOODSPACK,@GOODSPACKSTOCK,@GOODSRKSL,@GOODSSTOCK,@ARFEE,@APFEE,@ASSOCIATEDNO,@BLNO,@FREESTORAGEPERIOD,@CONTRACTNO,@GOODSNAME,@GOODSMODEL,@CLIENTCODEOLD,@CLIENTNAMEOLD,@STORAGENAME,@CHARGEUNIT,@STARTBILLINGDATE,@CUSTOMNO,@CUSTNO,@GOODSSTANDARD,@GOODSPACKACTUAL,@GOODSRKSLACTUAL,@ARCLIENT,@FREESTORAGEPERIODAP,@STARTBILLINGDATEAP,@ISCHANGE,@CORPID,@AREANAME,@BANKVALUE,@MARKETVALUE,@BZTCHNO,@CREATEUSER,getdate(),@CLIENTBSNO");
|
|
|
strSql.Append(") ");
|
|
|
|
|
|
SqlParameter[] parameters = {
|
|
|
new SqlParameter("@GID", SqlDbType.VarChar,36) ,
|
|
|
new SqlParameter("@WMSNO", SqlDbType.VarChar,100) ,
|
|
|
new SqlParameter("@WMSDATE", SqlDbType.DateTime) ,
|
|
|
new SqlParameter("@CUSTOMERNAME", SqlDbType.VarChar,20) ,
|
|
|
new SqlParameter("@WMSOP", SqlDbType.VarChar,10) ,
|
|
|
new SqlParameter("@STOREMAN", SqlDbType.VarChar,10) ,
|
|
|
new SqlParameter("@MODIFIEDUSER", SqlDbType.VarChar,36) ,
|
|
|
new SqlParameter("@TRUCKNAME", SqlDbType.VarChar,20) ,
|
|
|
new SqlParameter("@GOODSPACK", SqlDbType.Decimal,9) ,
|
|
|
new SqlParameter("@GOODSPACKSTOCK", SqlDbType.Decimal,9) ,
|
|
|
new SqlParameter("@GOODSRKSL", SqlDbType.Decimal,9) ,
|
|
|
new SqlParameter("@GOODSSTOCK", SqlDbType.Decimal,9) ,
|
|
|
new SqlParameter("@ARFEE", SqlDbType.Decimal,9) ,
|
|
|
new SqlParameter("@APFEE", SqlDbType.Decimal,9) ,
|
|
|
new SqlParameter("@ASSOCIATEDNO", SqlDbType.VarChar,100),
|
|
|
new SqlParameter("@BLNO", SqlDbType.VarChar,100) ,
|
|
|
new SqlParameter("@FREESTORAGEPERIOD", SqlDbType.Int,4) ,
|
|
|
new SqlParameter("@CONTRACTNO", SqlDbType.VarChar,100),
|
|
|
new SqlParameter("@GOODSNAME", SqlDbType.VarChar,100) ,
|
|
|
new SqlParameter("@GOODSMODEL", SqlDbType.VarChar,100) ,
|
|
|
new SqlParameter("@CLIENTCODEOLD", SqlDbType.VarChar,20) ,
|
|
|
new SqlParameter("@CLIENTNAMEOLD", SqlDbType.VarChar,20) ,
|
|
|
new SqlParameter("@STORAGENAME", SqlDbType.VarChar,20) ,
|
|
|
new SqlParameter("@CHARGEUNIT", SqlDbType.VarChar,10) ,
|
|
|
new SqlParameter("@STARTBILLINGDATE", SqlDbType.DateTime) ,
|
|
|
new SqlParameter("@CUSTOMNO",SqlDbType.VarChar,20) ,
|
|
|
new SqlParameter("@CUSTNO",SqlDbType.VarChar,30) ,
|
|
|
new SqlParameter("@GOODSSTANDARD",SqlDbType.VarChar,100) ,
|
|
|
new SqlParameter("@GOODSPACKACTUAL", SqlDbType.Decimal,9) ,
|
|
|
new SqlParameter("@GOODSRKSLACTUAL", SqlDbType.Decimal,9) ,
|
|
|
new SqlParameter("@ARCLIENT", SqlDbType.VarChar,20) ,
|
|
|
new SqlParameter("@FREESTORAGEPERIODAP", SqlDbType.Int,4) ,
|
|
|
new SqlParameter("@STARTBILLINGDATEAP", SqlDbType.DateTime) ,
|
|
|
new SqlParameter("@ISCHANGE", SqlDbType.Bit) ,
|
|
|
new SqlParameter("@CORPID", SqlDbType.VarChar,36) ,
|
|
|
new SqlParameter("@AREANAME",SqlDbType.VarChar,100) ,
|
|
|
new SqlParameter("@BANKVALUE", SqlDbType.Decimal,9) ,
|
|
|
new SqlParameter("@MARKETVALUE", SqlDbType.Decimal,9) ,
|
|
|
new SqlParameter("@BZTCHNO", SqlDbType.VarChar,100) ,
|
|
|
new SqlParameter("@CREATEUSER", SqlDbType.VarChar,36) ,
|
|
|
new SqlParameter("@CLIENTBSNO", SqlDbType.VarChar,100)
|
|
|
|
|
|
//new SqlParameter("@MODIFIEDTIME", SqlDbType.DateTime) ,
|
|
|
//new SqlParameter("@ISDELETE", SqlDbType.Bit,1)
|
|
|
//new SqlParameter("@DELETEUSER", SqlDbType.VarChar,36) ,
|
|
|
//new SqlParameter("@DELETETIME", SqlDbType.DateTime) ,
|
|
|
};
|
|
|
|
|
|
parameters[0].Value = model.GID;
|
|
|
parameters[1].Value = model.WMSNO;
|
|
|
parameters[2].Value = model.WMSDATE;
|
|
|
parameters[3].Value = model.CUSTOMERNAME;
|
|
|
parameters[4].Value = model.WMSOP;
|
|
|
parameters[5].Value = model.STOREMAN;
|
|
|
parameters[6].Value = model.MODIFIEDUSER;
|
|
|
parameters[7].Value = model.TRUCKNAME;
|
|
|
parameters[8].Value = model.GOODSPACK;
|
|
|
parameters[9].Value = model.GOODSPACKSTOCK;
|
|
|
parameters[10].Value = model.GOODSRKSL;
|
|
|
parameters[11].Value = model.GOODSSTOCK;
|
|
|
parameters[12].Value = model.ARFEE;
|
|
|
parameters[13].Value = model.APFEE;
|
|
|
parameters[14].Value = model.ASSOCIATEDNO;
|
|
|
parameters[15].Value = model.BLNO;
|
|
|
parameters[16].Value = model.FREESTORAGEPERIOD;
|
|
|
parameters[17].Value = model.CONTRACTNO;
|
|
|
parameters[18].Value = model.GOODSNAME;
|
|
|
parameters[19].Value = model.GOODSMODEL;
|
|
|
parameters[20].Value = model.CLIENTCODEOLD;
|
|
|
parameters[21].Value = model.CLIENTNAMEOLD;
|
|
|
parameters[22].Value = model.STORAGENAME;
|
|
|
parameters[23].Value = model.CHARGEUNIT;
|
|
|
parameters[24].Value = model.STARTBILLINGDATE;
|
|
|
parameters[25].Value = model.CUSTOMNO;
|
|
|
parameters[26].Value = model.CUSTNO;
|
|
|
parameters[27].Value = model.GOODSSTANDARD;
|
|
|
parameters[28].Value = model.GOODSPACKACTUAL;
|
|
|
parameters[29].Value = model.GOODSRKSLACTUAL;
|
|
|
parameters[30].Value = model.ARCLIENT;
|
|
|
parameters[31].Value = model.FREESTORAGEPERIODAP;
|
|
|
parameters[32].Value = model.STARTBILLINGDATEAP;
|
|
|
parameters[33].Value = model.ISCHANGE;
|
|
|
parameters[34].Value = model.CORPID;
|
|
|
parameters[35].Value = model.AREANAME;
|
|
|
parameters[36].Value = model.BANKVALUE;
|
|
|
parameters[37].Value = model.MARKETVALUE;
|
|
|
parameters[38].Value = model.BZTCHNO;
|
|
|
parameters[39].Value = model.CREATEUSER;
|
|
|
parameters[40].Value = model.CLIENTBSNO;
|
|
|
|
|
|
//
|
|
|
int iResult = 0;
|
|
|
using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
|
|
|
{
|
|
|
int existVal = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, strSql.ToString(), parameters);
|
|
|
if (existVal > 0)
|
|
|
{
|
|
|
iResult = 1;
|
|
|
//
|
|
|
//string str0 = "insert into sys_log(NAME,LOGTYPE,LOGCONTENT,CREATEUSER) values('增加信息','增加操作','GID=" + model.GID.ToString() + "','" + model.MODIFIEDUSER.ToString() + "')";
|
|
|
//bool bl0 = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str0);
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
iResult = -1;//执行异常
|
|
|
}
|
|
|
}
|
|
|
return iResult;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 更新一条数据
|
|
|
/// </summary>
|
|
|
public int Update(WmsEntity model)
|
|
|
{
|
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
strSql.Append("update wms set ");
|
|
|
|
|
|
//strSql.Append(" GID = @GID , ");
|
|
|
strSql.Append(" WMSNO = @WMSNO , ");
|
|
|
strSql.Append(" WMSDATE = @WMSDATE , ");
|
|
|
strSql.Append(" CUSTOMERNAME = @CUSTOMERNAME , ");
|
|
|
strSql.Append(" WMSOP = @WMSOP , ");
|
|
|
strSql.Append(" STOREMAN = @STOREMAN , ");
|
|
|
strSql.Append(" MODIFIEDUSER = @MODIFIEDUSER , ");
|
|
|
strSql.Append(" MODIFIEDTIME = getdate(), ");
|
|
|
strSql.Append(" TRUCKNAME = @TRUCKNAME, ");
|
|
|
strSql.Append(" GOODSPACK = @GOODSPACK , ");
|
|
|
strSql.Append(" GOODSPACKSTOCK = @GOODSPACKSTOCK , ");
|
|
|
strSql.Append(" GOODSRKSL = @GOODSRKSL , ");
|
|
|
strSql.Append(" GOODSSTOCK = @GOODSSTOCK , ");
|
|
|
strSql.Append(" ARFEE = @ARFEE , ");
|
|
|
strSql.Append(" APFEE = @APFEE , ");
|
|
|
strSql.Append(" ASSOCIATEDNO = @ASSOCIATEDNO , ");
|
|
|
strSql.Append(" BLNO = @BLNO , ");
|
|
|
strSql.Append(" FREESTORAGEPERIOD = @FREESTORAGEPERIOD , ");
|
|
|
strSql.Append(" CONTRACTNO = @CONTRACTNO , ");
|
|
|
strSql.Append(" GOODSNAME = @GOODSNAME , ");
|
|
|
strSql.Append(" GOODSMODEL = @GOODSMODEL , ");
|
|
|
strSql.Append(" CLIENTCODEOLD = @CLIENTCODEOLD , ");
|
|
|
strSql.Append(" CLIENTNAMEOLD = @CLIENTNAMEOLD , ");
|
|
|
strSql.Append(" STORAGENAME = @STORAGENAME , ");
|
|
|
strSql.Append(" CHARGEUNIT = @CHARGEUNIT ,");
|
|
|
strSql.Append(" STARTBILLINGDATE = @STARTBILLINGDATE, ");
|
|
|
strSql.Append(" CUSTOMNO=@CUSTOMNO, ");
|
|
|
strSql.Append(" CUSTNO=@CUSTNO, ");
|
|
|
strSql.Append(" GOODSSTANDARD=@GOODSSTANDARD, ");
|
|
|
strSql.Append(" ARCLIENT = @ARCLIENT, ");
|
|
|
strSql.Append(" FREESTORAGEPERIODAP = @FREESTORAGEPERIODAP, ");
|
|
|
strSql.Append(" STARTBILLINGDATEAP = @STARTBILLINGDATEAP, ");
|
|
|
//strSql.Append(" CORPID = @CORPID, ");
|
|
|
strSql.Append(" AREANAME = @AREANAME, ");
|
|
|
strSql.Append(" BANKVALUE = @BANKVALUE, ");
|
|
|
strSql.Append(" MARKETVALUE = @MARKETVALUE, ");
|
|
|
strSql.Append(" BZTCHNO = @BZTCHNO, ");
|
|
|
strSql.Append(" CLIENTBSNO = @CLIENTBSNO ");
|
|
|
|
|
|
//strSql.Append(" ISDELETE = @ISDELETE ");
|
|
|
//strSql.Append(" DELETEUSER = @DELETEUSER , ");
|
|
|
//strSql.Append(" DELETETIME = @DELETETIME , ");
|
|
|
strSql.Append(" where GID=@GID ");
|
|
|
|
|
|
SqlParameter[] parameters = {
|
|
|
new SqlParameter("@GID", SqlDbType.VarChar,36) ,
|
|
|
new SqlParameter("@WMSNO", SqlDbType.VarChar,100) ,
|
|
|
new SqlParameter("@WMSDATE", SqlDbType.DateTime) ,
|
|
|
new SqlParameter("@CUSTOMERNAME", SqlDbType.VarChar,20) ,
|
|
|
new SqlParameter("@WMSOP", SqlDbType.VarChar,10) ,
|
|
|
new SqlParameter("@STOREMAN", SqlDbType.VarChar,10) ,
|
|
|
new SqlParameter("@MODIFIEDUSER", SqlDbType.VarChar,36) ,
|
|
|
new SqlParameter("@TRUCKNAME", SqlDbType.VarChar,20) ,
|
|
|
new SqlParameter("@GOODSPACK", SqlDbType.Decimal,9) ,
|
|
|
new SqlParameter("@GOODSPACKSTOCK", SqlDbType.Decimal,9) ,
|
|
|
new SqlParameter("@GOODSRKSL", SqlDbType.Decimal,9) ,
|
|
|
new SqlParameter("@GOODSSTOCK", SqlDbType.Decimal,9) ,
|
|
|
new SqlParameter("@ARFEE", SqlDbType.Decimal,9) ,
|
|
|
new SqlParameter("@APFEE", SqlDbType.Decimal,9) ,
|
|
|
new SqlParameter("@ASSOCIATEDNO", SqlDbType.VarChar,100),
|
|
|
new SqlParameter("@BLNO", SqlDbType.VarChar,100) ,
|
|
|
new SqlParameter("@FREESTORAGEPERIOD", SqlDbType.Int,4) ,
|
|
|
new SqlParameter("@CONTRACTNO", SqlDbType.VarChar,100) ,
|
|
|
new SqlParameter("@GOODSNAME", SqlDbType.VarChar,100) ,
|
|
|
new SqlParameter("@GOODSMODEL", SqlDbType.VarChar,100) ,
|
|
|
new SqlParameter("@CLIENTCODEOLD", SqlDbType.VarChar,20) ,
|
|
|
new SqlParameter("@CLIENTNAMEOLD", SqlDbType.VarChar,20) ,
|
|
|
new SqlParameter("@STORAGENAME", SqlDbType.VarChar,100) ,
|
|
|
new SqlParameter("@CHARGEUNIT", SqlDbType.VarChar,10) ,
|
|
|
new SqlParameter("@STARTBILLINGDATE", SqlDbType.DateTime),
|
|
|
new SqlParameter("@CUSTOMNO",SqlDbType.VarChar,20),
|
|
|
new SqlParameter("@CUSTNO",SqlDbType.VarChar,30),
|
|
|
new SqlParameter("@GOODSSTANDARD",SqlDbType.VarChar,100),
|
|
|
new SqlParameter("@ARCLIENT", SqlDbType.VarChar,20) ,
|
|
|
new SqlParameter("@FREESTORAGEPERIODAP", SqlDbType.Int,4) ,
|
|
|
new SqlParameter("@STARTBILLINGDATEAP", SqlDbType.DateTime) ,
|
|
|
//new SqlParameter("@CORPID", SqlDbType.VarChar,36) ,
|
|
|
new SqlParameter("@AREANAME",SqlDbType.VarChar,100) ,
|
|
|
new SqlParameter("@BANKVALUE", SqlDbType.Decimal,9) ,
|
|
|
new SqlParameter("@MARKETVALUE", SqlDbType.Decimal,9) ,
|
|
|
new SqlParameter("@BZTCHNO", SqlDbType.VarChar,100) ,
|
|
|
new SqlParameter("@CLIENTBSNO", SqlDbType.VarChar,100)
|
|
|
};
|
|
|
|
|
|
parameters[0].Value = model.GID;
|
|
|
parameters[1].Value = model.WMSNO;
|
|
|
parameters[2].Value = model.WMSDATE;
|
|
|
parameters[3].Value = model.CUSTOMERNAME;
|
|
|
parameters[4].Value = model.WMSOP;
|
|
|
parameters[5].Value = model.STOREMAN;
|
|
|
parameters[6].Value = model.MODIFIEDUSER;
|
|
|
parameters[7].Value = model.TRUCKNAME;
|
|
|
parameters[8].Value = model.GOODSPACK;
|
|
|
parameters[9].Value = model.GOODSPACKSTOCK;
|
|
|
parameters[10].Value = model.GOODSRKSL;
|
|
|
parameters[11].Value = model.GOODSSTOCK;
|
|
|
parameters[12].Value = model.ARFEE;
|
|
|
parameters[13].Value = model.APFEE;
|
|
|
parameters[14].Value = model.ASSOCIATEDNO;
|
|
|
parameters[15].Value = model.BLNO;
|
|
|
parameters[16].Value = model.FREESTORAGEPERIOD;
|
|
|
parameters[17].Value = model.CONTRACTNO;
|
|
|
parameters[18].Value = model.GOODSNAME;
|
|
|
parameters[19].Value = model.GOODSMODEL;
|
|
|
parameters[20].Value = model.CLIENTCODEOLD;
|
|
|
parameters[21].Value = model.CLIENTNAMEOLD;
|
|
|
parameters[22].Value = model.STORAGENAME;
|
|
|
parameters[23].Value = model.CHARGEUNIT;
|
|
|
parameters[24].Value = model.STARTBILLINGDATE;
|
|
|
parameters[25].Value = model.CUSTOMNO;
|
|
|
parameters[26].Value = model.CUSTNO;
|
|
|
parameters[27].Value = model.GOODSSTANDARD;
|
|
|
parameters[28].Value = model.ARCLIENT;
|
|
|
parameters[29].Value = model.FREESTORAGEPERIODAP;
|
|
|
parameters[30].Value = model.STARTBILLINGDATEAP;
|
|
|
//parameters[31].Value = model.CORPID;
|
|
|
parameters[31].Value = model.AREANAME;
|
|
|
parameters[32].Value = model.BANKVALUE;
|
|
|
parameters[33].Value = model.MARKETVALUE;
|
|
|
parameters[34].Value = model.BZTCHNO;
|
|
|
parameters[35].Value = model.CLIENTBSNO;
|
|
|
//
|
|
|
int iResult = 0;
|
|
|
using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
|
|
|
{
|
|
|
int existVal = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, strSql.ToString(), parameters);
|
|
|
if (existVal > 0)
|
|
|
{
|
|
|
iResult = 1;
|
|
|
//
|
|
|
//string str0 = "insert into sys_log(NAME,LOGTYPE,LOGCONTENT,CREATEUSER) values('更新信息表','更新操作','GID=" + model.GID.ToString() + "','" + model.MODIFIEDUSER.ToString() + "')";
|
|
|
//bool bl0 = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str0);
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
iResult = -1;//执行异常
|
|
|
}
|
|
|
}
|
|
|
return iResult;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 删除一条数据
|
|
|
/// </summary>
|
|
|
public int Delete(string GID)
|
|
|
{
|
|
|
|
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
strSql.Append("delete from wms ");
|
|
|
strSql.Append(" where GID=@GID ");
|
|
|
SqlParameter[] parameters = {
|
|
|
new SqlParameter("@GID", SqlDbType.VarChar,36) };
|
|
|
parameters[0].Value = GID;
|
|
|
|
|
|
|
|
|
//
|
|
|
int iResult = 0;
|
|
|
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
|
|
|
{
|
|
|
try
|
|
|
{
|
|
|
SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
|
|
|
iResult = 1;//状态为1表示删除成功
|
|
|
sqlTran.Commit();
|
|
|
}
|
|
|
catch (Exception execError)
|
|
|
{
|
|
|
iResult = -1;//有异常,删除失败
|
|
|
sqlTran.Rollback();
|
|
|
iResult = -2;//插入异常,事务已回滚成功
|
|
|
throw execError;
|
|
|
}
|
|
|
finally
|
|
|
{
|
|
|
SqlHelper.CloseConnection();
|
|
|
}
|
|
|
}
|
|
|
return iResult;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 得到一个对象实体
|
|
|
/// </summary>
|
|
|
public WmsEntity GetModel(string GID)
|
|
|
{
|
|
|
//初始化返回变量
|
|
|
WmsEntity model = null;
|
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
strSql.Append("select GID, WMSNO, WMSDATE, CUSTOMERNAME, WMSOP, STOREMAN, MODIFIEDUSER, MODIFIEDTIME, ISDELETE, DELETEUSER, DELETETIME,TRUCKNAME, GOODSPACK, GOODSPACKSTOCK, GOODSRKSL, GOODSSTOCK, ARFEE, APFEE, ASSOCIATEDNO, BLNO, FREESTORAGEPERIOD, CONTRACTNO, GOODSNAME, GOODSMODEL, CLIENTCODEOLD, CLIENTNAMEOLD, STORAGENAME, CHARGEUNIT, STARTBILLINGDATE,CUSTOMNO,CUSTNO,GOODSSTANDARD,GOODSPACKACTUAL,GOODSRKSLACTUAL,ARCLIENT, FREESTORAGEPERIODAP, STARTBILLINGDATEAP, ISCHANGE,AREANAME,BANKVALUE,MARKETVALUE,BZTCHNO,CREATEUSER,CREATETIME,CLIENTBSNO");
|
|
|
strSql.Append(" from wms ");
|
|
|
strSql.Append(" where GID=@GID ");
|
|
|
SqlParameter[] parameters = {
|
|
|
new SqlParameter("@GID", SqlDbType.VarChar,36) };
|
|
|
parameters[0].Value = GID;
|
|
|
|
|
|
//
|
|
|
using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters))
|
|
|
{
|
|
|
try
|
|
|
{
|
|
|
model = new WmsEntity();
|
|
|
//读取字段值
|
|
|
while (sqlRead.Read())
|
|
|
{
|
|
|
if (!sqlRead.IsDBNull(0)) { model.GID = sqlRead.GetString(0); } // 唯一编码 newid()
|
|
|
if (!sqlRead.IsDBNull(1)) { model.WMSNO = sqlRead.GetString(1); } //
|
|
|
if (!sqlRead.IsDBNull(2)) { model.WMSDATE = sqlRead.GetDateTime(2); } //
|
|
|
if (!sqlRead.IsDBNull(3)) { model.CUSTOMERNAME = sqlRead.GetString(3); } //
|
|
|
if (!sqlRead.IsDBNull(4)) { model.WMSOP = sqlRead.GetString(4); } //
|
|
|
if (!sqlRead.IsDBNull(5)) { model.STOREMAN = sqlRead.GetString(5); }
|
|
|
if (!sqlRead.IsDBNull(6)) { model.MODIFIEDUSER = sqlRead.GetString(6); }
|
|
|
if (!sqlRead.IsDBNull(7)) { model.MODIFIEDTIME = sqlRead.GetDateTime(7); }
|
|
|
if (!sqlRead.IsDBNull(8)) { model.ISDELETE = sqlRead.GetBoolean(8); } // 是否废除
|
|
|
if (!sqlRead.IsDBNull(9)) { model.DELETEUSER = sqlRead.GetString(9); } // 废除操作人
|
|
|
if (!sqlRead.IsDBNull(10)) { model.DELETETIME = sqlRead.GetDateTime(10); } // 废除时间
|
|
|
if (!sqlRead.IsDBNull(11)) { model.TRUCKNAME = sqlRead.GetString(11); } //
|
|
|
if (!sqlRead.IsDBNull(12)) { model.GOODSPACK = sqlRead.GetDecimal(12); } //
|
|
|
if (!sqlRead.IsDBNull(13)) { model.GOODSPACKSTOCK = sqlRead.GetDecimal(13); } //
|
|
|
if (!sqlRead.IsDBNull(14)) { model.GOODSRKSL = sqlRead.GetDecimal(14); } //
|
|
|
if (!sqlRead.IsDBNull(15)) { model.GOODSSTOCK = sqlRead.GetDecimal(15); } //
|
|
|
if (!sqlRead.IsDBNull(16)) { model.ARFEE = sqlRead.GetDecimal(16); } //
|
|
|
if (!sqlRead.IsDBNull(17)) { model.APFEE = sqlRead.GetDecimal(17); } //
|
|
|
if (!sqlRead.IsDBNull(18)) { model.ASSOCIATEDNO = sqlRead.GetString(18); } //
|
|
|
if (!sqlRead.IsDBNull(19)) { model.BLNO = sqlRead.GetString(19); } //
|
|
|
if (!sqlRead.IsDBNull(20)) { model.FREESTORAGEPERIOD = sqlRead.GetInt32(20); } //
|
|
|
if (!sqlRead.IsDBNull(21)) { model.CONTRACTNO = sqlRead.GetString(21); } //
|
|
|
if (!sqlRead.IsDBNull(22)) { model.GOODSNAME = sqlRead.GetString(22); } //
|
|
|
if (!sqlRead.IsDBNull(23)) { model.GOODSMODEL = sqlRead.GetString(23); } //
|
|
|
if (!sqlRead.IsDBNull(24)) { model.CLIENTCODEOLD = sqlRead.GetString(24); } //
|
|
|
if (!sqlRead.IsDBNull(25)) { model.CLIENTNAMEOLD = sqlRead.GetString(25); } //
|
|
|
if (!sqlRead.IsDBNull(26)) { model.STORAGENAME = sqlRead.GetString(26); } //
|
|
|
if (!sqlRead.IsDBNull(27)) { model.CHARGEUNIT = sqlRead.GetString(27); } //
|
|
|
if (!sqlRead.IsDBNull(28)) { model.STARTBILLINGDATE = sqlRead.GetDateTime(28); } //
|
|
|
if (!sqlRead.IsDBNull(29)) { model.CUSTOMNO = sqlRead.GetString(29); }//
|
|
|
if (!sqlRead.IsDBNull(30)) { model.CUSTNO = sqlRead.GetString(30); }
|
|
|
if (!sqlRead.IsDBNull(31)) { model.GOODSSTANDARD = sqlRead.GetString(31); }
|
|
|
if (!sqlRead.IsDBNull(32)) { model.GOODSPACKACTUAL = sqlRead.GetDecimal(32); }
|
|
|
if (!sqlRead.IsDBNull(33)) { model.GOODSRKSLACTUAL = sqlRead.GetDecimal(33); }
|
|
|
if (!sqlRead.IsDBNull(34)) { model.ARCLIENT = sqlRead.GetString(34); } //
|
|
|
if (!sqlRead.IsDBNull(35)) { model.FREESTORAGEPERIODAP = sqlRead.GetInt32(35); } //
|
|
|
if (!sqlRead.IsDBNull(36)) { model.STARTBILLINGDATEAP = sqlRead.GetDateTime(36); } //
|
|
|
if (!sqlRead.IsDBNull(37)) { model.ISCHANGE = sqlRead.GetBoolean(37); } //
|
|
|
if (!sqlRead.IsDBNull(38)) { model.AREANAME = sqlRead.GetString(38); }
|
|
|
if (!sqlRead.IsDBNull(39)) { model.BANKVALUE = sqlRead.GetDecimal(39); }
|
|
|
if (!sqlRead.IsDBNull(40)) { model.MARKETVALUE = sqlRead.GetDecimal(40); }
|
|
|
if (!sqlRead.IsDBNull(41)) { model.BZTCHNO = sqlRead.GetString(41); }
|
|
|
if (!sqlRead.IsDBNull(42)) { model.CREATEUSER = sqlRead.GetString(42); }
|
|
|
if (!sqlRead.IsDBNull(43)) { model.CREATETIME = sqlRead.GetDateTime(43); }
|
|
|
if (!sqlRead.IsDBNull(44)) { model.CLIENTBSNO = sqlRead.GetString(44); }
|
|
|
}
|
|
|
}
|
|
|
catch (Exception exceError)
|
|
|
{
|
|
|
//抛出异常
|
|
|
throw exceError;
|
|
|
}
|
|
|
}
|
|
|
return model;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获得数据列表
|
|
|
/// </summary>
|
|
|
public DataSet GetList(string strWhere)
|
|
|
{
|
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
strSql.Append("select * ");
|
|
|
strSql.Append(" FROM wms ");
|
|
|
if (strWhere.Trim() != "")
|
|
|
{
|
|
|
strSql.Append(" where " + strWhere);
|
|
|
}
|
|
|
//
|
|
|
DataSet DS = SqlHelper.OpenSqlDataSet(SqlHelper.ConnectionStringLocalTransaction, strSql.ToString());
|
|
|
try
|
|
|
{
|
|
|
if (DS.Tables[0].Rows.Count <= 0)
|
|
|
{
|
|
|
DS = null;
|
|
|
}
|
|
|
}
|
|
|
catch
|
|
|
{
|
|
|
DS = null;
|
|
|
}
|
|
|
return DS;
|
|
|
}
|
|
|
|
|
|
#region 根据SQL语句查询入库数据集
|
|
|
/// <summary>
|
|
|
/// 根据SQL语句查询入库数据集
|
|
|
/// </summary>
|
|
|
/// <param name="strSql"></param>
|
|
|
/// <returns></returns>
|
|
|
public DataSet GetRateListByCondition(string strSql)
|
|
|
{
|
|
|
DataSet userSet = new DataSet();
|
|
|
userSet = SqlHelper.ExecuteDataset(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql);
|
|
|
return userSet;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
#region 删除入库信息
|
|
|
/// <summary>
|
|
|
/// 删除入库信息
|
|
|
/// </summary>
|
|
|
/// <param name="strSel">入库信息GID</param>
|
|
|
/// <param name="strUserID">删除人ID</param>
|
|
|
/// <returns>值1表示删除成功 值-1表示有异常，删除失败 值-2表示插入异常,事务已回滚成功</returns>
|
|
|
public String GetWmsDel(string strSel, string strUserID)
|
|
|
{
|
|
|
string alt = "";
|
|
|
//string str = "update wms set ISDELETE=1,DELETETIME=getdate(),DELETEUSER='" + strUserID + "' where GID in('" + strSel + "') and ISDELETE=0";
|
|
|
string str = "delete from ch_fee where BSNO in('" + strSel + "')";
|
|
|
bool bl = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str);
|
|
|
str = "delete from wms_in where ASSOCIATEDNO in('" + strSel + "')";
|
|
|
bl = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str);
|
|
|
str = "delete from wms_rate where BSNO in('" + strSel + "')";
|
|
|
bl = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str);
|
|
|
str = "delete from wms where GID in('" + strSel + "')";
|
|
|
bl = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str);
|
|
|
if (bl == false)
|
|
|
{
|
|
|
alt = "入库信息表wms删除操作出错！";
|
|
|
return alt;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
//str = str.Trim().Replace("'", "''");
|
|
|
//string str0 = "insert into sys_log(NAME,LOGTYPE,LOGCONTENT,CREATEUSER) values('删除销售订舱信息表wms','删除操作','" + str + "','" + strUserID + "')";
|
|
|
//bool bl0 = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str0);
|
|
|
}
|
|
|
return alt;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
/// <summary>
|
|
|
/// 装卸费入账，并锁定入库信息
|
|
|
/// </summary>
|
|
|
public int setislock(string sGids, string strUserID)
|
|
|
{
|
|
|
int iResult = 0;
|
|
|
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
|
|
|
{
|
|
|
try
|
|
|
{
|
|
|
string strgids = "'" + sGids.Replace(",", "','") + "'";
|
|
|
string strSql = "update wms set ISLOCK=1,LOCKUSER='" + strUserID + "',LOCKTIME=getdate() where gid in (" + strgids + ")";
|
|
|
iResult = -3;//
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
|
|
|
|
|
|
strSql = "update wms_in set ISLOCK=1,LOCKUSER='" + strUserID + "',LOCKTIME=getdate() where ASSOCIATEDNO in (" + strgids + ")";
|
|
|
iResult = -4;//
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
|
|
|
|
|
|
//
|
|
|
string[] items = sGids.Trim().Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
|
|
|
for (int i = 0; i < items.Length; i++)
|
|
|
{
|
|
|
#region 费用参数
|
|
|
string PARM_FEE_GID = "@gid";
|
|
|
string PARM_FEE_BSNO = "@bsno";
|
|
|
string PARM_FEE_TYPE = "@fee_type";
|
|
|
string PARM_FEE_NAME = "@fee_name";
|
|
|
string PARM_FEE_REMARK = "@remark";
|
|
|
string PARM_FEE_FRT = "@fee_frt";
|
|
|
string PARM_FEE_AMOUNT = "@amount";
|
|
|
string PARM_FEE_CURRENCY = "@currency";
|
|
|
string PARM_FEE_QUANTITY = "@quantity";
|
|
|
string PARM_FEE_STATUS = "@fee_status";
|
|
|
string PARM_FEE_UNIT = "@unit";
|
|
|
string PARM_FEE_UNIT_PRICE = "@unit_price";
|
|
|
string PARM_FEE_COMMISSION_RATE = "@comm_rate";
|
|
|
string PARM_FEE_CUSTOMER_NAME = "@customer_name";
|
|
|
string PARM_FEE_EXCHANGE_RATE = "@exchange_rate";
|
|
|
string PARM_FEE_ISADVANCEPAY = "@is_advance_pay";
|
|
|
string PARM_FEE_ENTER_OPERATOR = "@enter_operator";
|
|
|
string PARM_FEE_AUDIT_WMSOUTBSNO = "@WMSOUTBSNO";
|
|
|
//
|
|
|
string strARCLIENT = "SELECT top 1 ARCLIENT FROM wms WHERE GID = '" + items[i].ToString().Trim() + "'";
|
|
|
object statusObj = SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, strARCLIENT, null);
|
|
|
if (statusObj == null)
|
|
|
{
|
|
|
strARCLIENT = "";
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
strARCLIENT = statusObj.ToString().Trim();
|
|
|
}
|
|
|
//
|
|
|
string strTRUCKNAME = "SELECT top 1 TRUCKNAME FROM wms WHERE GID = '" + items[i].ToString().Trim() + "'";
|
|
|
statusObj = SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, strTRUCKNAME, null);
|
|
|
if (statusObj == null)
|
|
|
{
|
|
|
strTRUCKNAME = "";
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
strTRUCKNAME = statusObj.ToString().Trim();
|
|
|
}
|
|
|
//
|
|
|
string strASSOCIATEDNO = "SELECT top 1 ASSOCIATEDNO FROM wms WHERE GID = '" + items[i].ToString().Trim() + "'";
|
|
|
statusObj = SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, strASSOCIATEDNO, null);
|
|
|
if (statusObj == null)
|
|
|
{
|
|
|
strASSOCIATEDNO = items[i].ToString().Trim();
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
strASSOCIATEDNO = statusObj.ToString().Trim();
|
|
|
}
|
|
|
//
|
|
|
int isFeeGain = 0;
|
|
|
#endregion
|
|
|
//
|
|
|
#region 应收费用合计入ch_fee表
|
|
|
string feehj = "SELECT isnull(sum(ARFEE),0) as hj FROM wms_in WHERE ASSOCIATEDNO = '" + items[i].ToString().Trim() + "'";
|
|
|
statusObj = SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, feehj, null);
|
|
|
if (statusObj == null)
|
|
|
{
|
|
|
feehj = "0";
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
feehj = statusObj.ToString().Trim();
|
|
|
}
|
|
|
//
|
|
|
if (decimal.Parse(feehj) != 0)
|
|
|
{
|
|
|
isFeeGain = 1;
|
|
|
string SQL_INSERT_CH_FEE = " INSERT INTO ch_fee (GID,FEENAME,CUSTOMERNAME,UNIT,UNITPRICE,QUANTITY,COMMISSIONRATE,AMOUNT,CURRENCY,EXCHANGERATE,FEEFRT,REMARK,ISADVANCEDPAY,FEETYPE,BSNO,ENTEROPERATOR,ENTERDATE,FEESTATUS,WMSOUTBSNO) VALUES(@gid,@fee_name,@customer_name,@unit,@unit_price,@quantity,@comm_rate,@amount,@currency,@exchange_rate,@fee_frt,@remark,@is_advance_pay,@fee_type,@bsno,@enter_operator,GETDATE(),@fee_status,@WMSOUTBSNO) ";
|
|
|
SqlParameter[] insertParms = new SqlParameter[] {
|
|
|
new SqlParameter(PARM_FEE_GID,SqlDbType.VarChar,36),
|
|
|
new SqlParameter(PARM_FEE_NAME,SqlDbType.VarChar,100),
|
|
|
new SqlParameter(PARM_FEE_CUSTOMER_NAME,SqlDbType.VarChar,50),
|
|
|
new SqlParameter(PARM_FEE_UNIT,SqlDbType.VarChar,20),
|
|
|
new SqlParameter(PARM_FEE_UNIT_PRICE,SqlDbType.Decimal),
|
|
|
new SqlParameter(PARM_FEE_QUANTITY,SqlDbType.Decimal),
|
|
|
new SqlParameter(PARM_FEE_COMMISSION_RATE,SqlDbType.Decimal),
|
|
|
new SqlParameter(PARM_FEE_AMOUNT,SqlDbType.Decimal),
|
|
|
new SqlParameter(PARM_FEE_CURRENCY,SqlDbType.VarChar,20),
|
|
|
new SqlParameter(PARM_FEE_EXCHANGE_RATE,SqlDbType.Decimal),
|
|
|
new SqlParameter(PARM_FEE_FRT,SqlDbType.VarChar,20),
|
|
|
new SqlParameter(PARM_FEE_REMARK,SqlDbType.VarChar),
|
|
|
new SqlParameter(PARM_FEE_ISADVANCEPAY,SqlDbType.Bit),
|
|
|
new SqlParameter(PARM_FEE_TYPE,SqlDbType.Int),
|
|
|
new SqlParameter(PARM_FEE_BSNO,SqlDbType.VarChar,100),
|
|
|
new SqlParameter(PARM_FEE_ENTER_OPERATOR,SqlDbType.VarChar,50),
|
|
|
new SqlParameter(PARM_FEE_STATUS,SqlDbType.Int),
|
|
|
new SqlParameter(PARM_FEE_AUDIT_WMSOUTBSNO,SqlDbType.VarChar,100)
|
|
|
};
|
|
|
|
|
|
insertParms[0].Value = Guid.NewGuid().ToString();
|
|
|
insertParms[1].Value = "仓储装卸费";
|
|
|
insertParms[2].Value = strARCLIENT.Trim();
|
|
|
insertParms[3].Value = "重量";
|
|
|
insertParms[4].Value = decimal.Parse(feehj);
|
|
|
insertParms[5].Value = 1;
|
|
|
insertParms[6].Value = 1;
|
|
|
insertParms[7].Value = decimal.Parse(feehj);
|
|
|
insertParms[8].Value = "RMB";
|
|
|
insertParms[9].Value = 1;
|
|
|
insertParms[10].Value = "";
|
|
|
insertParms[11].Value = "";
|
|
|
insertParms[12].Value = false;
|
|
|
insertParms[13].Value = 1;
|
|
|
insertParms[14].Value = strASSOCIATEDNO.Trim();
|
|
|
insertParms[15].Value = strUserID;
|
|
|
insertParms[16].Value = 0;
|
|
|
insertParms[17].Value = items[i].ToString().Trim();
|
|
|
iResult = -5;//
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_CH_FEE, insertParms);
|
|
|
}
|
|
|
#endregion
|
|
|
//
|
|
|
#region 应付费用合计入ch_fee表
|
|
|
feehj = "SELECT isnull(sum(APFEE),0) as hj FROM wms_in WHERE ASSOCIATEDNO = '" + items[i].ToString().Trim() + "'";
|
|
|
statusObj = SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, feehj, null);
|
|
|
if (statusObj == null)
|
|
|
{
|
|
|
feehj = "0";
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
feehj = statusObj.ToString().Trim();
|
|
|
}
|
|
|
//
|
|
|
if (decimal.Parse(feehj) != 0)
|
|
|
{
|
|
|
isFeeGain = 1;
|
|
|
string SQL_INSERT_CH_FEE = " INSERT INTO ch_fee (GID,FEENAME,CUSTOMERNAME,UNIT,UNITPRICE,QUANTITY,COMMISSIONRATE,AMOUNT,CURRENCY,EXCHANGERATE,FEEFRT,REMARK,ISADVANCEDPAY,FEETYPE,BSNO,ENTEROPERATOR,ENTERDATE,FEESTATUS,WMSOUTBSNO) VALUES(@gid,@fee_name,@customer_name,@unit,@unit_price,@quantity,@comm_rate,@amount,@currency,@exchange_rate,@fee_frt,@remark,@is_advance_pay,@fee_type,@bsno,@enter_operator,GETDATE(),@fee_status,@WMSOUTBSNO) ";
|
|
|
SqlParameter[] insertParms = new SqlParameter[] {
|
|
|
new SqlParameter(PARM_FEE_GID,SqlDbType.VarChar,36),
|
|
|
new SqlParameter(PARM_FEE_NAME,SqlDbType.VarChar,100),
|
|
|
new SqlParameter(PARM_FEE_CUSTOMER_NAME,SqlDbType.VarChar,50),
|
|
|
new SqlParameter(PARM_FEE_UNIT,SqlDbType.VarChar,20),
|
|
|
new SqlParameter(PARM_FEE_UNIT_PRICE,SqlDbType.Decimal),
|
|
|
new SqlParameter(PARM_FEE_QUANTITY,SqlDbType.Decimal),
|
|
|
new SqlParameter(PARM_FEE_COMMISSION_RATE,SqlDbType.Decimal),
|
|
|
new SqlParameter(PARM_FEE_AMOUNT,SqlDbType.Decimal),
|
|
|
new SqlParameter(PARM_FEE_CURRENCY,SqlDbType.VarChar,20),
|
|
|
new SqlParameter(PARM_FEE_EXCHANGE_RATE,SqlDbType.Decimal),
|
|
|
new SqlParameter(PARM_FEE_FRT,SqlDbType.VarChar,20),
|
|
|
new SqlParameter(PARM_FEE_REMARK,SqlDbType.VarChar),
|
|
|
new SqlParameter(PARM_FEE_ISADVANCEPAY,SqlDbType.Bit),
|
|
|
new SqlParameter(PARM_FEE_TYPE,SqlDbType.Int),
|
|
|
new SqlParameter(PARM_FEE_BSNO,SqlDbType.VarChar,100),
|
|
|
new SqlParameter(PARM_FEE_ENTER_OPERATOR,SqlDbType.VarChar,50),
|
|
|
new SqlParameter(PARM_FEE_STATUS,SqlDbType.Int),
|
|
|
new SqlParameter(PARM_FEE_AUDIT_WMSOUTBSNO,SqlDbType.VarChar,100)
|
|
|
};
|
|
|
|
|
|
insertParms[0].Value = Guid.NewGuid().ToString();
|
|
|
insertParms[1].Value = "仓储装卸费";
|
|
|
insertParms[2].Value = strTRUCKNAME.Trim();
|
|
|
insertParms[3].Value = "重量";
|
|
|
insertParms[4].Value = decimal.Parse(feehj);
|
|
|
insertParms[5].Value = 1;
|
|
|
insertParms[6].Value = 1;
|
|
|
insertParms[7].Value = decimal.Parse(feehj);
|
|
|
insertParms[8].Value = "RMB";
|
|
|
insertParms[9].Value = 1;
|
|
|
insertParms[10].Value = "";
|
|
|
insertParms[11].Value = "";
|
|
|
insertParms[12].Value = false;
|
|
|
insertParms[13].Value = 2;
|
|
|
insertParms[14].Value = strASSOCIATEDNO.Trim();
|
|
|
insertParms[15].Value = strUserID;
|
|
|
insertParms[16].Value = 0;
|
|
|
insertParms[17].Value = items[i].ToString().Trim();
|
|
|
iResult = -6;//
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_CH_FEE, insertParms);
|
|
|
}
|
|
|
#endregion
|
|
|
//
|
|
|
#region 保存利润信息
|
|
|
if (isFeeGain == 1)
|
|
|
{
|
|
|
string SQL_SELECT_FEE_PROFIT_BY_BSNO = "SELECT BSNO FROM fee_gain WHERE BSNO = '" + strASSOCIATEDNO + "' ";
|
|
|
object bsnoObj = SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, SQL_SELECT_FEE_PROFIT_BY_BSNO, null);
|
|
|
if (bsnoObj == null)//如果没有相关利润信息,则插入新的利润信息
|
|
|
{
|
|
|
SqlParameter[] insertProfitParms = new SqlParameter[] {
|
|
|
new SqlParameter("@bsno",SqlDbType.VarChar,100),//委托编号
|
|
|
new SqlParameter("@create_user",SqlDbType.VarChar,36)//创建人
|
|
|
};
|
|
|
insertProfitParms[0].Value = strASSOCIATEDNO;
|
|
|
insertProfitParms[1].Value = strUserID;
|
|
|
//
|
|
|
string SQL_INSERT_FEE_GAIN_RECALCULATE = " INSERT INTO fee_gain(BSNO,RMBDR,USDDR,OTDR,RMBCR,USDCR,OTCR,STLDRRMB,STLDRUSD, "
|
|
|
+ " STLDROT,STLCRRMB,STLCRUSD,STLCROT,STLDRTTL,STLCRTTL,RMBPROFIT,USDPROFIT,OTPROFIT, "
|
|
|
+ " DRTTL,CRTTL,TTLPROFIT,PROFITRATE,CREATEUSER,CREATETIME,MODIFIEDUSER,MODIFIEDTIME) "
|
|
|
+ " SELECT * FROM ("
|
|
|
+ " SELECT BSNO,SUM(RMBDR) AS RMBDR,SUM(USDDR) AS USDDR,0 AS OTDR,SUM(RMBCR) AS RMBCR,SUM(USDCR) AS USDCR,0 AS OTCR, "
|
|
|
+ " 0 AS STLDRRMB,0 AS STLDRUSD,0 AS STLDROT,0 AS STLCRRMB,0 AS STLCRUSD,0 AS STLCROT,0 AS STLDRTTL,0 AS STLCRTTL,SUM(RMBDR) - SUM(RMBCR) AS RMBPROFIT, "
|
|
|
+ " SUM(USDDR) - SUM(USDCR) AS USDPROFIT,0 AS OTPROFIT FROM "
|
|
|
+ " (SELECT CURRENCY,BSNO,SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as RMBDR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as RMBCR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as USDDR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as USDCR,FEETYPE "
|
|
|
+ " FROM ch_fee WHERE BSNO = @bsno and FEENAME in (select [name] from code_fee where FEETYPE='普通') GROUP BY CURRENCY,EXCHANGERATE,FEETYPE,BSNO ) AS D GROUP BY BSNO) AS B, "
|
|
|
+ " (SELECT SUM(RMBDR+USDTORMBDR) as DRTTL,SUM(RMBCR+USDTORMBCR) as CRTTL,"
|
|
|
+ " SUM(RMBDR+USDTORMBDR)- SUM(RMBCR+USDTORMBCR) as TTLPROFIT,"
|
|
|
//+ " (CAST(CAST(CASE WHEN SUM(RMBCR+USDTORMBCR) = 0 THEN 100 ELSE (ROUND((case when SUM(RMBDR+USDTORMBDR)=0 then 0 else (SUM(RMBDR+USDTORMBDR)- SUM(RMBCR+USDTORMBCR))/SUM(RMBDR+USDTORMBDR) END),2))*100 END as decimal(18,2)) as varchar(20))+'%') AS PROFITRATE,"
|
|
|
+ " (CAST(CAST(CASE WHEN SUM(RMBCR+USDTORMBCR) = 0 THEN 100 ELSE (ROUND(((SUM(RMBDR+USDTORMBDR)- SUM(RMBCR+USDTORMBCR))/SUM(RMBCR+USDTORMBCR)),4))*100 END as decimal(18,2)) as varchar(20))+'%') AS PROFITRATE,"
|
|
|
+ " @create_user as CREATEUSER,GETDATE() as CREATETIME,@create_user as MODIFIEDUSER,GETDATE() as MODIFIEDTIME "
|
|
|
+ " FROM (SELECT CURRENCY,SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as RMBDR,"
|
|
|
+ " SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as RMBCR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as USDDR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as USDCR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 1 THEN AMOUNT*EXCHANGERATE ELSE 0 END) as USDTORMBDR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 2 THEN AMOUNT*EXCHANGERATE ELSE 0 END) as USDTORMBCR,FEETYPE "
|
|
|
+ " FROM ch_fee WHERE BSNO = @bsno and FEENAME in (select [name] from code_fee where FEETYPE='普通') GROUP BY CURRENCY,EXCHANGERATE,FEETYPE ) AS C) AS A ";
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_FEE_GAIN_RECALCULATE, insertProfitParms);
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
SqlParameter[] updateProfitParms = new SqlParameter[] {
|
|
|
new SqlParameter("@bsno",SqlDbType.VarChar,100),//委托编号
|
|
|
new SqlParameter("@modified_user",SqlDbType.VarChar,36)//创建人
|
|
|
};
|
|
|
updateProfitParms[0].Value = strASSOCIATEDNO;
|
|
|
updateProfitParms[1].Value = strUserID;
|
|
|
//
|
|
|
string SQL_UPDATE_FEE_GAIN_RECALCULATE = " UPDATE fee_gain SET RMBDR = B.RMBDR,RMBCR = B.RMBCR,USDDR = B.USDDR,USDCR = B.USDCR,RMBPROFIT = A.RMBPROFIT,"
|
|
|
+ " USDPROFIT = A.USDPROFIT,TTLPROFIT = A.TTLPROFIT,DRTTL = A.DRTTL,CRTTL = A.CRTTL,MODIFIEDUSER = @modified_user,MODIFIEDTIME = GETDATE(),PROFITRATE = A.PROFITRATE "
|
|
|
+ " FROM (SELECT SUM(RMBDR) AS RMBDR,SUM(RMBCR) AS RMBCR,SUM(USDDR) AS USDDR,SUM(USDCR) AS USDCR "
|
|
|
+ " FROM (SELECT CURRENCY,SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as RMBDR,"
|
|
|
+ " SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as RMBCR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as USDDR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as USDCR, "
|
|
|
+ " FEETYPE FROM ch_fee WHERE BSNO = @bsno and FEENAME in (select [name] from code_fee where FEETYPE='普通') GROUP BY CURRENCY,EXCHANGERATE,FEETYPE ) AS D) AS B, "
|
|
|
+ " (SELECT SUM(RMBDR) - SUM(RMBCR) AS RMBPROFIT,SUM(USDDR) - SUM(USDCR) AS USDPROFIT,SUM(RMBDR+USDTORMBDR) as DRTTL, "
|
|
|
+ " SUM(RMBCR+USDTORMBCR) as CRTTL,SUM(RMBDR+USDTORMBDR)- SUM(RMBCR+USDTORMBCR) as TTLPROFIT, "
|
|
|
//+ " (CAST(CAST(CASE WHEN SUM(RMBCR+USDTORMBCR) = 0 THEN 100 ELSE (ROUND((case when SUM(RMBDR+USDTORMBDR)=0 then 0 else (SUM(RMBDR+USDTORMBDR)- SUM(RMBCR+USDTORMBCR))/SUM(RMBDR+USDTORMBDR) END),2))*100 END as decimal(18,2)) as varchar(20))+'%') AS PROFITRATE "
|
|
|
+ " (CAST(CAST(CASE WHEN SUM(RMBCR+USDTORMBCR) = 0 THEN 100 ELSE (ROUND(((SUM(RMBDR+USDTORMBDR)- SUM(RMBCR+USDTORMBCR))/SUM(RMBCR+USDTORMBCR)),4))*100 END as decimal(18,2)) as varchar(20))+'%') AS PROFITRATE "
|
|
|
+ " FROM (SELECT CURRENCY,SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as RMBDR,"
|
|
|
+ " SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as RMBCR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as USDDR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as USDCR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 1 THEN AMOUNT*EXCHANGERATE ELSE 0 END) as USDTORMBDR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 2 THEN AMOUNT*EXCHANGERATE ELSE 0 END) as USDTORMBCR,FEETYPE "
|
|
|
+ " FROM ch_fee WHERE BSNO = @bsno and FEENAME in (select [name] from code_fee where FEETYPE='普通') GROUP BY CURRENCY,EXCHANGERATE,FEETYPE ) AS C) AS A"
|
|
|
+ " WHERE fee_gain.BSNO = @bsno ";
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_UPDATE_FEE_GAIN_RECALCULATE, updateProfitParms);
|
|
|
}
|
|
|
}
|
|
|
#endregion
|
|
|
}
|
|
|
//
|
|
|
//事务提交
|
|
|
sqlTran.Commit();
|
|
|
|
|
|
iResult = 1;//状态为1表示插入成功
|
|
|
}
|
|
|
catch (Exception execError)
|
|
|
{
|
|
|
iResult = -1;//有异常,插入失败
|
|
|
sqlTran.Rollback();
|
|
|
iResult = -2;//插入异常,事务已回滚成功
|
|
|
throw execError;
|
|
|
}
|
|
|
finally
|
|
|
{
|
|
|
SqlHelper.CloseConnection();
|
|
|
}
|
|
|
}
|
|
|
return iResult;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 资通_ZX_装卸费入账，并锁定入库信息
|
|
|
/// </summary>
|
|
|
public int setislockzx(string sGids, string strUserID, string strislockdate)
|
|
|
{
|
|
|
int iResult = 0;
|
|
|
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
|
|
|
{
|
|
|
try
|
|
|
{
|
|
|
string strgids = "'" + sGids.Replace(",", "','") + "'";
|
|
|
string strSql = "update wms set ISLOCK=1,LOCKUSER='" + strUserID + "',LOCKTIME='" + strislockdate.Trim() + "' where gid in (" + strgids + ")";
|
|
|
iResult = -3;//
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
|
|
|
|
|
|
strSql = "update wms_in set ISLOCK=1,LOCKUSER='" + strUserID + "',LOCKTIME='" + strislockdate.Trim() + "' where ASSOCIATEDNO in (" + strgids + ")";
|
|
|
iResult = -4;//
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
|
|
|
|
|
|
//
|
|
|
string[] items = sGids.Trim().Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
|
|
|
for (int i = 0; i < items.Length; i++)
|
|
|
{
|
|
|
#region 费用参数
|
|
|
string PARM_FEE_GID = "@gid";
|
|
|
string PARM_FEE_BSNO = "@bsno";
|
|
|
string PARM_FEE_TYPE = "@fee_type";
|
|
|
string PARM_FEE_NAME = "@fee_name";
|
|
|
string PARM_FEE_REMARK = "@remark";
|
|
|
string PARM_FEE_FRT = "@fee_frt";
|
|
|
string PARM_FEE_AMOUNT = "@amount";
|
|
|
string PARM_FEE_CURRENCY = "@currency";
|
|
|
string PARM_FEE_QUANTITY = "@quantity";
|
|
|
string PARM_FEE_STATUS = "@fee_status";
|
|
|
string PARM_FEE_UNIT = "@unit";
|
|
|
string PARM_FEE_UNIT_PRICE = "@unit_price";
|
|
|
string PARM_FEE_COMMISSION_RATE = "@comm_rate";
|
|
|
string PARM_FEE_CUSTOMER_NAME = "@customer_name";
|
|
|
string PARM_FEE_EXCHANGE_RATE = "@exchange_rate";
|
|
|
string PARM_FEE_ISADVANCEPAY = "@is_advance_pay";
|
|
|
string PARM_FEE_ENTER_OPERATOR = "@enter_operator";
|
|
|
string PARM_FEE_AUDIT_WMSOUTBSNO = "@WMSOUTBSNO";
|
|
|
//
|
|
|
string strCUSTOMERNAME = "SELECT CUSTOMERNAME FROM wms WHERE GID = '" + items[i].ToString().Trim() + "'";
|
|
|
object statusObj = SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, strCUSTOMERNAME, null);
|
|
|
if (statusObj == null)
|
|
|
{
|
|
|
strCUSTOMERNAME = "";
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
strCUSTOMERNAME = statusObj.ToString().Trim();
|
|
|
}
|
|
|
//
|
|
|
string strTRUCKNAME = "SELECT TRUCKNAME FROM wms WHERE GID = '" + items[i].ToString().Trim() + "'";
|
|
|
statusObj = SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, strTRUCKNAME, null);
|
|
|
if (statusObj == null)
|
|
|
{
|
|
|
strTRUCKNAME = "";
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
strTRUCKNAME = statusObj.ToString().Trim();
|
|
|
}
|
|
|
//
|
|
|
int isFeeGain = 0;
|
|
|
#endregion
|
|
|
//
|
|
|
#region 应收费用合计入ch_fee表
|
|
|
string feehj = "SELECT isnull(sum(ARFEE),0) as hj FROM wms_in WHERE ASSOCIATEDNO = '" + items[i].ToString().Trim() + "'";
|
|
|
statusObj = SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, feehj, null);
|
|
|
if (statusObj == null)
|
|
|
{
|
|
|
feehj = "0";
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
feehj = statusObj.ToString().Trim();
|
|
|
}
|
|
|
//
|
|
|
if (decimal.Parse(feehj) != 0 && strCUSTOMERNAME != "")
|
|
|
{
|
|
|
isFeeGain = 1;
|
|
|
string SQL_INSERT_CH_FEE = " INSERT INTO ch_fee (GID,FEENAME,CUSTOMERNAME,UNIT,UNITPRICE,QUANTITY,COMMISSIONRATE,AMOUNT,CURRENCY,EXCHANGERATE,FEEFRT,REMARK,ISADVANCEDPAY,FEETYPE,BSNO,ENTEROPERATOR,ENTERDATE,FEESTATUS,WMSOUTBSNO) VALUES(@gid,@fee_name,@customer_name,@unit,@unit_price,@quantity,@comm_rate,@amount,@currency,@exchange_rate,@fee_frt,@remark,@is_advance_pay,@fee_type,@bsno,@enter_operator,@ENTERDATE,@fee_status,@WMSOUTBSNO) ";
|
|
|
SqlParameter[] insertParms = new SqlParameter[] {
|
|
|
new SqlParameter(PARM_FEE_GID,SqlDbType.VarChar,36),
|
|
|
new SqlParameter(PARM_FEE_NAME,SqlDbType.VarChar,100),
|
|
|
new SqlParameter(PARM_FEE_CUSTOMER_NAME,SqlDbType.VarChar,50),
|
|
|
new SqlParameter(PARM_FEE_UNIT,SqlDbType.VarChar,20),
|
|
|
new SqlParameter(PARM_FEE_UNIT_PRICE,SqlDbType.Decimal),
|
|
|
new SqlParameter(PARM_FEE_QUANTITY,SqlDbType.Decimal),
|
|
|
new SqlParameter(PARM_FEE_COMMISSION_RATE,SqlDbType.Decimal),
|
|
|
new SqlParameter(PARM_FEE_AMOUNT,SqlDbType.Decimal),
|
|
|
new SqlParameter(PARM_FEE_CURRENCY,SqlDbType.VarChar,20),
|
|
|
new SqlParameter(PARM_FEE_EXCHANGE_RATE,SqlDbType.Decimal),
|
|
|
new SqlParameter(PARM_FEE_FRT,SqlDbType.VarChar,20),
|
|
|
new SqlParameter(PARM_FEE_REMARK,SqlDbType.VarChar),
|
|
|
new SqlParameter(PARM_FEE_ISADVANCEPAY,SqlDbType.Bit),
|
|
|
new SqlParameter(PARM_FEE_TYPE,SqlDbType.Int),
|
|
|
new SqlParameter(PARM_FEE_BSNO,SqlDbType.VarChar,100),
|
|
|
new SqlParameter(PARM_FEE_ENTER_OPERATOR,SqlDbType.VarChar,50),
|
|
|
new SqlParameter("@ENTERDATE",SqlDbType.DateTime),
|
|
|
new SqlParameter(PARM_FEE_STATUS,SqlDbType.Int),
|
|
|
new SqlParameter(PARM_FEE_AUDIT_WMSOUTBSNO,SqlDbType.VarChar,100)
|
|
|
};
|
|
|
|
|
|
insertParms[0].Value = Guid.NewGuid().ToString();
|
|
|
insertParms[1].Value = "仓储装卸费";
|
|
|
insertParms[2].Value = strCUSTOMERNAME.Trim();
|
|
|
insertParms[3].Value = "重量";
|
|
|
insertParms[4].Value = decimal.Parse(feehj);
|
|
|
insertParms[5].Value = 1;
|
|
|
insertParms[6].Value = 1;
|
|
|
insertParms[7].Value = decimal.Parse(feehj);
|
|
|
insertParms[8].Value = "RMB";
|
|
|
insertParms[9].Value = 1;
|
|
|
insertParms[10].Value = "";
|
|
|
insertParms[11].Value = "";
|
|
|
insertParms[12].Value = false;
|
|
|
insertParms[13].Value = 1;
|
|
|
insertParms[14].Value = items[i].ToString().Trim();
|
|
|
insertParms[15].Value = strUserID;
|
|
|
insertParms[16].Value = DateTime.Parse(strislockdate.Trim());
|
|
|
insertParms[17].Value = 0;
|
|
|
insertParms[18].Value = items[i].ToString().Trim();
|
|
|
iResult = -5;//
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_CH_FEE, insertParms);
|
|
|
}
|
|
|
#endregion
|
|
|
//
|
|
|
#region 应付费用合计入ch_fee表
|
|
|
feehj = "SELECT isnull(sum(APFEE),0) as hj FROM wms_in WHERE ASSOCIATEDNO = '" + items[i].ToString().Trim() + "'";
|
|
|
statusObj = SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, feehj, null);
|
|
|
if (statusObj == null)
|
|
|
{
|
|
|
feehj = "0";
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
feehj = statusObj.ToString().Trim();
|
|
|
}
|
|
|
//
|
|
|
if (decimal.Parse(feehj) != 0 && strTRUCKNAME != "")
|
|
|
{
|
|
|
isFeeGain = 1;
|
|
|
string SQL_INSERT_CH_FEE = " INSERT INTO ch_fee (GID,FEENAME,CUSTOMERNAME,UNIT,UNITPRICE,QUANTITY,COMMISSIONRATE,AMOUNT,CURRENCY,EXCHANGERATE,FEEFRT,REMARK,ISADVANCEDPAY,FEETYPE,BSNO,ENTEROPERATOR,ENTERDATE,FEESTATUS,WMSOUTBSNO) VALUES(@gid,@fee_name,@customer_name,@unit,@unit_price,@quantity,@comm_rate,@amount,@currency,@exchange_rate,@fee_frt,@remark,@is_advance_pay,@fee_type,@bsno,@enter_operator,@ENTERDATE,@fee_status,@WMSOUTBSNO) ";
|
|
|
SqlParameter[] insertParms = new SqlParameter[] {
|
|
|
new SqlParameter(PARM_FEE_GID,SqlDbType.VarChar,36),
|
|
|
new SqlParameter(PARM_FEE_NAME,SqlDbType.VarChar,100),
|
|
|
new SqlParameter(PARM_FEE_CUSTOMER_NAME,SqlDbType.VarChar,50),
|
|
|
new SqlParameter(PARM_FEE_UNIT,SqlDbType.VarChar,20),
|
|
|
new SqlParameter(PARM_FEE_UNIT_PRICE,SqlDbType.Decimal),
|
|
|
new SqlParameter(PARM_FEE_QUANTITY,SqlDbType.Decimal),
|
|
|
new SqlParameter(PARM_FEE_COMMISSION_RATE,SqlDbType.Decimal),
|
|
|
new SqlParameter(PARM_FEE_AMOUNT,SqlDbType.Decimal),
|
|
|
new SqlParameter(PARM_FEE_CURRENCY,SqlDbType.VarChar,20),
|
|
|
new SqlParameter(PARM_FEE_EXCHANGE_RATE,SqlDbType.Decimal),
|
|
|
new SqlParameter(PARM_FEE_FRT,SqlDbType.VarChar,20),
|
|
|
new SqlParameter(PARM_FEE_REMARK,SqlDbType.VarChar),
|
|
|
new SqlParameter(PARM_FEE_ISADVANCEPAY,SqlDbType.Bit),
|
|
|
new SqlParameter(PARM_FEE_TYPE,SqlDbType.Int),
|
|
|
new SqlParameter(PARM_FEE_BSNO,SqlDbType.VarChar,100),
|
|
|
new SqlParameter(PARM_FEE_ENTER_OPERATOR,SqlDbType.VarChar,50),
|
|
|
new SqlParameter("@ENTERDATE",SqlDbType.DateTime),
|
|
|
new SqlParameter(PARM_FEE_STATUS,SqlDbType.Int),
|
|
|
new SqlParameter(PARM_FEE_AUDIT_WMSOUTBSNO,SqlDbType.VarChar,100)
|
|
|
};
|
|
|
|
|
|
insertParms[0].Value = Guid.NewGuid().ToString();
|
|
|
insertParms[1].Value = "仓储装卸费";
|
|
|
insertParms[2].Value = strTRUCKNAME.Trim();
|
|
|
insertParms[3].Value = "重量";
|
|
|
insertParms[4].Value = decimal.Parse(feehj);
|
|
|
insertParms[5].Value = 1;
|
|
|
insertParms[6].Value = 1;
|
|
|
insertParms[7].Value = decimal.Parse(feehj);
|
|
|
insertParms[8].Value = "RMB";
|
|
|
insertParms[9].Value = 1;
|
|
|
insertParms[10].Value = "";
|
|
|
insertParms[11].Value = "";
|
|
|
insertParms[12].Value = false;
|
|
|
insertParms[13].Value = 2;
|
|
|
insertParms[14].Value = items[i].ToString().Trim();
|
|
|
insertParms[15].Value = strUserID;
|
|
|
insertParms[16].Value = DateTime.Parse(strislockdate.Trim());
|
|
|
insertParms[17].Value = 0;
|
|
|
insertParms[18].Value = items[i].ToString().Trim();
|
|
|
iResult = -6;//
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_CH_FEE, insertParms);
|
|
|
}
|
|
|
#endregion
|
|
|
//
|
|
|
#region 保存利润信息
|
|
|
if (isFeeGain == 1)
|
|
|
{
|
|
|
string SQL_SELECT_FEE_PROFIT_BY_BSNO = "SELECT BSNO FROM fee_gain WHERE BSNO = '" + items[i].ToString().Trim() + "' ";
|
|
|
object bsnoObj = SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, SQL_SELECT_FEE_PROFIT_BY_BSNO, null);
|
|
|
if (bsnoObj == null)//如果没有相关利润信息,则插入新的利润信息
|
|
|
{
|
|
|
SqlParameter[] insertProfitParms = new SqlParameter[] {
|
|
|
new SqlParameter("@bsno",SqlDbType.VarChar,100),//委托编号
|
|
|
new SqlParameter("@create_user",SqlDbType.VarChar,36)//创建人
|
|
|
};
|
|
|
insertProfitParms[0].Value = items[i].ToString().Trim();
|
|
|
insertProfitParms[1].Value = strUserID;
|
|
|
//
|
|
|
string SQL_INSERT_FEE_GAIN_RECALCULATE = " INSERT INTO fee_gain(BSNO,RMBDR,USDDR,OTDR,RMBCR,USDCR,OTCR,STLDRRMB,STLDRUSD, "
|
|
|
+ " STLDROT,STLCRRMB,STLCRUSD,STLCROT,STLDRTTL,STLCRTTL,RMBPROFIT,USDPROFIT,OTPROFIT, "
|
|
|
+ " DRTTL,CRTTL,TTLPROFIT,PROFITRATE,CREATEUSER,CREATETIME,MODIFIEDUSER,MODIFIEDTIME) "
|
|
|
+ " SELECT * FROM ("
|
|
|
+ " SELECT BSNO,SUM(RMBDR) AS RMBDR,SUM(USDDR) AS USDDR,0 AS OTDR,SUM(RMBCR) AS RMBCR,SUM(USDCR) AS USDCR,0 AS OTCR, "
|
|
|
+ " 0 AS STLDRRMB,0 AS STLDRUSD,0 AS STLDROT,0 AS STLCRRMB,0 AS STLCRUSD,0 AS STLCROT,0 AS STLDRTTL,0 AS STLCRTTL,SUM(RMBDR) - SUM(RMBCR) AS RMBPROFIT, "
|
|
|
+ " SUM(USDDR) - SUM(USDCR) AS USDPROFIT,0 AS OTPROFIT FROM "
|
|
|
+ " (SELECT CURRENCY,BSNO,SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as RMBDR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as RMBCR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as USDDR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as USDCR,FEETYPE "
|
|
|
+ " FROM ch_fee WHERE BSNO = @bsno and FEENAME in (select [name] from code_fee where FEETYPE='普通') GROUP BY CURRENCY,EXCHANGERATE,FEETYPE,BSNO ) AS D GROUP BY BSNO) AS B, "
|
|
|
+ " (SELECT SUM(RMBDR+USDTORMBDR) as DRTTL,SUM(RMBCR+USDTORMBCR) as CRTTL,"
|
|
|
+ " SUM(RMBDR+USDTORMBDR)- SUM(RMBCR+USDTORMBCR) as TTLPROFIT,"
|
|
|
//+ " (CAST(CAST(CASE WHEN SUM(RMBCR+USDTORMBCR) = 0 THEN 100 ELSE (ROUND((case when SUM(RMBDR+USDTORMBDR)=0 then 0 else (SUM(RMBDR+USDTORMBDR)- SUM(RMBCR+USDTORMBCR))/SUM(RMBDR+USDTORMBDR) END),2))*100 END as decimal(18,2)) as varchar(20))+'%') AS PROFITRATE,"
|
|
|
+ " (CAST(CAST(CASE WHEN SUM(RMBCR+USDTORMBCR) = 0 THEN 100 ELSE (ROUND(((SUM(RMBDR+USDTORMBDR)- SUM(RMBCR+USDTORMBCR))/SUM(RMBCR+USDTORMBCR)),4))*100 END as decimal(18,2)) as varchar(20))+'%') AS PROFITRATE,"
|
|
|
+ " @create_user as CREATEUSER,GETDATE() as CREATETIME,@create_user as MODIFIEDUSER,GETDATE() as MODIFIEDTIME "
|
|
|
+ " FROM (SELECT CURRENCY,SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as RMBDR,"
|
|
|
+ " SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as RMBCR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as USDDR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as USDCR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 1 THEN AMOUNT*EXCHANGERATE ELSE 0 END) as USDTORMBDR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 2 THEN AMOUNT*EXCHANGERATE ELSE 0 END) as USDTORMBCR,FEETYPE "
|
|
|
+ " FROM ch_fee WHERE BSNO = @bsno and FEENAME in (select [name] from code_fee where FEETYPE='普通') GROUP BY CURRENCY,EXCHANGERATE,FEETYPE ) AS C) AS A ";
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_FEE_GAIN_RECALCULATE, insertProfitParms);
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
SqlParameter[] updateProfitParms = new SqlParameter[] {
|
|
|
new SqlParameter("@bsno",SqlDbType.VarChar,100),//委托编号
|
|
|
new SqlParameter("@modified_user",SqlDbType.VarChar,36)//创建人
|
|
|
};
|
|
|
updateProfitParms[0].Value = items[i].ToString().Trim();
|
|
|
updateProfitParms[1].Value = strUserID;
|
|
|
//
|
|
|
string SQL_UPDATE_FEE_GAIN_RECALCULATE = " UPDATE fee_gain SET RMBDR = B.RMBDR,RMBCR = B.RMBCR,USDDR = B.USDDR,USDCR = B.USDCR,RMBPROFIT = A.RMBPROFIT,"
|
|
|
+ " USDPROFIT = A.USDPROFIT,TTLPROFIT = A.TTLPROFIT,DRTTL = A.DRTTL,CRTTL = A.CRTTL,MODIFIEDUSER = @modified_user,MODIFIEDTIME = GETDATE(),PROFITRATE = A.PROFITRATE "
|
|
|
+ " FROM (SELECT SUM(RMBDR) AS RMBDR,SUM(RMBCR) AS RMBCR,SUM(USDDR) AS USDDR,SUM(USDCR) AS USDCR "
|
|
|
+ " FROM (SELECT CURRENCY,SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as RMBDR,"
|
|
|
+ " SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as RMBCR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as USDDR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as USDCR, "
|
|
|
+ " FEETYPE FROM ch_fee WHERE BSNO = @bsno and FEENAME in (select [name] from code_fee where FEETYPE='普通') GROUP BY CURRENCY,EXCHANGERATE,FEETYPE ) AS D) AS B, "
|
|
|
+ " (SELECT SUM(RMBDR) - SUM(RMBCR) AS RMBPROFIT,SUM(USDDR) - SUM(USDCR) AS USDPROFIT,SUM(RMBDR+USDTORMBDR) as DRTTL, "
|
|
|
+ " SUM(RMBCR+USDTORMBCR) as CRTTL,SUM(RMBDR+USDTORMBDR)- SUM(RMBCR+USDTORMBCR) as TTLPROFIT, "
|
|
|
//+ " (CAST(CAST(CASE WHEN SUM(RMBCR+USDTORMBCR) = 0 THEN 100 ELSE (ROUND((case when SUM(RMBDR+USDTORMBDR)=0 then 0 else (SUM(RMBDR+USDTORMBDR)- SUM(RMBCR+USDTORMBCR))/SUM(RMBDR+USDTORMBDR) END),2))*100 END as decimal(18,2)) as varchar(20))+'%') AS PROFITRATE "
|
|
|
+ " (CAST(CAST(CASE WHEN SUM(RMBCR+USDTORMBCR) = 0 THEN 100 ELSE (ROUND(((SUM(RMBDR+USDTORMBDR)- SUM(RMBCR+USDTORMBCR))/SUM(RMBCR+USDTORMBCR)),4))*100 END as decimal(18,2)) as varchar(20))+'%') AS PROFITRATE "
|
|
|
+ " FROM (SELECT CURRENCY,SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as RMBDR,"
|
|
|
+ " SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as RMBCR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as USDDR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as USDCR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 1 THEN AMOUNT*EXCHANGERATE ELSE 0 END) as USDTORMBDR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 2 THEN AMOUNT*EXCHANGERATE ELSE 0 END) as USDTORMBCR,FEETYPE "
|
|
|
+ " FROM ch_fee WHERE BSNO = @bsno and FEENAME in (select [name] from code_fee where FEETYPE='普通') GROUP BY CURRENCY,EXCHANGERATE,FEETYPE ) AS C) AS A"
|
|
|
+ " WHERE fee_gain.BSNO = @bsno ";
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_UPDATE_FEE_GAIN_RECALCULATE, updateProfitParms);
|
|
|
}
|
|
|
}
|
|
|
#endregion
|
|
|
}
|
|
|
//
|
|
|
//事务提交
|
|
|
sqlTran.Commit();
|
|
|
|
|
|
iResult = 1;//状态为1表示插入成功
|
|
|
}
|
|
|
catch (Exception execError)
|
|
|
{
|
|
|
iResult = -1;//有异常,插入失败
|
|
|
sqlTran.Rollback();
|
|
|
iResult = -2;//插入异常,事务已回滚成功
|
|
|
throw execError;
|
|
|
}
|
|
|
finally
|
|
|
{
|
|
|
SqlHelper.CloseConnection();
|
|
|
}
|
|
|
}
|
|
|
return iResult;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 装卸费取消入账，并解除锁定入库信息
|
|
|
/// </summary>
|
|
|
public int setnotlock(string strgids, string strUserID)
|
|
|
{
|
|
|
int iResult = 0;
|
|
|
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
|
|
|
{
|
|
|
try
|
|
|
{
|
|
|
string[] items = strgids.Trim().Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
|
|
|
for (int i = 0; i < items.Length; i++)
|
|
|
{
|
|
|
string strSql = "delete from ch_fee where WMSOUTBSNO in ('" + items[i].ToString().Trim() + "')";
|
|
|
iResult = -5;//
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
|
|
|
|
|
|
strSql = "update wms_in set ISLOCK=0,LOCKUSER='" + strUserID + "',LOCKTIME=getdate() where ASSOCIATEDNO in ('" + items[i].ToString().Trim() + "')";
|
|
|
iResult = -4;//
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
|
|
|
|
|
|
strSql = "update wms set ISLOCK=0,LOCKUSER='" + strUserID + "',LOCKTIME=getdate() where gid in ('" + items[i].ToString().Trim() + "')";
|
|
|
iResult = -3;//
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
|
|
|
|
|
|
//
|
|
|
#region 保存利润信息
|
|
|
string strASSOCIATEDNO = "SELECT ASSOCIATEDNO FROM wms WHERE GID in ('" + items[i].ToString().Trim() + "')";
|
|
|
object statusObj = SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, strASSOCIATEDNO, null);
|
|
|
if (statusObj == null)
|
|
|
{
|
|
|
strASSOCIATEDNO = items[i].ToString().Trim();
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
strASSOCIATEDNO = statusObj.ToString().Trim();
|
|
|
}
|
|
|
//
|
|
|
string SQL_SELECT_FEE_PROFIT_BY_BSNO = "SELECT BSNO FROM fee_gain WHERE BSNO = '" + strASSOCIATEDNO + "' ";
|
|
|
object bsnoObj = SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, SQL_SELECT_FEE_PROFIT_BY_BSNO, null);
|
|
|
if (bsnoObj == null)//如果没有相关利润信息,则插入新的利润信息
|
|
|
{
|
|
|
SqlParameter[] insertProfitParms = new SqlParameter[] {
|
|
|
new SqlParameter("@bsno",SqlDbType.VarChar,100),//委托编号
|
|
|
new SqlParameter("@create_user",SqlDbType.VarChar,36)//创建人
|
|
|
};
|
|
|
insertProfitParms[0].Value = strASSOCIATEDNO;
|
|
|
insertProfitParms[1].Value = strUserID;
|
|
|
//
|
|
|
string SQL_INSERT_FEE_GAIN_RECALCULATE = " INSERT INTO fee_gain(BSNO,RMBDR,USDDR,OTDR,RMBCR,USDCR,OTCR,STLDRRMB,STLDRUSD, "
|
|
|
+ " STLDROT,STLCRRMB,STLCRUSD,STLCROT,STLDRTTL,STLCRTTL,RMBPROFIT,USDPROFIT,OTPROFIT, "
|
|
|
+ " DRTTL,CRTTL,TTLPROFIT,PROFITRATE,CREATEUSER,CREATETIME,MODIFIEDUSER,MODIFIEDTIME) "
|
|
|
+ " SELECT * FROM ("
|
|
|
+ " SELECT BSNO,SUM(RMBDR) AS RMBDR,SUM(USDDR) AS USDDR,0 AS OTDR,SUM(RMBCR) AS RMBCR,SUM(USDCR) AS USDCR,0 AS OTCR, "
|
|
|
+ " 0 AS STLDRRMB,0 AS STLDRUSD,0 AS STLDROT,0 AS STLCRRMB,0 AS STLCRUSD,0 AS STLCROT,0 AS STLDRTTL,0 AS STLCRTTL,SUM(RMBDR) - SUM(RMBCR) AS RMBPROFIT, "
|
|
|
+ " SUM(USDDR) - SUM(USDCR) AS USDPROFIT,0 AS OTPROFIT FROM "
|
|
|
+ " (SELECT CURRENCY,BSNO,SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as RMBDR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as RMBCR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as USDDR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as USDCR,FEETYPE "
|
|
|
+ " FROM ch_fee WHERE BSNO = @bsno and FEENAME in (select [name] from code_fee where FEETYPE='普通') GROUP BY CURRENCY,EXCHANGERATE,FEETYPE,BSNO ) AS D GROUP BY BSNO) AS B, "
|
|
|
+ " (SELECT SUM(RMBDR+USDTORMBDR) as DRTTL,SUM(RMBCR+USDTORMBCR) as CRTTL,"
|
|
|
+ " SUM(RMBDR+USDTORMBDR)- SUM(RMBCR+USDTORMBCR) as TTLPROFIT,"
|
|
|
//+ " (CAST(CAST(CASE WHEN SUM(RMBCR+USDTORMBCR) = 0 THEN 100 ELSE (ROUND((case when SUM(RMBDR+USDTORMBDR)=0 then 0 else (SUM(RMBDR+USDTORMBDR)- SUM(RMBCR+USDTORMBCR))/SUM(RMBDR+USDTORMBDR) END),2))*100 END as decimal(18,2)) as varchar(20))+'%') AS PROFITRATE,"
|
|
|
+ " (CAST(CAST(CASE WHEN SUM(RMBCR+USDTORMBCR) = 0 THEN 100 ELSE (ROUND(((SUM(RMBDR+USDTORMBDR)- SUM(RMBCR+USDTORMBCR))/SUM(RMBCR+USDTORMBCR)),4))*100 END as decimal(18,2)) as varchar(20))+'%') AS PROFITRATE,"
|
|
|
+ " @create_user as CREATEUSER,GETDATE() as CREATETIME,@create_user as MODIFIEDUSER,GETDATE() as MODIFIEDTIME "
|
|
|
+ " FROM (SELECT CURRENCY,SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as RMBDR,"
|
|
|
+ " SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as RMBCR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as USDDR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as USDCR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 1 THEN AMOUNT*EXCHANGERATE ELSE 0 END) as USDTORMBDR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 2 THEN AMOUNT*EXCHANGERATE ELSE 0 END) as USDTORMBCR,FEETYPE "
|
|
|
+ " FROM ch_fee WHERE BSNO = @bsno and FEENAME in (select [name] from code_fee where FEETYPE='普通') GROUP BY CURRENCY,EXCHANGERATE,FEETYPE ) AS C) AS A ";
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_FEE_GAIN_RECALCULATE, insertProfitParms);
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
SqlParameter[] updateProfitParms = new SqlParameter[] {
|
|
|
new SqlParameter("@bsno",SqlDbType.VarChar,100),//委托编号
|
|
|
new SqlParameter("@modified_user",SqlDbType.VarChar,36)//创建人
|
|
|
};
|
|
|
updateProfitParms[0].Value = strASSOCIATEDNO;
|
|
|
updateProfitParms[1].Value = strUserID;
|
|
|
//
|
|
|
string SQL_UPDATE_FEE_GAIN_RECALCULATE = " UPDATE fee_gain SET RMBDR = B.RMBDR,RMBCR = B.RMBCR,USDDR = B.USDDR,USDCR = B.USDCR,RMBPROFIT = A.RMBPROFIT,"
|
|
|
+ " USDPROFIT = A.USDPROFIT,TTLPROFIT = A.TTLPROFIT,DRTTL = A.DRTTL,CRTTL = A.CRTTL,MODIFIEDUSER = @modified_user,MODIFIEDTIME = GETDATE(),PROFITRATE = A.PROFITRATE "
|
|
|
+ " FROM (SELECT SUM(RMBDR) AS RMBDR,SUM(RMBCR) AS RMBCR,SUM(USDDR) AS USDDR,SUM(USDCR) AS USDCR "
|
|
|
+ " FROM (SELECT CURRENCY,SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as RMBDR,"
|
|
|
+ " SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as RMBCR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as USDDR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as USDCR, "
|
|
|
+ " FEETYPE FROM ch_fee WHERE BSNO = @bsno and FEENAME in (select [name] from code_fee where FEETYPE='普通') GROUP BY CURRENCY,EXCHANGERATE,FEETYPE ) AS D) AS B, "
|
|
|
+ " (SELECT SUM(RMBDR) - SUM(RMBCR) AS RMBPROFIT,SUM(USDDR) - SUM(USDCR) AS USDPROFIT,SUM(RMBDR+USDTORMBDR) as DRTTL, "
|
|
|
+ " SUM(RMBCR+USDTORMBCR) as CRTTL,SUM(RMBDR+USDTORMBDR)- SUM(RMBCR+USDTORMBCR) as TTLPROFIT, "
|
|
|
//+ " (CAST(CAST(CASE WHEN SUM(RMBCR+USDTORMBCR) = 0 THEN 100 ELSE (ROUND((case when SUM(RMBDR+USDTORMBDR)=0 then 0 else (SUM(RMBDR+USDTORMBDR)- SUM(RMBCR+USDTORMBCR))/SUM(RMBDR+USDTORMBDR) END),2))*100 END as decimal(18,2)) as varchar(20))+'%') AS PROFITRATE "
|
|
|
+ " (CAST(CAST(CASE WHEN SUM(RMBCR+USDTORMBCR) = 0 THEN 100 ELSE (ROUND(((SUM(RMBDR+USDTORMBDR)- SUM(RMBCR+USDTORMBCR))/SUM(RMBCR+USDTORMBCR)),4))*100 END as decimal(18,2)) as varchar(20))+'%') AS PROFITRATE "
|
|
|
+ " FROM (SELECT CURRENCY,SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as RMBDR,"
|
|
|
+ " SUM(CASE WHEN CURRENCY='RMB' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as RMBCR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 1 THEN AMOUNT ELSE 0 END) as USDDR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 2 THEN AMOUNT ELSE 0 END) as USDCR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 1 THEN AMOUNT*EXCHANGERATE ELSE 0 END) as USDTORMBDR, "
|
|
|
+ " SUM(CASE WHEN CURRENCY='USD' AND FEETYPE = 2 THEN AMOUNT*EXCHANGERATE ELSE 0 END) as USDTORMBCR,FEETYPE "
|
|
|
+ " FROM ch_fee WHERE BSNO = @bsno and FEENAME in (select [name] from code_fee where FEETYPE='普通') GROUP BY CURRENCY,EXCHANGERATE,FEETYPE ) AS C) AS A"
|
|
|
+ " WHERE fee_gain.BSNO = @bsno ";
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_UPDATE_FEE_GAIN_RECALCULATE, updateProfitParms);
|
|
|
}
|
|
|
#endregion
|
|
|
}
|
|
|
|
|
|
//事务提交
|
|
|
sqlTran.Commit();
|
|
|
iResult = 1;//状态为1表示插入成功
|
|
|
}
|
|
|
catch (Exception execError)
|
|
|
{
|
|
|
iResult = -1;//有异常,插入失败
|
|
|
sqlTran.Rollback();
|
|
|
iResult = -2;//插入异常,事务已回滚成功
|
|
|
throw execError;
|
|
|
}
|
|
|
finally
|
|
|
{
|
|
|
SqlHelper.CloseConnection();
|
|
|
}
|
|
|
}
|
|
|
return iResult;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 更改应收开始计费日期
|
|
|
/// </summary>
|
|
|
public int updateStartBillingDate(string StartBillingDate, string FreeStorageperiod, string strParentGID, string strUserID, string strShowName)
|
|
|
{
|
|
|
int iResult = 0;
|
|
|
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
|
|
|
{
|
|
|
try
|
|
|
{
|
|
|
string strSql = "update wms set STARTBILLINGDATE='" + StartBillingDate + "', FREESTORAGEPERIOD=" + FreeStorageperiod + ", MODIFIEDUSER='" + strUserID + "', MODIFIEDTIME=getdate() where gid='" + strParentGID + "'";
|
|
|
iResult = -3;
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
|
|
|
|
|
|
strSql = "update wms_in set FREESTORAGEPERIOD=" + FreeStorageperiod + ", MODIFIEDUSER='" + strUserID + "', MODIFIEDTIME=getdate() where ASSOCIATEDNO='" + strParentGID + "'";
|
|
|
iResult = -4;
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, null);
|
|
|
|
|
|
#region 应收仓储费率重新计算结束计费日期
|
|
|
if (StartBillingDate != "")
|
|
|
{
|
|
|
DateTime sdate = DateTime.Parse(StartBillingDate);
|
|
|
string strSql2 = "SELECT GID,FEEGRADE,FEETYPE,FEESCALE,FEEUNIT,FEEPRICE,ADDPRICE,CAPPRICE,convert(char(10),ENDBILLINGDATE,20) AS ENDBILLINGDATE,REMARK,MODIFIEDUSER,MODIFIEDTIME,BSNO FROM wms_rate WHERE BSNO='" + strParentGID.ToString().Trim() + "' and FEETYPE=1 ORDER BY FEETYPE,FEEGRADE,CREATEUSER";
|
|
|
DataSet ds = SqlHelper.ExecuteQueryDataset(sqlTran, CommandType.Text, strSql2, null);
|
|
|
if (ds != null)
|
|
|
{
|
|
|
if (ds.Tables[0].Rows.Count > 0)
|
|
|
{
|
|
|
DateTime myDate = sdate;
|
|
|
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
|
|
|
{
|
|
|
#region 循环计算，并更新结束计费日期
|
|
|
string sGID = ds.Tables[0].Rows[i]["GID"].ToString().Trim();//惟一编号
|
|
|
string sFEETYPE = ds.Tables[0].Rows[i]["FEETYPE"].ToString().Trim();//费用类别
|
|
|
string sFEESCALE = ds.Tables[0].Rows[i]["FEESCALE"].ToString().Trim();//计费区间
|
|
|
string sFEEUNIT = ds.Tables[0].Rows[i]["FEEUNIT"].ToString().Trim();//计费单位
|
|
|
//
|
|
|
if (sFEEUNIT.IndexOf("月") > -1)
|
|
|
{
|
|
|
myDate = myDate.AddMonths(int.Parse(sFEESCALE.Trim()));
|
|
|
myDate = myDate.AddDays(-1);
|
|
|
sdate = myDate.Date;
|
|
|
}
|
|
|
else if (sFEEUNIT.IndexOf("天") > -1)
|
|
|
{
|
|
|
myDate = myDate.AddDays(int.Parse(sFEESCALE.Trim()) - 1);
|
|
|
sdate = myDate.Date;
|
|
|
}
|
|
|
//
|
|
|
string strSql3 = "update wms_rate set ENDBILLINGDATE='" + sdate + "',MODIFIEDUSER='" + strShowName + "',MODIFIEDTIME=getdate() where gid='" + sGID + "'";
|
|
|
iResult = -(i+5);
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql3, null);
|
|
|
//
|
|
|
myDate = myDate.AddDays(1);
|
|
|
#endregion
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
//事务提交
|
|
|
sqlTran.Commit();
|
|
|
|
|
|
iResult = 1;//状态为1表示插入成功
|
|
|
}
|
|
|
catch (Exception execError)
|
|
|
{
|
|
|
iResult = -1;//有异常,插入失败
|
|
|
sqlTran.Rollback();
|
|
|
iResult = -2;//插入异常,事务已回滚成功
|
|
|
throw execError;
|
|
|
}
|
|
|
finally
|
|
|
{
|
|
|
SqlHelper.CloseConnection();
|
|
|
}
|
|
|
}
|
|
|
return iResult;
|
|
|
}
|
|
|
|
|
|
//
|
|
|
}
|
|
|
}
|