wufan
/
ERP
2
0
Fork 0
Code Issues Pull Requests Packages Projects 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.
master
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
ERP/TradeData/JC_PostFeeService.cs

373 lines
17 KiB
C#
Raw Permalink Blame History

using System;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;
using NetLibrary;
using NetLibrary.Data;
using NetLibrary.ReportPrint;
using TradeModel;
namespace TradeData
{
public class JC_PostFeeService
{
#region 保存
public static void Save(JC_PostFee Model)
{
string tsql= @"
if @Id>0
begin
Update [JC_PostFee] set [ExpressID]=@ExpressID,[FeeType]=@FeeType,[CompanyId]=@CompanyId,[FeeDesc]=@FeeDesc where Id=@Id
--delete from JC_PostFeeDetail where FeeId=@Id
end
else
begin
INSERT INTO [JC_PostFee]([ExpressID],[FeeType],[CompanyId],[FeeDesc])values(@ExpressID,@FeeType,@CompanyId,@FeeDesc)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd,"@Id", DbType.Int32,Model.Id);
db.AddInParameter(cmd,"@ExpressID", DbType.Int32,Model.ExpressID);
db.AddInParameter(cmd,"@FeeType", DbType.Int32,Model.FeeType);
db.AddInParameter(cmd,"@CompanyId", DbType.Int32,Model.CompanyId);
db.AddInParameter(cmd,"@FeeDesc", DbType.String,Model.FeeDesc);
int a=Convert.ToInt32(db.ExecuteScalar(cmd));
tsql = @"
if @Id>0
begin
Update [JC_PostFeeDetail] set [FeeId]=@FeeId,[Country]=@Country,[SWeight]=@SWeight,[EWeight]=@EWeight,[FeeRate]=@FeeRate,[FeePrice]=@FeePrice,[WeightType]=@WeightType,[Groups]=@Groups,[CountryName]=@CountryName,[MoneyCode]=@MoneyCode where Id=@Id
end
else
begin
INSERT INTO [JC_PostFeeDetail]([FeeId],[Country],[SWeight],[EWeight],[FeeRate],[FeePrice],[WeightType],[Groups],[CountryName],[MoneyCode])values(@FeeId,@Country,@SWeight,@EWeight,@FeeRate,@FeePrice,@WeightType,@Groups,@CountryName,@MoneyCode)
set @Id=SCOPE_IDENTITY()
end
select @Id";
cmd = db.GetSqlStringCommand(tsql);
foreach (var item in Model.ListModel)
{
if (item.EWeight == null) item.EWeight = 0;
cmd.Parameters.Clear();
db.AddInParameter(cmd, "@Id", DbType.Int32, item.Id);
db.AddInParameter(cmd,"@FeeId", DbType.Int32,a);
db.AddInParameter(cmd,"@Country", DbType.String,item.Country);
db.AddInParameter(cmd,"@SWeight", DbType.Int32,item.SWeight);
db.AddInParameter(cmd,"@EWeight", DbType.Int32,item.EWeight);
db.AddInParameter(cmd,"@FeeRate", DbType.Decimal,item.FeeRate);
db.AddInParameter(cmd,"@FeePrice", DbType.Decimal,item.FeePrice);
db.AddInParameter(cmd,"@WeightType", DbType.Int32,item.WeightType);
db.AddInParameter(cmd, "@Groups", DbType.String, item.Groups);
db.AddInParameter(cmd, "@CountryName", DbType.String, item.CountryName);
db.AddInParameter(cmd, "@MoneyCode", DbType.String, item.MoneyCode);
db.ExecuteNonQuery(cmd);
}
}
#endregion
#region 删除
public static void DeletePostFeeDetail(int Id)
{
string tsql = @"
delete from JC_PostFeeDetail where Id=@Id
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 返回对象
public static JC_PostFee GetModel(int ExpressID)
{
string tsql = @"
select a.ExpressID,d.id,d.FeeType,a.CompanyId,d.FeeDesc,ExpressName=c.Name,a.OffNum
from JC_ExpressPost a
--inner join JC_ExpressPostDetail b on a.ExpressPostID=b.ExpressPostID and b.IsUse=1
inner join JC_Express c on a.ExpressID=c.ExpressID
left join JC_PostFee d on c.ExpressID=d.ExpressID
where a.ExpressID=@ExpressID
select a.Id,a.FeeId,a.Country,a.SWeight,a.EWeight,FeeRate=isnull(a.FeeRate,0),FeePrice=isnull(a.FeePrice,0),a.WeightType,a.Groups,CountryName=case when a.Country='0' then '不限' else a.CountryName end,MoneyCode=isnull(a.MoneyCode,'CNY'),CodeFee=isnull(a.CodeFee,0),a.PostOff,a.Groups,a.Groups2 from JC_PostFeeDetail a
inner join JC_PostFee b on a.FeeId=b.id
where b.ExpressID=@ExpressID
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@ExpressID", DbType.Int32, ExpressID);
DataSet ds = db.ExecuteDataSet(cmd);
JC_PostFee Model = ds.Tables[0].Rows[0].ToModel<JC_PostFee>();
if (Model.FeeType == 1) Model.FeeTypeName = "按每克费率计算";
if (Model.FeeType == 2) Model.FeeTypeName = "按首重续重计算";
Model.ListModel = ds.Tables[1].ToList<JC_PostFeeDetail>();
if (Model.ListModel == null) Model.ListModel = new List<JC_PostFeeDetail>();
return Model;
}
#endregion
#region 分页查询
public static List<JC_PostFee> GetListJC_PostFee(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"ExpressID=cast(b.ExpressID as int),d.id,d.FeeType,a.CompanyId,d.FeeDesc,ExpressName=c.Name";
ser.Tables = @"
JC_ExpressPost a
inner join JC_ExpressPostDetail b on a.ExpressPostID=b.ExpressPostID and b.IsUse=1
inner join JC_Express c on b.ExpressID=c.ExpressID
left join JC_PostFee d on c.ExpressID=d.ExpressID
";
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "ExpressID";
string tsql=ser.GetText();
where.AddOutParameter("RowCount",System.Data.DbType.Int32);
List<JC_PostFee> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
DataTable tb=db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel=tb.ToList<JC_PostFee>();
foreach (var item in ListModel)
{
if (item.FeeType == 1) item.FeeTypeName = "按克计算";
if (item.FeeType == 2) item.FeeTypeName = "按重量范围";
}
return ListModel;
}
#endregion
#region 保存
public static int SavePostFeeDetail(JC_PostFeeDetail Model)
{
string tsql = @"
if @FeeId is null
begin
INSERT INTO [JC_PostFee]
([ExpressID]
,[FeeType]
,[CompanyId])
VALUES (@ExpressId,1,@CompanyId)
set @FeeId=SCOPE_IDENTITY()
end
declare @CId int,@EnglishName nvarchar(100)
if @Country<>'0'
begin
set @CId=0
select top 1 @CId=Id from JC_Country where name=@Country or EnglishName=@Country order by IsUse desc
select @CountryCode=Code,@EnglishName=EnglishName,@CountryName=Name from JC_Country where Id=@CId
end
if @CountryName is null or @CountryName=''
begin
select 0
end
else
begin
if @Id>0
begin
Update [JC_PostFeeDetail] set [FeeId]=@FeeId,[Country]=@EnglishName,[SWeight]=@SWeight,[EWeight]=@EWeight,[FeeRate]=@FeeRate,[FeePrice]=@FeePrice,[WeightType]=@WeightType,[Groups]=@Groups,[CountryName]=@CountryName,[MoneyCode]=@MoneyCode,[Groups2]=@Groups2,[CodeFee]=@CodeFee,[PostOff]=@PostOff,[CountryCode]=@CountryCode where Id=@Id
end
else
begin
INSERT INTO [JC_PostFeeDetail]([FeeId],[Country],[SWeight],[EWeight],[FeeRate],[FeePrice],[WeightType],[Groups],[CountryName],[MoneyCode],[Groups2],[CodeFee],[PostOff],[CountryCode])values(@FeeId,@EnglishName,@SWeight,@EWeight,@FeeRate,@FeePrice,@WeightType,@Groups,@CountryName,@MoneyCode,@Groups2,@CodeFee,@PostOff,@CountryCode)
set @Id=SCOPE_IDENTITY()
end
--declare @ExpressId int
--select @ExpressId=ExpressID from JC_PostFee where Id=@FeeId
if(select count(0) from JC_ExpressCountry where ExpressId=@ExpressId and (Country=@EnglishName or CountryCode=@CountryCode))=0
begin
INSERT INTO JC_ExpressCountry([Country],[CountryCode],[ExpressId]) values(@EnglishName,@CountryCode,@ExpressId)
end
select @Id
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@FeeId", DbType.Int32, Model.FeeId);
db.AddInParameter(cmd, "@Country", DbType.String, Model.Country);
db.AddInParameter(cmd, "@SWeight", DbType.Int32, Model.SWeight);
db.AddInParameter(cmd, "@EWeight", DbType.Int32, Model.EWeight);
db.AddInParameter(cmd, "@FeeRate", DbType.Decimal, Model.FeeRate);
db.AddInParameter(cmd, "@FeePrice", DbType.Decimal, Model.FeePrice);
db.AddInParameter(cmd, "@WeightType", DbType.Int32, Model.WeightType);
db.AddInParameter(cmd, "@Groups", DbType.String, Model.Groups);
db.AddInParameter(cmd, "@CountryName", DbType.String, Model.CountryName);
db.AddInParameter(cmd, "@MoneyCode", DbType.String, Model.MoneyCode);
db.AddInParameter(cmd, "@Groups2", DbType.String, Model.Groups2);
db.AddInParameter(cmd, "@CodeFee", DbType.Decimal, Model.CodeFee);
db.AddInParameter(cmd, "@PostOff", DbType.Decimal, Model.PostOff);
db.AddInParameter(cmd, "@ExpressId", DbType.Int32, Model.ExpressId);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
db.AddInParameter(cmd, "@CountryCode", DbType.String, Model.CountryCode);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存
public static int SavePostFeeDetail22(JC_PostFeeDetail Model)
{
string tsql = @"
declare @CId int,@EnglishName nvarchar(100)
if @Country<>'0'
begin
set @CId=0
select top 1 @CId=Id from JC_Country where name=@Country or EnglishName=@Country order by IsUse desc
select @CountryCode=Code,@EnglishName=EnglishName,@CountryName=Name from JC_Country where Id=@CId
end
if @CountryName is null or @CountryName=''
begin
select 0
end
else
begin
select @Id=Id from JC_PostFeeDetail where [FeeId]=@FeeId and (Country=@EnglishName or Country=@CountryCode)
if @Id>0
begin
Update [JC_PostFeeDetail] set [FeeId]=@FeeId,[Country]=@EnglishName,[SWeight]=@SWeight,[EWeight]=@EWeight,[FeeRate]=@FeeRate,[FeePrice]=@FeePrice,[CountryName]=@CountryName,[MoneyCode]=@MoneyCode,[CodeFee]=@CodeFee,[CountryCode]=@CountryCode where Id=@Id
end
else
begin
INSERT INTO [JC_PostFeeDetail]([FeeId],[Country],[SWeight],[EWeight],[FeeRate],[FeePrice],[WeightType],[Groups],[CountryName],[MoneyCode],[Groups2],[CodeFee],[PostOff],[CountryCode])values(@FeeId,@EnglishName,@SWeight,@EWeight,@FeeRate,@FeePrice,@WeightType,@Groups,@CountryName,@MoneyCode,@Groups2,@CodeFee,@PostOff,@CountryCode)
set @Id=SCOPE_IDENTITY()
end
select @Id
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@FeeId", DbType.Int32, Model.FeeId);
db.AddInParameter(cmd, "@Country", DbType.String, Model.Country);
db.AddInParameter(cmd, "@SWeight", DbType.Int32, Model.SWeight);
db.AddInParameter(cmd, "@EWeight", DbType.Int32, Model.EWeight);
db.AddInParameter(cmd, "@FeeRate", DbType.Decimal, Model.FeeRate);
db.AddInParameter(cmd, "@FeePrice", DbType.Decimal, Model.FeePrice);
db.AddInParameter(cmd, "@WeightType", DbType.Int32, Model.WeightType);
db.AddInParameter(cmd, "@Groups", DbType.String, Model.Groups);
db.AddInParameter(cmd, "@CountryName", DbType.String, Model.CountryName);
db.AddInParameter(cmd, "@MoneyCode", DbType.String, Model.MoneyCode);
db.AddInParameter(cmd, "@Groups2", DbType.String, Model.Groups2);
db.AddInParameter(cmd, "@CodeFee", DbType.Decimal, Model.CodeFee);
db.AddInParameter(cmd, "@PostOff", DbType.Decimal, Model.PostOff);
db.AddInParameter(cmd, "@ExpressId", DbType.Int32, Model.ExpressId);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
db.AddInParameter(cmd, "@CountryCode", DbType.String, Model.CountryCode);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存
public static int SavePostFee (JC_PostFee Model)
{
string tsql = @"
select @Id=Id from JC_PostFee where ExpressID=@ExpressID and CompanyId=@CompanyId
if @Id>0
begin
Update [JC_PostFee] set [ExpressID]=@ExpressID,[FeeType]=@FeeType,[CompanyId]=@CompanyId,[FeeDesc]=@FeeDesc where Id=@Id
--delete from JC_PostFeeDetail where FeeId=@Id
end
else
begin
INSERT INTO [JC_PostFee]([ExpressID],[FeeType],[CompanyId],[FeeDesc])values(@ExpressID,@FeeType,@CompanyId,@FeeDesc)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@ExpressID", DbType.Int32, Model.ExpressID);
db.AddInParameter(cmd, "@FeeType", DbType.Int32, Model.FeeType);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
db.AddInParameter(cmd, "@FeeDesc", DbType.String, Model.FeeDesc);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存
public static int SavePostFeeDetail2(JC_PostFeeDetail Model)
{
string tsql = @"
if @FeeId is null
begin
INSERT INTO [JC_PostFee]
([ExpressID]
,[FeeType]
,[CompanyId])
VALUES (@ExpressId,1,@CompanyId)
set @FeeId=SCOPE_IDENTITY()
end
declare @CId int,@EnglishName nvarchar(100)
if @Country<>'0'
begin
set @CId=0
select top 1 @CId=Id from JC_Country where name=@Country or EnglishName=@Country order by IsUse desc
select @CountryCode=Code,@EnglishName=EnglishName,@CountryName=Name from JC_Country where Id=@CId
end
if @CountryName is null or @CountryName=''
begin
select 0
end
else
begin
if @Id>0
begin
Update [JC_PostFeeDetail] set [FeeId]=@FeeId,[Country]=@EnglishName,[SWeight]=@SWeight,[EWeight]=@EWeight,[FeeRate]=@FeeRate,[FeePrice]=@FeePrice,[WeightType]=@WeightType,[Groups]=@Groups,[CountryName]=@CountryName,[MoneyCode]=@MoneyCode,[Groups2]=@Groups2,[CodeFee]=@CodeFee,[PostOff]=@PostOff,[CountryCode]=@CountryCode where Id=@Id
end
else
begin
INSERT INTO [JC_PostFeeDetail]([FeeId],[Country],[SWeight],[EWeight],[FeeRate],[FeePrice],[WeightType],[Groups],[CountryName],[MoneyCode],[Groups2],[CodeFee],[PostOff],[CountryCode])values(@FeeId,@EnglishName,@SWeight,@EWeight,@FeeRate,@FeePrice,@WeightType,@Groups,@CountryName,@MoneyCode,@Groups2,@CodeFee,@PostOff,@CountryCode)
set @Id=SCOPE_IDENTITY()
end
--declare @ExpressId int
--select @ExpressId=ExpressID from JC_PostFee where Id=@FeeId
if(select count(0) from JC_ExpressCountry where ExpressId=@ExpressId and (Country=@EnglishName or CountryCode=@CountryCode))=0
begin
INSERT INTO JC_ExpressCountry([Country],[CountryCode],[ExpressId]) values(@EnglishName,@CountryCode,@ExpressId)
end
select @Id
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@FeeId", DbType.Int32, Model.FeeId);
db.AddInParameter(cmd, "@Country", DbType.String, Model.Country);
db.AddInParameter(cmd, "@SWeight", DbType.Int32, Model.SWeight);
db.AddInParameter(cmd, "@EWeight", DbType.Int32, Model.EWeight);
db.AddInParameter(cmd, "@FeeRate", DbType.Decimal, Model.FeeRate);
db.AddInParameter(cmd, "@FeePrice", DbType.Decimal, Model.FeePrice);
db.AddInParameter(cmd, "@WeightType", DbType.Int32, Model.WeightType);
db.AddInParameter(cmd, "@Groups", DbType.String, Model.Groups);
db.AddInParameter(cmd, "@CountryName", DbType.String, Model.CountryName);
db.AddInParameter(cmd, "@MoneyCode", DbType.String, Model.MoneyCode);
db.AddInParameter(cmd, "@Groups2", DbType.String, Model.Groups2);
db.AddInParameter(cmd, "@CodeFee", DbType.Decimal, Model.CodeFee);
db.AddInParameter(cmd, "@PostOff", DbType.Decimal, Model.PostOff);
db.AddInParameter(cmd, "@ExpressId", DbType.Int32, Model.ExpressId);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
db.AddInParameter(cmd, "@CountryCode", DbType.String, Model.CountryCode);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
}
}
Reference in New Issue View Git Blame Copy Permalink