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.
556 lines
25 KiB
C#
556 lines
25 KiB
C#
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_ExpressService
|
|
{
|
|
// #region 快递初始化
|
|
// public static void InitExpress(int CompanyId)
|
|
// {
|
|
// if (CompanyId <= 0) return;
|
|
// string tsql = @"
|
|
//insert JC_ExpressPost(CompanyId,Name,PostType,IsUse)
|
|
//select @CompanyId,Name,SortNo,1
|
|
//from JC_BaseCodeDetail
|
|
//where KeyName='fhkd' and IsUse=1 and SortNo not in (select PostType from JC_ExpressPost)
|
|
//
|
|
//insert JC_ExpressPostDetail(ExpressPostID,ExpressID,IsUse)
|
|
//select b.ExpressPostID,a.ExpressID,1
|
|
//from JC_Express a
|
|
//inner join JC_ExpressPost b on a.PostType=b.PostType
|
|
//where b.CompanyId=@CompanyId and a.ExpressID not in (select ExpressID from JC_ExpressPostDetail)
|
|
//";
|
|
// Database db = DatabaseFactory.CreateDatabase();
|
|
// DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
// db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
// DataTable tb = db.ExecuteDataTable(cmd);
|
|
// }
|
|
// #endregion
|
|
// #region 保存
|
|
// public static void Save(JC_ExpressPost Model)
|
|
// {
|
|
// string tsql= @"
|
|
//Update [JC_ExpressPost] set [UserCode]=@UserCode,[CheckCode]=@CheckCode,IsUse=@IsUse where ExpressPostID=@ExpressPostID
|
|
//";
|
|
// Database db = DatabaseFactory.CreateDatabase();
|
|
// DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
// db.AddInParameter(cmd, "@ExpressPostID", DbType.Int32, Model.ExpressPostID);
|
|
// //db.AddInParameter(cmd,"@Name", DbType.String,Model.Name);
|
|
// db.AddInParameter(cmd,"@UserCode", DbType.String,Model.UserCode);
|
|
// db.AddInParameter(cmd,"@CheckCode", DbType.String,Model.CheckCode);
|
|
// db.AddInParameter(cmd, "@IsUse", DbType.Boolean, Model.IsUse);
|
|
// db.ExecuteNonQuery(cmd);
|
|
// tsql= @"
|
|
//Update [JC_ExpressPostDetail] set [IsUse]=@IsUse,operationtype=@operationtype,clcttype=@clcttype,customercode=@customercode,vipcode=@vipcode where ExpressPostID2=@ExpressPostID2
|
|
//";
|
|
// cmd = db.GetSqlStringCommand(tsql);
|
|
// foreach (var item in Model.ListModel)
|
|
// {
|
|
// cmd.Parameters.Clear();
|
|
// if (Model.IsUse == false) item.IsUse = false;
|
|
// db.AddInParameter(cmd, "@ExpressPostID2", DbType.Int32, item.ExpressPostID2);
|
|
// db.AddInParameter(cmd, "@IsUse", DbType.Boolean, item.IsUse);
|
|
// db.AddInParameter(cmd, "@operationtype", DbType.Int32, item.operationtype);
|
|
// db.AddInParameter(cmd, "@clcttype", DbType.Int32, item.clcttype);
|
|
// db.AddInParameter(cmd, "@customercode", DbType.String, item.customercode);
|
|
// db.AddInParameter(cmd, "@vipcode", DbType.String, item.vipcode);
|
|
// db.ExecuteNonQuery(cmd);
|
|
// }
|
|
// }
|
|
// #endregion
|
|
#region 分页查询
|
|
public static List<JC_ExpressPost> GetListJC_Express(int CompanyId, int LogisticsId,int IsAddr)
|
|
{
|
|
string tsql = @"
|
|
if @IsAddr=1
|
|
begin
|
|
select a.*,d.LogisticsName,c.LogisticsId,IsAddr=case when DefaultAddr is null then '未设置' else '已设置' end
|
|
from JC_ExpressPost a
|
|
inner join JC_Express c on a.ExpressID=c.ExpressID
|
|
inner join JC_ExpressType d on c.LogisticsId=d.LogisticsId
|
|
where a.CompanyId=@CompanyId and a.DefaultAddr is null and (@LogisticsId=0 or c.LogisticsId=@LogisticsId) order by c.LogisticsId
|
|
end
|
|
else
|
|
begin
|
|
select a.*,d.LogisticsName,c.LogisticsId,IsAddr=case when DefaultAddr is null then '未设置' else '已设置' end
|
|
from JC_ExpressPost a
|
|
inner join JC_Express c on a.ExpressID=c.ExpressID
|
|
inner join JC_ExpressType d on c.LogisticsId=d.LogisticsId
|
|
where a.CompanyId=@CompanyId and (@LogisticsId=0 or c.LogisticsId=@LogisticsId) order by c.LogisticsId
|
|
end
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
db.AddInParameter(cmd, "@LogisticsId", DbType.Int32, LogisticsId);
|
|
db.AddInParameter(cmd, "@IsAddr", DbType.Int32, IsAddr);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
List<JC_ExpressPost> ListModel = ds.Tables[0].ToList<JC_ExpressPost>();
|
|
//if (ListModel == null) return null;
|
|
//foreach (JC_ExpressPost model in ListModel)
|
|
//{
|
|
// model.ListModel = new List<JC_ExpressDetail>();
|
|
// DataRow[] drow = ds.Tables[1].Select("ExpressPostID=" + model.ExpressPostID);
|
|
// foreach (DataRow row2 in drow)
|
|
// {
|
|
// JC_ExpressDetail model2 = new JC_ExpressDetail();
|
|
// model2.ExpressPostID2 = Convert.ToInt32(row2["ExpressPostID2"]);
|
|
// model2.ExpressPostID = Convert.ToInt32(row2["ExpressPostID"]);
|
|
// model2.ExpressID = Convert.ToInt32(row2["ExpressID"]);
|
|
// model2.Name = Convert.ToString(row2["Name"]);
|
|
// model2.IsUse = Convert.ToBoolean(row2["IsUse"]);
|
|
// if (row2.IsNull("operationtype") == false) model2.operationtype = Convert.ToInt32(row2["operationtype"]);
|
|
// model2.customercode = Convert.ToString(row2["customercode"]);
|
|
// model2.vipcode = Convert.ToString(row2["vipcode"]);
|
|
// if (row2.IsNull("clcttype") == false) model2.clcttype = Convert.ToInt32(row2["clcttype"]);
|
|
// model.ListModel.Add(model2);
|
|
// }
|
|
//}
|
|
return ListModel;
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 获取物流对象
|
|
public JC_ExpressPost GetExpressModel(int CompanyId, int ExpressID)
|
|
{
|
|
JC_ExpressPost model = null;
|
|
string tsql = @"select a.ExpressPostID,a.ExpressID,a.CompanyId,a.Name,c.PostType,a.IsUse,a.UserCode,a.CheckCode,a.operationtype,a.customercode,a.vipcode,a.clcttype,a.DefaultAddr,a.client_id,a.client_secret,a.refresh_token,a.GetTime,a.Code,a.redirect_uri,c.EName,c.LogisticsId from [JC_ExpressPost] a
|
|
--inner join JC_ExpressPostDetail b on a.ExpressPostID=b.ExpressPostID
|
|
inner join JC_Express c on a.ExpressID=c.ExpressID
|
|
where a.ExpressID=@ExpressID and a.CompanyId=@CompanyId";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
db.AddInParameter(cmd, "@ExpressID", DbType.Int32, ExpressID);
|
|
DataTable tb = db.ExecuteDataTable(cmd);
|
|
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<JC_ExpressPost>();
|
|
return model;
|
|
}
|
|
#endregion
|
|
#region 获取物流对象
|
|
public List<JC_ExpressPost> GetOlineExpressModel(int LogisticsId)
|
|
{
|
|
List<JC_ExpressPost> List = null;
|
|
string tsql = @"select a.ExpressPostID,a.ExpressID,a.CompanyId,a.Name,c.PostType,a.IsUse,a.UserCode,a.CheckCode,a.operationtype,a.customercode,a.vipcode,a.clcttype,a.DefaultAddr,a.client_id,a.client_secret,a.refresh_token,a.GetTime,a.Code,a.redirect_uri,c.EName,c.LogisticsId from [JC_ExpressPost] a
|
|
--inner join JC_ExpressPostDetail b on a.ExpressPostID=b.ExpressPostID
|
|
inner join JC_Express c on a.ExpressID=c.ExpressID
|
|
where c.LogisticsId=@LogisticsId";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@LogisticsId", DbType.Int32, LogisticsId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
List = ds.Tables[0].ToList<JC_ExpressPost>();
|
|
|
|
return List;
|
|
}
|
|
#endregion
|
|
#region 验证国家
|
|
public int IsExpressCountry(int ExpressID, string Country)
|
|
{
|
|
|
|
string tsql = @"
|
|
declare @code nvarchar(100),@Name nvarchar(100)
|
|
select @code=code,@Name=EnglishName from JC_Country where (Code=@Country or Name=@Country) and IsUse=1
|
|
|
|
select count(0) from JC_ExpressCountry where (CountryCode=@code and CountryCode=@Name)";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@Country", DbType.String, Country);
|
|
db.AddInParameter(cmd, "@ExpressID", DbType.Int32, ExpressID);
|
|
int a= Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
#region 获取物流发件地址
|
|
public JC_PostAddress GetSendAddress(int CompanyId)
|
|
{
|
|
JC_PostAddress model = new JC_PostAddress();
|
|
string tsql = @"
|
|
SELECT * FROM dbo.JC_PostAddress where CompanyId=@CompanyId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
DataTable tb = db.ExecuteDataTable(cmd);
|
|
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<JC_PostAddress>();
|
|
return model;
|
|
}
|
|
#endregion
|
|
#region 获取内置物流单号
|
|
public string GetInnerExpressCode(int CompanyId, int ExpressID)
|
|
{
|
|
|
|
string tsql = @"
|
|
declare @Id int,@PostCode nvarchar(50)
|
|
select top 1 @Id=Id,@PostCode=PostCode from JC_ExpressCode where ExpressID=@ExpressID and IsUse=0 and CompanyId=@CompanyId
|
|
update JC_ExpressCode set Isuse=1,UpdateDate=getdate() where Id=@Id
|
|
select @PostCode
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
db.AddInParameter(cmd, "@ExpressID", DbType.Int32, ExpressID);
|
|
string Code=Convert.ToString(db.ExecuteScalar(cmd));
|
|
|
|
return Code;
|
|
}
|
|
#endregion
|
|
|
|
#region 获取内置规则物流单号中邮
|
|
public string GetGZExpressCode(int CompanyId, int ExpressID)
|
|
{
|
|
string tsql = @"
|
|
declare @Id int,@PostCode nvarchar(50)
|
|
select top 1 @Id=ExpressPostID,@PostCode=([TrackNumberHead]+(RIGHT('00000000'+CAST( TrackNumberBegin AS nvarchar(50)),8))+[TrackNumberEnd]) from [JC_ExpressPost] where ExpressID=@ExpressID and CompanyId=@CompanyId
|
|
update [JC_ExpressPost] set [TrackNumberBegin]=[TrackNumberBegin]+1 where ExpressPostID=@Id
|
|
select @PostCode
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
db.AddInParameter(cmd, "@ExpressID", DbType.Int32, ExpressID);
|
|
string Code = Convert.ToString(db.ExecuteScalar(cmd));
|
|
|
|
return Code;
|
|
}
|
|
#endregion
|
|
#region 获取内置规则物流单号德邮
|
|
public string GetGZExpressCodeDY(int CompanyId, int ExpressID)
|
|
{
|
|
string tsql = @"
|
|
declare @Id int,@PostCode nvarchar(50)
|
|
select top 1 @Id=ExpressPostID,@PostCode=([TrackNumberHead]+(RIGHT('000000000'+CAST( TrackNumberBegin AS nvarchar(50)),9))) from [JC_ExpressPost] where ExpressID=@ExpressID and CompanyId=@CompanyId
|
|
update [JC_ExpressPost] set [TrackNumberBegin]=[TrackNumberBegin]+1 where ExpressPostID=@Id
|
|
select @PostCode
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
db.AddInParameter(cmd, "@ExpressID", DbType.Int32, ExpressID);
|
|
string Code = Convert.ToString(db.ExecuteScalar(cmd));
|
|
|
|
return Code;
|
|
}
|
|
#endregion
|
|
#region 获取内置规则物流单号BPost
|
|
public string GetGZExpressCodeBP(int CompanyId, int ExpressID)
|
|
{
|
|
string tsql = @"
|
|
declare @Id int,@PostCode nvarchar(50)
|
|
select top 1 @Id=ExpressPostID,@PostCode=([TrackNumberHead]+(RIGHT('00000000'+CAST(TrackNumberBegin AS nvarchar(50)),8))) from [JC_ExpressPost] where ExpressID=@ExpressID and CompanyId=@CompanyId
|
|
update [JC_ExpressPost] set [TrackNumberBegin]=[TrackNumberBegin]+1 where ExpressPostID=@Id
|
|
select @PostCode
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
db.AddInParameter(cmd, "@ExpressID", DbType.Int32, ExpressID);
|
|
string Code = Convert.ToString(db.ExecuteScalar(cmd));
|
|
|
|
return Code;
|
|
}
|
|
#endregion
|
|
|
|
#region 返回Model
|
|
public JC_Express GetExpressModel(int ExpressID)
|
|
{
|
|
JC_Express model = null;
|
|
string tsql = "select * from JC_Express where ExpressID=@ExpressID";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ExpressID", DbType.Int32, ExpressID);
|
|
DataTable tb = db.ExecuteDataTable(cmd);
|
|
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<JC_Express>();
|
|
return model;
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 查询物流
|
|
public List<JC_ExpressType> GetExpressTypeList()
|
|
{
|
|
List<JC_ExpressType> List = null;
|
|
string tsql = @"
|
|
select * from JC_ExpressType
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
List = ds.Tables[0].ToList<JC_ExpressType>();
|
|
|
|
return List;
|
|
}
|
|
#endregion
|
|
|
|
#region 查询渠道
|
|
public List<JC_Express> GetExpressList()
|
|
{
|
|
List<JC_Express> List = null;
|
|
string tsql = @"
|
|
select * from JC_Express
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
List = ds.Tables[0].ToList<JC_Express>();
|
|
|
|
return List;
|
|
}
|
|
#endregion
|
|
|
|
|
|
|
|
|
|
|
|
#region 保存物流渠道
|
|
public static int SaveExpressPost(JC_ExpressPost Model)
|
|
{
|
|
string tsql = @"
|
|
if @ExpressPostID>0
|
|
begin
|
|
Update [JC_ExpressPost] set [CompanyId]=@CompanyId,[Name]=@Name,[IsUse]=@IsUse,[UserCode]=@UserCode,[CheckCode]=@CheckCode,[DefaultAddr]=@DefaultAddr where ExpressPostID=@ExpressPostID
|
|
update JC_Express set Name=@Name where ExpressID=@ExpressID
|
|
end
|
|
else
|
|
begin
|
|
declare @PrintTemplateName nvarchar(200),@PrintTemplateName2 nvarchar(200)
|
|
if @TempId>0
|
|
begin
|
|
select @PrintTemplateName=PrintTemplateName,@PrintTemplateName2=PrintTemplateName2 from JC_Express where ExpressID=@TempId
|
|
select top 1 @PostType=PostType from JC_Express where LogisticsId=@LogisticsId
|
|
end
|
|
INSERT INTO [JC_Express]([Name],[EName],[PrintTemplateName],[PrintTemplateName2],[Remark],[PostType],[LogisticsId])
|
|
values(@Name,@Name,@PrintTemplateName,@PrintTemplateName2,'',5,@LogisticsId)
|
|
set @ExpressID=SCOPE_IDENTITY()
|
|
INSERT INTO [JC_ExpressPost]([CompanyId],[Name],[PostType],[IsUse],[UserCode],[CheckCode],[ExpressID],[operationtype],[customercode],[vipcode],[clcttype],[DefaultAddr],[IsSys])
|
|
values(@CompanyId,@Name,@PostType,@IsUse,@UserCode,@CheckCode,@ExpressID,@operationtype,@customercode,@vipcode,@clcttype,@DefaultAddr,@IsSys)
|
|
set @ExpressPostID=SCOPE_IDENTITY()
|
|
INSERT INTO [JC_ExpressCountry]([Country],[CountryCode],[ExpressId])
|
|
values('不限','0',@ExpressID)
|
|
end
|
|
select @ExpressPostID";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ExpressPostID", DbType.Int32, Model.ExpressPostID);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
|
|
db.AddInParameter(cmd, "@Name", DbType.String, Model.Name);
|
|
db.AddInParameter(cmd, "@PostType", DbType.Int32, Model.PostType);
|
|
db.AddInParameter(cmd, "@IsUse", DbType.Boolean, Model.IsUse);
|
|
db.AddInParameter(cmd, "@UserCode", DbType.String, Model.UserCode);
|
|
db.AddInParameter(cmd, "@CheckCode", DbType.String, Model.CheckCode);
|
|
db.AddInParameter(cmd, "@ExpressID", DbType.Int32, Model.ExpressID);
|
|
db.AddInParameter(cmd, "@operationtype", DbType.Int32, Model.operationtype);
|
|
db.AddInParameter(cmd, "@customercode", DbType.String, Model.customercode);
|
|
db.AddInParameter(cmd, "@vipcode", DbType.String, Model.vipcode);
|
|
db.AddInParameter(cmd, "@clcttype", DbType.Int32, Model.clcttype);
|
|
db.AddInParameter(cmd, "@DefaultAddr", DbType.Int32, Model.DefaultAddr);
|
|
db.AddInParameter(cmd, "@IsSys", DbType.Int32, Model.IsSys);
|
|
db.AddInParameter(cmd, "@TempId", DbType.Int32, Model.TempId);
|
|
db.AddInParameter(cmd, "@LogisticsId", DbType.Int32, Model.LogisticsId);
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
|
|
#region 设置地址
|
|
public static int UpdateExpressAddr(int ExpressPostID,int AddrId)
|
|
{
|
|
string tsql = @"
|
|
|
|
Update [JC_ExpressPost] set [DefaultAddr]=@AddrId where ExpressPostID=@ExpressPostID
|
|
select @ExpressPostID";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ExpressPostID", DbType.Int32, ExpressPostID);
|
|
db.AddInParameter(cmd, "@AddrId", DbType.Int32, AddrId);
|
|
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
#region 设置地址
|
|
public static void DeleteExpress(int ExpressPostID)
|
|
{
|
|
string tsql = @"
|
|
delete from JC_Express where ExpressID in (select ExpressID from [JC_ExpressPost] where ExpressPostID=@ExpressPostID)
|
|
delete from [JC_ExpressPost] where ExpressPostID=@ExpressPostID";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ExpressPostID", DbType.Int32, ExpressPostID);
|
|
|
|
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
#region 查询物流面板
|
|
public List<JC_Express> GetExpressTemp(int LogisticsId)
|
|
{
|
|
List<JC_Express> List = null;
|
|
string tsql = @"
|
|
select ExpressID,Name=Name+'的面板' from JC_Express where LogisticsId=@LogisticsId and PrintTemplateName is not null
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@LogisticsId", DbType.Int32, LogisticsId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
List = ds.Tables[0].ToList<JC_Express>();
|
|
|
|
return List;
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 返回Model
|
|
public static int GetExpressWeight(int ExpressID)
|
|
{
|
|
|
|
string tsql = @"select top 1 IsWeight=isnull(a.IsWeight,0) from JC_ExpressType a
|
|
inner join JC_Express b on a.LogisticsId=b.LogisticsId
|
|
where b.ExpressID=@ExpressID";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ExpressID", DbType.Int32, ExpressID);
|
|
int a=Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
|
|
return a;
|
|
}
|
|
#endregion
|
|
|
|
#region 查询oneworld渠道
|
|
public List<ShippingMethods> GetShippingMethods()
|
|
{
|
|
|
|
List<ShippingMethods> list = null;
|
|
string tsql = @"select * from OneWord";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<ShippingMethods>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 分页查询物流比较
|
|
public List<DT_TrackCodeCompare> GetTrackCodeCompare(int TJ,RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
|
|
{
|
|
if (where == null) where = new RefParameterCollection();
|
|
QueryService ser = new QueryService();
|
|
if (TJ == 2)
|
|
{
|
|
ser.Fields = @"Id=cast(a.Id as int),a.TrackCode,a.Weight,PostFee=a.Fee,MyWeight=cast(b.Weight as Decimal),MyPostFee=b.PostFee,b.OrderCode,WeightOff=isnull(a.weight,0)-isnull(b.Weight,0),FeeOff=isnull(a.Fee,0)-isnull(b.PostFee,0)";
|
|
ser.Tables = @"WL_TrackCompareDetail a
|
|
left join DT_TrackCodeScan b on a.TrackCode=b.TrackCode";
|
|
}
|
|
else
|
|
{
|
|
ser.Fields = @"Id=cast(a.Id as int),a.TrackCode,a.Weight,PostFee=a.Fee,MyWeight=cast(b.Weight as Decimal),MyPostFee=b.PostFee,b.OrderCode,WeightOff=isnull(a.weight,0)-isnull(b.Weight,0),FeeOff=isnull(a.Fee,0)-isnull(b.PostFee,0)";
|
|
ser.Tables = @"WL_TrackCompareDetail a
|
|
inner join DT_TrackCodeScan b on a.TrackCode=b.TrackCode";
|
|
}
|
|
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
|
|
ser.PageIndex = PageIndex;
|
|
ser.PageSize = PageSize;
|
|
ser.Sort = Sort;
|
|
ser.KeyName = "Id";
|
|
string tsql = ser.GetText();
|
|
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
|
|
List<DT_TrackCodeCompare> 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<DT_TrackCodeCompare>();
|
|
return ListModel;
|
|
}
|
|
#endregion
|
|
|
|
#region 分页查询物流比较
|
|
public DataTable GetTrackCodeCompareTable(int MasterId, int TJ, string TrackCode, Decimal? OffNum)
|
|
{
|
|
string tsql = "";
|
|
if (TJ == 2)
|
|
{
|
|
tsql = @"
|
|
select a.Id,a.TrackCode,a.Weight,PostFee=a.Fee,MyWeight=cast(b.Weight as Decimal),MyPostFee=b.PostFee,b.OrderCode,WeightOff=isnull(a.weight,0)-isnull(b.Weight,0),FeeOff=isnull(a.Fee,0)-isnull(b.PostFee,0) from
|
|
WL_TrackCompareDetail a
|
|
left join DT_TrackCodeScan b on a.TrackCode=b.TrackCode
|
|
where b.Id is null and a.MasterId=@MasterId and (@TrackCode='' or a.TrackCode=@TrackCode) order by a.Id
|
|
";
|
|
}
|
|
else if (TJ == 1)
|
|
{
|
|
tsql = @"
|
|
select a.Id,a.TrackCode,a.Weight,PostFee=a.Fee,MyWeight=cast(b.Weight as Decimal),MyPostFee=b.PostFee,b.OrderCode,WeightOff=isnull(a.weight,0)-isnull(b.Weight,0),FeeOff=isnull(a.Fee,0)-isnull(b.PostFee,0) from
|
|
WL_TrackCompareDetail a
|
|
left join DT_TrackCodeScan b on a.TrackCode=b.TrackCode
|
|
where a.MasterId=@MasterId and (@TrackCode='' or a.TrackCode=@TrackCode) order by a.Id
|
|
";
|
|
|
|
}
|
|
else if (TJ == 3)
|
|
{
|
|
tsql = @"
|
|
select a.Id,a.TrackCode,a.Weight,PostFee=a.Fee,MyWeight=cast(b.Weight as Decimal),MyPostFee=b.PostFee,b.OrderCode,WeightOff=isnull(a.weight,0)-isnull(b.Weight,0),FeeOff=isnull(a.Fee,0)-isnull(b.PostFee,0) from
|
|
WL_TrackCompareDetail a
|
|
inner join DT_TrackCodeScan b on a.TrackCode=b.TrackCode
|
|
where a.MasterId=@MasterId and (@TrackCode='' or a.TrackCode=@TrackCode) and (isnull(a.Weight,0)-isnull(b.Weight,0)>@OffNum or isnull(b.Weight,0)-isnull(a.Weight,0)>@OffNum) order by a.Id
|
|
";
|
|
|
|
}
|
|
else if (TJ == 4)
|
|
{
|
|
tsql = @"
|
|
select * from
|
|
WL_TrackCompareDetail a
|
|
inner join DT_TrackCodeScan b on a.TrackCode=b.TrackCode
|
|
where a.MasterId=@MasterId and (@TrackCode='' or a.TrackCode=@TrackCode) and (isnull(a.Fee,0)-b.PostFee>@OffNum or isnull(b.PostFee,0)-isnull(a.Fee,0)>@OffNum) order by a.Id
|
|
";
|
|
|
|
}
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@MasterId", DbType.Int32, MasterId);
|
|
db.AddInParameter(cmd, "@TrackCode", DbType.String, TrackCode);
|
|
db.AddInParameter(cmd, "@OffNum", DbType.Decimal, OffNum);
|
|
DataTable dt = db.ExecuteDataTable(cmd);
|
|
|
|
return dt;
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 查询物流比较
|
|
public List<DT_TrackCodeCompare> GetTrackCodeCompareList(int TJ,decimal? Rate,string TrackCode)
|
|
{
|
|
List<DT_TrackCodeCompare> List = null;
|
|
string tsql = @"
|
|
select * from JC_ExpressType
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
List = ds.Tables[0].ToList<DT_TrackCodeCompare>();
|
|
|
|
return List;
|
|
}
|
|
#endregion
|
|
}
|
|
}
|
|
|