/// <summary>
/// 根据订单编号获取退款单状态
/// </summary>
/// <param name="SoSysNo"></param>
/// <returns></returns>
public virtual int?GetRequestStatusBySoSysNo(int SoSysNo)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Customer_Get_RequestStatusBySoSysNo");
cmd.SetParameterValue("@SOSysNo", SoSysNo);
if (cmd.ExecuteScalar() != null)
{
return((int)cmd.ExecuteScalar());
}
else
{
return(null);
}
}
/// <summary>
/// 根据PaySysNo查询单据状态
/// </summary>
/// <param name="sysNo">PaySysNo</param>
/// <returns></returns>
public string QueryInvoiceStatusByPaySysNo(int sysNo)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Invoice_GetInvoiceStatusByPaySysNo");
cmd.SetParameterValue("@PaySysNo", sysNo);
return(cmd.ExecuteScalar <string>());
}
/// <summary>
/// 自印刷发票系统查询
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List <CodeNamePair> InvoiceSelfStockQuery()
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("InvoiceReport.GetSelfPrintStocks");
object stocks = dataCommand.ExecuteScalar();
if (stocks != null)
{
List <CodeNamePair> result = new List <CodeNamePair>();
CustomDataCommand Command = DataCommandManager.CreateCustomDataCommandFromConfig("InvoiceReport.GetSelfPrintStocksName");
Dictionary <string, string> dir = new Dictionary <string, string>();
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(Command.CommandText, Command, null, "s.SysNo ASC"))
{
List <string> stockSysNo = new List <string>();
foreach (string item in stocks.ToString().Split(','))
{
stockSysNo.Add(item.Trim());
}
sqlBuilder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND, "s.SysNo", DbType.String, stockSysNo);
Command.CommandText = sqlBuilder.BuildQuerySql();
result = Command.ExecuteEntityList <CodeNamePair>();
}
return(result);
}
else
{
return(null);
}
}
/// <summary>
/// 根据条件取得责任人信息
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public ResponsibleUserInfo GetExistedResponsibleUser(ResponsibleUserInfo entity)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("ExistedResponsibleUser");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, null, "sr.SysNo desc"))
{
//特殊模式责任人
var isSpecialMode = (!entity.PayTypeSysNo.HasValue && !entity.ShipTypeSysNo.HasValue && !entity.CustomerSysNo.HasValue);
if (!isSpecialMode)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sr.IsPayWhenRecv", DbType.Int32, "@IncomeStyle", QueryConditionOperatorType.Equal, entity.IncomeStyle);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sr.PayTypeSysNo", DbType.Int32, "@PayTypeSysNo", QueryConditionOperatorType.Equal, entity.PayTypeSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sr.ShipTypeSysNo", DbType.Int32, "@ShipTypeSysNo", QueryConditionOperatorType.Equal, entity.ShipTypeSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sr.CustomerSysNo", DbType.Int32, "@CustomerSysNo", QueryConditionOperatorType.Equal, entity.CustomerSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sr.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, entity.CompanyCode);
}
else
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sr.IsPayWhenRecv", DbType.Int32, "@IsPayWhenRecv", QueryConditionOperatorType.Equal, entity.IncomeStyle);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sr.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, entity.CompanyCode);
builder.ConditionConstructor.AddNullCheckCondition(QueryConditionRelationType.AND, "sr.PayTypeSysNo", QueryConditionOperatorType.IsNull);
builder.ConditionConstructor.AddNullCheckCondition(QueryConditionRelationType.AND, "sr.ShipTypeSysNo", QueryConditionOperatorType.IsNull);
builder.ConditionConstructor.AddNullCheckCondition(QueryConditionRelationType.AND, "sr.CustomerSysNo", QueryConditionOperatorType.IsNull);
}
dataCommand.CommandText = builder.BuildQuerySql();
var result = dataCommand.ExecuteScalar();
if (!(result is DBNull || result == null))
{
return(LoadResponsibleUserBySysNo(Convert.ToInt32(result)));
}
return(null);
}
}
public void CreateEIMSInvoiceInput(List <EIMSInvoiceInfoEntity> entities)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("EIMS_Create_InvoiceInput");
string sql = BuildSql(entities, cmd);
cmd.CommandText = sql;
cmd.ExecuteScalar();
//写入日志表
List <EIMSInvoiceInfoEntity> list = entities;
List <string> invoiceNumberList = new List <string>();
list.ForEach(entity =>
{
entity.EIMSInvoiceInputExtendList.ForEach(ex =>
{
if (!invoiceNumberList.Contains(ex.InvoiceNumber))
{
invoiceNumberList.Add(ex.InvoiceNumber);
int invoiceNumber = 0;
int.TryParse(ex.InvoiceNumber, out invoiceNumber);
this.InsertEIMSEventLog(invoiceNumber, 196, entity.CurrentUser, string.Format("给单据{0}录入发票信息", invoiceNumber));
}
});
});
}
//验证同级分类名称是否重复
public bool CheckNameDuplicate(string name, int excludeSysNo, ECCategoryLevel level, string companyCode, string channelID, string parentSysnoList)
{
//DataCommand cmd = DataCommandManager.GetDataCommand("ECCategory_CheckNameDuplicate");
//cmd.SetParameterValue("@ExcludeSysNo", excludeSysNo);
//cmd.SetParameterValue("@CategoryName", name);
//cmd.SetParameterValue("@Level", level);
//cmd.SetParameterValue("@CompanyCode", companyCode);
////TODO:添加渠道参数
//cmd.SetParameterValue("@parentSysnoList", parentSysnoList);
//return cmd.ExecuteScalar<int>() > 0;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("ECCategory_CheckNameDuplicate");
string expendStr = string.Format("AND cr.ParentSysno in ({0})", parentSysnoList);
if (level != ECCategoryLevel.Category1)
{
cmd.CommandText = cmd.CommandText.Replace("#parentSysnoList", expendStr);
}
else
{
cmd.CommandText = cmd.CommandText.Replace("#parentSysnoList", "");
}
cmd.SetParameterValue("@ExcludeSysNo", excludeSysNo);
cmd.SetParameterValue("@CategoryName", name);
cmd.SetParameterValue("@Level", level);
cmd.SetParameterValue("@CompanyCode", companyCode);
//TODO:添加渠道参数
return(cmd.ExecuteScalar <int>() > 0);
}
/// <summary>
/// 获取商家新订单数量
/// </summary>
/// <param name="queryFilter"></param>
/// <returns></returns>
public static int GetNewOrderCount(DashboardQueryFilter queryFilter)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("Dashboard_GetNewOrderCount");
command.SetParameterValue(queryFilter);
return(command.ExecuteScalar <int>());
}
/// <summary>
/// Check以旧换新信息是否有效
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public bool IsOldChangeNewSO(OldChangeNewQueryFilter filter)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Invoice_Get_OldChangeNewSO");
cmd.AddInputParameter("@SOSysNo", System.Data.DbType.Int32, filter.OrderNo);
cmd.AddInputParameter("@CompanyCode", System.Data.DbType.Int32, filter.CompanyCode);
return(cmd.ExecuteScalar <int>() > 0);
}
public static int DeleteNavigation(int sysNo, int sellerSysNo)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("DeleteNavigation");
command.SetParameterValue("@SysNo", sysNo);
command.SetParameterValue("@SellerSysNo", sellerSysNo);
return(command.ExecuteScalar <int>());
}
public static int DeleteStorePageFromPublish(int sysno, int SellerSysNo)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("DeleteStorePageFromPublish");
command.SetParameterValue("@SysNo", sysno);
command.SetParameterValue("@SellerSysNo", SellerSysNo);
return(command.ExecuteScalar <int>());
}
public int GetPriceprotectPoint(int soSysNo, List <int> productSysNoList)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetPriceprotectPoint");
cmd.CommandText = cmd.CommandText.Replace("#ProductSysNoList", productSysNoList.Join(",").TrimEnd(','));
cmd.SetParameterValue("@SOSysNo", soSysNo);
return(cmd.ExecuteScalar <int>());
}
/// <summary>
/// 取得已确认的多付金额
/// </summary>
/// <param name="confirmedSOSysNo">订单系统编号，多个编号之间用逗号（,）隔开</param>
/// <returns></returns>
public decimal GetRefundAmtByConfirmedSOSysNoList(string confirmedSOSysNo)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetRefundAmtByConfirmedSOSysNo");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, null, "OrderSysNo desc"))
{
sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "OrderSysNo",
QueryConditionOperatorType.In, confirmedSOSysNo);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
object obj = dataCommand.ExecuteScalar();
if (obj == DBNull.Value)
{
return(0);
}
return(Convert.ToDecimal(dataCommand.ExecuteScalar()));
}
}
public int CreateSequence(string tableName)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("CreateSequence");
cmd.CommandText = cmd.CommandText.Replace("#tableName#", tableName);
object result = cmd.ExecuteScalar();
if (result != null)
{
return(Convert.ToInt32(result.ToString()));
}
return(-1);
}
public TrackingNumberInfo CreateTrackingNumber(TrackingNumberInfo entity)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("InsertTrackingNumber");
dataCommand.SetParameterValue(entity);
var suffix = GetStockSuffix(entity.StockSysNo.Value);
dataCommand.CommandText = dataCommand.CommandText
.Replace("Invoice_TrackingNumber", "Invoice_TrackingNumber_" + suffix);
entity.SysNo = Convert.ToInt32(dataCommand.ExecuteScalar());
return(entity);
}
/// <summary>
/// 是否已存在相同的Case
/// </summary>
/// <param name="info">投诉内容实体</param>
/// <param name="oldComplainID">已存在的相关投诉</param>
/// <returns>如果存在返回真，否则返回假</returns>
public bool IsSameCaseExist(SOComplaintCotentInfo info, ref object oldComplainID)
{
// 距当前时间一个工作日的时间点
DateTime tmpStarttime = CommonUtility.AddWorkMinute(DateTime.Now, -1 * 60 * 9);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("IsSameComplainCaseExist");
cmd.AddInputParameter("@SOSysNo", DbType.Int32, info.SOSysNo);
cmd.AddInputParameter("@CustomerSysno", DbType.Int32, info.CustomerSysNo);
cmd.AddInputParameter("@Subject", DbType.String, info.Subject.Trim());
cmd.AddInputParameter("@StartTime", DbType.DateTime, tmpStarttime);
cmd.AddInputParameter("@CompanyCode", DbType.AnsiStringFixedLength, info.CompanyCode);
oldComplainID = cmd.ExecuteScalar();
return(oldComplainID != null);
}
/// <summary>
/// 获取付款结算公司
/// </summary>
/// <param name="poNo"></param>
/// <param name="orderType"></param>
/// <param name="batchNumber"></param>
/// <returns></returns>
public virtual int GetPaySettleCompany(int vendorSysNo)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("GetPaySettleCompany");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
command.CommandText, command, null, "Sysno"
))
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"VendorSysNo",
DbType.Int32,
"@VendorSysNo",
QueryConditionOperatorType.Equal, vendorSysNo);
command.CommandText = sqlBuilder.BuildQuerySql();
object obj = command.ExecuteScalar();
if (obj == DBNull.Value)
{
return(0);
}
return(Convert.ToInt32(command.ExecuteScalar()));
}
}
public bool ExistsDifferentPMSysNo(List <int> pms, List <int> productSysNo, string companyCode)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("ExistsDifferentPMSysNoNew");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(command.CommandText, command, null, "SysNo desc"))
{
builder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "product.SysNo", DbType.Int32, productSysNo);
builder.ConditionConstructor.AddNotInCondition(QueryConditionRelationType.AND, "product.PMUserSysNo", DbType.Int32, pms);
command.CommandText = builder.BuildQuerySql();
command.AddInputParameter("@CompanyCode", DbType.AnsiStringFixedLength);
command.SetParameterValue("@CompanyCode", companyCode);
return(command.ExecuteScalar <int>() > 0);
}
}
public virtual string GetUserFullName(string userID, bool isSysNo)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetUserFullName");
using (var sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, new PagingInfoEntity(), " ORDER BY UserSysNo"))
{
if (isSysNo)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "UserSysNo", DbType.Int32, "@UserSysNo", QueryConditionOperatorType.Equal, int.Parse(userID));
}
else
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "LoginName", DbType.Int32, "@LoginName", QueryConditionOperatorType.Equal, userID);
}
cmd.CommandText = sb.BuildQuerySql();
string re = cmd.ExecuteScalar <string>();
return(re);
}
}
/// <summary>
/// 获取FinancePaySysNo
/// </summary>
/// <param name="poNo"></param>
/// <param name="orderType"></param>
/// <param name="batchNumber"></param>
/// <returns></returns>
public virtual int GetFinancePaySysNo(int poNo, PayableOrderType orderType, int?batchNumber)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("GetFinancePaySysNo");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
command.CommandText, command, null, "Sysno"
))
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"OrderSysNo",
DbType.Int32,
"@PoNo",
QueryConditionOperatorType.Equal, poNo);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"OrderType",
DbType.Int32,
"@OrderType",
QueryConditionOperatorType.Equal,
orderType);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"BatchNumber",
DbType.Int32,
"@BatchNumber",
QueryConditionOperatorType.Equal,
batchNumber);
command.CommandText = sqlBuilder.BuildQuerySql();
}
return(Convert.ToInt32(command.ExecuteScalar()));
}
private void AddSelfPrintParameter(InvoiceSelfPrintQueryFilter filter, DynamicQuerySqlBuilder sqlBuilder)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "sm.Status"
, DbType.String
, "@Status"
, QueryConditionOperatorType.Equal
, "4");
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "sm.IsVAT"
, DbType.Int32
, "@IsVAT"
, QueryConditionOperatorType.Equal
, 0);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "sm.CompanyCode"
, DbType.String
, "@CompanyCode"
, QueryConditionOperatorType.Equal
, filter.CompanyCode);
var warehouseNumber = filter.StockSysNo;
if (warehouseNumber.HasValue & filter.StockSysNo != 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "subso.WareHouseNumber"
, DbType.String
, "@WarehouseNumber"
, QueryConditionOperatorType.Equal
, filter.StockSysNo);
}
else
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("InvoiceReport.GetSelfPrintStocks");
string stocks = dataCommand.ExecuteScalar().ToString();
if (!String.IsNullOrEmpty(stocks))
{
List <string> stockSysNo = new List <string>();
foreach (string item in stocks.Split(','))
{
stockSysNo.Add(item.Trim());
}
sqlBuilder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND, "subso.WareHouseNumber", DbType.String, stockSysNo);
}
}
var outdateFrom = filter.OutDateFrom;
if (outdateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "sm.OutTime"
, DbType.DateTime
, "@OutDateFrom"
, QueryConditionOperatorType.MoreThanOrEqual
, filter.OutDateFrom);
}
var outdateTo = filter.OutDateTo;
if (outdateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "sm.OutTime"
, DbType.DateTime
, "@OutDateTo"
, QueryConditionOperatorType.LessThanOrEqual
, filter.OutDateTo);
}
var invoicedateFrom = filter.InvoiceDateFrom;
if (invoicedateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "im.PrintDate"
, DbType.DateTime
, "@InvoiceDateFrom"
, QueryConditionOperatorType.MoreThanOrEqual
, filter.InvoiceDateFrom);
}
var invoicedateTo = filter.InvoiceDateTo;
if (invoicedateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "im.PrintDate"
, DbType.DateTime
, "@InvoiceDateTo"
, QueryConditionOperatorType.LessThanOrEqual
, invoicedateTo.Value.AddDays(1).AddMinutes(-1));
}
var orderIDString = filter.OrderID;
if (!string.IsNullOrEmpty(orderIDString))
{
List <string> orderIDList = new List <string>();
foreach (string item in orderIDString.Split('.'))
{
orderIDList.Add(item.Trim());
}
sqlBuilder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND, "sm.SysNo", DbType.String, orderIDList);
}
var invoiceNumber = filter.InvoiceNumber;
if (!string.IsNullOrEmpty(invoiceNumber))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "im.InvoiceNumber"
, DbType.String
, "@InvoiceNumber"
, QueryConditionOperatorType.Like
, filter.InvoiceNumber);
}
}
public decimal QueryCommissionTotalAmt(QueryFilter.PO.CommissionQueryFilter queryFilter)
{
DataTable dt = new DataTable();
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("SearchCommissionTotalAmt");
string whereSql = "WHERE 1=1";
if (queryFilter.SysNo.HasValue)
{
dataCommand.AddInputParameter("@SysNo", DbType.Int32, queryFilter.SysNo.Value);
whereSql += " AND commMaster.SysNo = @SysNo";
}
if (queryFilter.VendorSysNo.HasValue)
{
dataCommand.AddInputParameter("@VendorSysNo", DbType.Int32, queryFilter.VendorSysNo.Value);
whereSql += " AND commMaster.MerchantSysNo = @VendorSysNo";
}
//InDate
if (queryFilter.InDateBegin.HasValue)
{
whereSql += " AND commMaster.InDate >= @InDateBegin";
dataCommand.AddInputParameter("@InDateBegin", DbType.DateTime, queryFilter.InDateBegin.Value);
}
if (queryFilter.InDateEnd.HasValue)
{
whereSql += " AND commMaster.InDate <= @InDateEnd";
dataCommand.AddInputParameter("@InDateEnd", DbType.DateTime, queryFilter.InDateEnd.Value.AddDays(1));
}
//OutListDate出单时间
if (queryFilter.OutListDateBegin.HasValue)
{
whereSql += " AND commMaster.EndDate >= @OutListDateBegin";
dataCommand.AddInputParameter("@OutListDateBegin", DbType.DateTime, queryFilter.OutListDateBegin.Value);
}
if (queryFilter.OutListDateEnd.HasValue)
{
whereSql += " AND commMaster.EndDate <= @OutListDateEnd";
dataCommand.AddInputParameter("@OutListDateEnd", DbType.DateTime, queryFilter.OutListDateEnd.Value.AddDays(1));
}
if (!string.IsNullOrEmpty(queryFilter.CompanyCode))
{
whereSql += " AND commMaster.CompanyCode = @CompanyCode ";
dataCommand.AddInputParameter("@CompanyCode", DbType.String, queryFilter.CompanyCode);
}
if (queryFilter.PageInfo != null)
{
dataCommand.AddOutParameter("@TotalCount", DbType.Int32, 4);
dataCommand.AddOutParameter("@TotalAmt", DbType.String, 10);
}
dataCommand.CommandText = dataCommand.CommandText.Replace("#StrWhere#", whereSql);
EnumColumnList list = new EnumColumnList();
list.Add("Status", typeof(VendorCommissionMasterStatus));
dataCommand.ExecuteScalar();
decimal totalAmt = Convert.ToDecimal(dataCommand.GetParameterValue("@TotalAmt"));
return(totalAmt);
}
