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);
}
public DataTable FinanceQuery(FinanceQueryFilter filter, out int totalCount, out double totalPayAmt)
{
string sqlName = string.Empty;
string orderStr = "SysNo DESC";
if (filter.IsGroupByVendor == true)
{
sqlName = "Invoice_Query_FinanceGroupByVendor";
}
else
{
sqlName = "Invoice_Query_Finance";
if (!string.IsNullOrEmpty(filter.PagingInfo.SortBy))
{
orderStr = filter.PagingInfo.SortBy;
}
}
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig(sqlName);
using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, HelpDA.ToPagingInfo(filter.PagingInfo), orderStr))
{
cmd.CommandText = sb.BuildQuerySql();
if (filter.IsGroupByVendor == true)
{
AddFinanceParametersGroupByVendor(cmd, filter);
}
else
{
AddFinanceParameters(filter, cmd);
}
//合计已到应付总额
cmd.AddOutParameter("@TotalPayableAmt", DbType.Double, 12);
DataTable dt = cmd.ExecuteDataTable();
EnumColumnList enumColList = new EnumColumnList();
CodeNamePairColumnList codeNameColList = new CodeNamePairColumnList();
if (filter.IsGroupByVendor == false ||
filter.IsGroupByVendor == null)
{
enumColList.Add("AuditStatus", typeof(PayableAuditStatus));
//codeNameColList.Add("OrderType", "Invoice", "OrderType");
enumColList.Add("OrderType", typeof(PayableOrderType));
//codeNameColList.Add("OrderStatus", "Invoice", "OrderStatus");
codeNameColList.Add("InvoiceStatus", "Invoice", "InvoiceStatus");
codeNameColList.Add("IsConsign", "Invoice", "VendorType");
}
else
{
codeNameColList.Add("IsConsign", "Invoice", "VendorType");
}
cmd.ConvertColumn(dt, enumColList, codeNameColList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
var totalPayableAmtParam = cmd.GetParameterValue("@TotalPayableAmt");
if (totalPayableAmtParam != DBNull.Value)
{
totalPayAmt = Convert.ToDouble(totalPayableAmtParam);
}
else
{
totalPayAmt = 0.0;
}
return(dt);
}
}
public System.Data.DataTable QueryCommission(QueryFilter.PO.CommissionQueryFilter queryFilter, out int totalCount, out decimal totalAmt)
{
DataTable dt = new DataTable();
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("SearchCommission");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PageInfo.SortBy,
StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize,
MaximumRows = queryFilter.PageInfo.PageSize
};
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.AddInputParameter("@StartNumber", DbType.Int32, queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex);
dataCommand.AddInputParameter("@EndNumber", DbType.Int32, queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex + queryFilter.PageInfo.PageSize);
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));
dt = dataCommand.ExecuteDataTable(list);
totalAmt = Convert.ToDecimal(dataCommand.GetParameterValue("@TotalAmt"));
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
public DataSet QueryTrackingInfo(TrackingInfoQueryFilter filter, out int totalCount)
{
DataSet result = null;
PagingInfoEntity pagingInfo = CreatePagingInfo(filter.PagingInfo);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryTrackingInfo");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingInfo, "Temp.SysNo desc"))
{
if (!string.IsNullOrEmpty(filter.OrderSysNo))
{
var orderSysNoList = filter.OrderSysNo.Split('.').ToList()
.ConvertAll(x => Convert.ToInt32(x.Trim()));
builder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND, "Temp.OrderSysNo", DbType.Int32, orderSysNoList);
}
var statusList = new List <string>();
//业务跟进
if (filter.HasStatusFollow)
{
statusList.Add("A");
}
//提交报损
if (filter.HasStatusSubmit)
{
statusList.Add("S");
}
//核销完毕
if (filter.HasStatusConfirm)
{
statusList.Add("C");
}
//部分选择时再添加该条件
if (statusList.Count > 0 && statusList.Count < 3)
{
builder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND, "Temp.Status", DbType.AnsiStringFixedLength, statusList);
}
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.OrderType", DbType.Int32, "@OrderType", QueryConditionOperatorType.Equal, filter.OrderType);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.IncomeStyle", DbType.Int32, "@IncomeStyle", QueryConditionOperatorType.Equal, filter.IncomeStyle);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.PayTypeSysNo", DbType.Int32, "@PayTypeSysNo", QueryConditionOperatorType.Equal, filter.PayTypeSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.OutDate", DbType.DateTime, "@OutFromDate", QueryConditionOperatorType.MoreThanOrEqual, filter.OutFromDate);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.OutDate", DbType.DateTime, "@OutToDate", QueryConditionOperatorType.LessThanOrEqual, filter.OutToDate);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.ShipTypeSysNo", DbType.Int32, "@ShipTypeSysNo", QueryConditionOperatorType.Equal, filter.ShipTypeSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.LossType", DbType.Int32, "@LossType", QueryConditionOperatorType.Equal, filter.LossType);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.ResponsibleUserName", DbType.String, "@ResponsibleUserName", QueryConditionOperatorType.Equal, filter.ResponsibleUserName);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.InType", DbType.Int32, "@InType", QueryConditionOperatorType.Equal, filter.InType);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
cmd.AddOutParameter("@OrderAmt", DbType.Double, 12);
cmd.AddOutParameter("@PrepayAmt", DbType.Double, 12);
cmd.AddOutParameter("@GiftCardPayAmt", DbType.Double, 12);
cmd.AddOutParameter("@IncomeAmt", DbType.Double, 12);
cmd.AddOutParameter("@UnpayedAmt", DbType.Double, 12);
cmd.CommandText = builder.BuildQuerySql();
result = ExecuteDataCommandForTrackingInfo(cmd, out totalCount);
}
return(result);
}
private void AddEIMSInvoiceParameters(EIMSInvoiceQueryFilter filter, DynamicQuerySqlBuilder sb, CustomDataCommand cmd)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "vendor.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
if (filter.VendorSysNo.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"vendor.SysNo",
DbType.String,
"@SysNo",
QueryConditionOperatorType.Equal,
filter.VendorSysNo);
}
if (!string.IsNullOrEmpty(filter.VendorName))
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"vendor.VendorName",
DbType.String,
"@VendorName",
QueryConditionOperatorType.Like,
filter.VendorName);
}
if (!string.IsNullOrEmpty(filter.RuleNo))
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ruleMaster.AssignedCode",
DbType.String,
"@RuleNo",
QueryConditionOperatorType.Equal,
filter.RuleNo);
}
if (!string.IsNullOrEmpty(filter.InvoiceNo))
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"invoiceMaster.AssignedCode",
DbType.String,
"@InvoiceNo",
QueryConditionOperatorType.Equal,
filter.InvoiceNo);
}
if (!string.IsNullOrEmpty(filter.EIMSType))
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"invoiceMaster.EIMSType",
DbType.String,
"@EIMSType",
QueryConditionOperatorType.Equal,
filter.EIMSType);
}
if (!string.IsNullOrEmpty(filter.ReceivedType))
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"invoiceMaster.ReceiveType",
DbType.String,
"@ReceivedType",
QueryConditionOperatorType.Equal,
filter.ReceivedType);
}
if (filter.InvoiceApprovedStart.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"invoiceMaster.ApproveDate",
DbType.DateTime,
"@ApproveDateStart",
QueryConditionOperatorType.MoreThanOrEqual,
filter.InvoiceApprovedStart);
}
if (filter.InvocieApprovedEnd.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"invoiceMaster.ApproveDate",
DbType.DateTime,
"@ApproveDateEnd",
QueryConditionOperatorType.LessThanOrEqual,
filter.InvocieApprovedEnd);
}
if (!string.IsNullOrEmpty(filter.InvoiceStatus))
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"invoiceMaster.Status",
DbType.String,
"@InvoiceStatus",
QueryConditionOperatorType.Equal,
filter.InvoiceStatus);
}
cmd.AddOutParameter("@SumNoReceivedAmount", DbType.Double, 50);
object obj = cmd.GetParameterValue("@SumNoReceivedAmount");
if (obj == null ||
Convert.IsDBNull(obj))
{
obj = 0;
}
cmd.AddInputParameter("@PageSize", DbType.Int32, filter.PagingInfo.PageSize);
cmd.AddInputParameter("@PageIndex", DbType.Int32, filter.PagingInfo.PageIndex);
cmd.CommandText = sb.BuildQuerySql();
}
private void AddOldChangeNewParameters(OldChangeNewQueryFilter filter, CustomDataCommand cmd, DynamicQuerySqlBuilder sb)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"rb.CompanyCode",
DbType.AnsiStringFixedLength,
"@CompanyCode",
QueryConditionOperatorType.Equal,
filter.CompanyCode);
sb.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "1=1");
if (!string.IsNullOrEmpty(filter.OrderNoList))
{
filter.OrderNoList = filter.OrderNoList.Replace(" ", "");
filter.OrderNoList = filter.OrderNoList.Replace(".", ",");
sb.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, string.Format("rb.SoSysNo in ({0})", filter.OrderNoList));
}
else if (filter.OrderNo.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"rb.SoSysNo",
DbType.Int32,
"@Sosysno",
QueryConditionOperatorType.Equal,
filter.OrderNo);
}
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"rb.TradeInId",
DbType.String,
"@TradeInId",
QueryConditionOperatorType.Equal,
filter.ApplyID);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"rb.Licence",
DbType.String,
"@Licence",
QueryConditionOperatorType.Equal,
filter.CertificateNo);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"vm.CustomerSysNo",
DbType.Int32,
"@CustomerSysNo",
QueryConditionOperatorType.Equal,
filter.CustomerNo);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.CustomerID",
DbType.String,
"@CustomerID",
QueryConditionOperatorType.Equal,
filter.CustomerID);
if (!string.IsNullOrEmpty(filter.ProductType))
{
sb.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, string.Format(@"EXISTS(
SELECT i.SoSysNo FROM ipp3.dbo.V_SO_Item i
INNER JOIN IPP3.dbo.Product p
ON p.SysNo=i.ProductSysNo
WHERE p.C3SysNo IN({0}) AND i.SOSysNo=rb.SoSysNo)", filter.ProductType));
}
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"rb.InDate",
DbType.DateTime,
"@InDateFrom", QueryConditionOperatorType.MoreThanOrEqual,
filter.CreateDateFrom);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"rb.InDate",
DbType.DateTime,
"@InDateTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.CreateDateTo);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"rb.ConfirmDate",
DbType.DateTime,
"@ConfirmDateFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.CompleteDateFrom);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"rb.ConfirmDate",
DbType.DateTime,
"@ConfirmDateTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.CompleteDateTo);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"rb.Status",
DbType.Int32,
"@Status",
QueryConditionOperatorType.Equal,
filter.Status);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"rb.ReferenceID",
DbType.String,
"@ReferenceID",
QueryConditionOperatorType.Equal,
filter.RefundCertificate);
cmd.AddOutParameter("@TotalRebate", DbType.String, 50);
cmd.AddOutParameter("@TotalReviseRebate", DbType.String, 50);
cmd.AddOutParameter("@TotalPassReviseRebate", DbType.String, 50);
cmd.AddOutParameter("@TotalReturnReviseRebate", DbType.String, 50);
cmd.CommandText = sb.BuildQuerySql();
}
public int InsertProductShiftDetails(List <BizEntity.Invoice.ProductShiftDetailEntity> listProductShiftDetails, string groubText)
{
XmlDocument xmldoc = new XmlDocument();
XmlProductShift xmlEntity = new XmlProductShift();
if (listProductShiftDetails.Count > 0)
{
xmlEntity.InvoiceNode = new InvoiceNode
{
OrderSysNo = groubText,
OrderType = "SHIFT",
WarehouseNumber = listProductShiftDetails[0].WarehouseNumber.ToString(),
ComputerNo = 0,
Items = new Items
{
Item = new List <ItemForProductShift>()
}
};
}
var productShiftDetailEntitys = listProductShiftDetails.GroupBy(p => p.SysNo);
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("InsertProductShiftDetails");
// command.CommandText = @"
// EXEC WMS.dbo.UP_SendShiftInvoiceMessage
// @WarehouseNumber = @WarehouseNumber,
// @Body=@Body,
// @ReturnMsg= @ReturnMsg output
// ";
foreach (ProductShiftDetailEntity entity in listProductShiftDetails)
{
xmlEntity.InvoiceNode.Items.Item.Add(
new ItemForProductShift(entity, groubText)
);
}
var serialResult = "";
int?whereHoseNumber = listProductShiftDetails[0].WarehouseNumber;
if (whereHoseNumber == 50 || whereHoseNumber == 59)
{
whereHoseNumber = 51;
}
serialResult = SerializationUtility.XmlSerialize(xmlEntity, true);
//serialResult = serialResult.Replace("<?xml version=\"1.0\" encoding=\"utf-16\"?>\r\n", "");
//command.AddInputParameter("@WarehouseNumber", DbType.Int32, whereHoseNumber);
//command.AddInputParameter("@Body", DbType.String, serialResult);
command.SetParameterValue("@WarehouseNumber", whereHoseNumber);
command.SetParameterValue("@Body", serialResult);
command.AddOutParameter("@ReturnMsg", DbType.String, 50);
command.ExecuteNonQuery();
string result = command.GetParameterValue("@ReturnMsg").ToString();
if (!result.Equals("success", StringComparison.OrdinalIgnoreCase))
{
return(1);
//try
//{
// CommonService.SendMail(new MailBodyMsg
// {
// CreateDate = DateTime.Now,
// Subjuect = "导入金税失败",
// MailBody = serialResult,
// MailFrom = "OYSD Support",
// MailTo = " [email protected]; [email protected]; [email protected];[email protected];[email protected]",
// Status = 0
// });
//}
//catch
//{
// throw new BusinessException("发送SSB消息失败！");
//}
}
else
{
return(0);
}
}
public DataSet Query(BalanceAccountQueryFilter filter, out int totalCount)
{
DataSet result = null;
PagingInfoEntity pagingInfo = new PagingInfoEntity();
if (filter.PagingInfo != null)
{
MapSortField(filter);
pagingInfo.MaximumRows = filter.PagingInfo.PageSize;
pagingInfo.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize;
pagingInfo.SortField = filter.PagingInfo.SortBy;
}
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryBalanceAccount");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, pagingInfo, "CP.SysNo desc"))
{
StringBuilder t1 = new StringBuilder();
StringBuilder t2 = new StringBuilder();
StringBuilder t3 = new StringBuilder();
t1.Append("WHERE PL.Status = @Status AND PL.CompanyCode = @CompanyCode ");
t2.Append("WHERE PL.Status = @Status AND PL.CompanyCode = @CompanyCode ");
t3.Append("WHERE PL.Status = @Status AND PL.CompanyCode = @CompanyCode ");
if (filter.CustomerSysNo.HasValue)
{
t1.Append(" AND PL.CustomerSysNo = @CustomerSysNo ");
t2.Append(" AND PL.CustomerSysNo = @CustomerSysNo ");
t3.Append(" AND PL.CustomerSysNo = @CustomerSysNo ");
}
if (!string.IsNullOrEmpty(filter.CustomerID))
{
t1.Append(" AND C.CustomerID = @CustomerID ");
t2.Append(" AND C.CustomerID = @CustomerID ");
t3.Append(" AND C.CustomerID = @CustomerID ");
}
if (filter.CreateTimeFrom.HasValue)
{
t1.Append(" AND PL.CreateTime < @CreateDateFrom ");
t3.Append(" AND PL.CreateTime > @CreateDateFrom ");
}
if (filter.CreateTimeTo.HasValue)
{
t2.Append(" AND PL.CreateTime <= @CreateDateTo ");
t3.Append(" AND PL.CreateTime <= @CreateDateTo ");
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CP.CustomerSysNo",
DbType.Int32, "@CustomerSysNo", QueryConditionOperatorType.Equal, filter.CustomerSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.CustomerID",
DbType.String, "@CustomerID", QueryConditionOperatorType.Equal, filter.CustomerID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CP.CreateTime",
DbType.DateTime, "@CreateDateFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.CreateTimeFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CP.CreateTime",
DbType.DateTime, "@CreateDateTo", QueryConditionOperatorType.LessThanOrEqual, filter.CreateTimeTo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CP.CompanyCode",
DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CP.Status",
DbType.AnsiStringFixedLength, "@Status", QueryConditionOperatorType.Equal, 'A');
if (filter.DetailType.HasValue)
{
if (filter.DetailType.Value > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "cp.PrepayAmt", DbType.Decimal, "@PrepayAmt", QueryConditionOperatorType.MoreThanOrEqual, 0);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "cp.PrepayAmt", DbType.Decimal, "@PrepayAmt", QueryConditionOperatorType.LessThan, 0);
}
}
dataCommand.AddOutParameter("@StartBalance", DbType.Double, 50);
dataCommand.AddOutParameter("@EndBalance", DbType.Double, 50);
dataCommand.AddOutParameter("@PayedIn", DbType.Double, 50);
dataCommand.AddOutParameter("@PayedOut", DbType.Double, 50);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
dataCommand.CommandText = dataCommand.CommandText.Replace("#ITWHERE01#", t1.ToString());
dataCommand.CommandText = dataCommand.CommandText.Replace("#ITWHERE02#", t2.ToString());
dataCommand.CommandText = dataCommand.CommandText.Replace("#ITWHERE03#", t3.ToString());
result = ExecuteDataTable(dataCommand, filter, out totalCount);
}
return(result);
}
