public override void OnPageLoad(object sender, EventArgs e)
{
base.OnPageLoad(sender, e);
m_QueryFacde = new EIMSOrderMgmtFacade(this);
viewList = new List <EIMSInvoiceEntryVM>();
spConditions.DataContext = m_QueryFilter = new EIMSInvoiceEntryQueryFilter();
IniPageData();
}
public List <EIMSInvoiceEntryInfo> InvoiceInfoListQuery(EIMSInvoiceEntryQueryFilter filter)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("EIMS_Query_InvoiceInfoList");
using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, HelpDA.ToPagingInfo(filter.PagingInfo), "ei.CreateDate DESC"))
{
AddInvoiceInfoListParameters(filter, cmd, sb);
return(cmd.ExecuteEntityList <EIMSInvoiceEntryInfo>());
}
}
public DataTable InvoiceInfoListQuery(EIMSInvoiceEntryQueryFilter filter, out int totalCount)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("EIMS_Query_InvoiceInfoList");
MapSortField(filter);
using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, HelpDA.ToPagingInfo(filter.PagingInfo), "ei.CreateDate DESC"))
{
AddInvoiceInfoListParameters(filter, cmd, sb);
DataTable dt = cmd.ExecuteDataTable();
EnumColumnList enumColList = new EnumColumnList();
enumColList.Add("Status", typeof(InvoiceStatus));
enumColList.Add("EIMSType", typeof(EIMSType));
enumColList.Add("ReceiveType", typeof(ReceiveType));
cmd.ConvertEnumColumn(dt, enumColList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
public EIMSInvoiceInfo QueryInvoiceList(string invoiceNumber)
{
EIMSInvoiceInfo entity = new EIMSInvoiceInfo();
entity.EIMSList = EIMSListQuery(invoiceNumber);
string invoiceNumberStr = string.Empty;
for (int i = 0; i < entity.EIMSList.Count; i++)
{
var extend = entity.EIMSList[i].EIMSInvoiceInputExtendList;
if (extend == null || extend.Count == 0)
{
continue;
}
for (int j = 0; j < extend.Count; j++)
{
if (!string.IsNullOrEmpty(invoiceNumberStr))
{
invoiceNumberStr += ".";
}
invoiceNumberStr += extend[j].InvoiceNumber;
}
}
EIMSInvoiceEntryQueryFilter filter = new EIMSInvoiceEntryQueryFilter()
{
InvoiceNumber = invoiceNumberStr,
PagingInfo = new PagingInfo {
PageIndex = 0, PageSize = int.MaxValue
}
};
entity.InvoiceInfoList = InvoiceInfoListQuery(filter);
entity.OldEIMSList = entity.EIMSList;
return(entity);
}
/// <summary>
/// 检验单据是否已录入
/// </summary>
/// <param name="invoiceList"></param>
private void CheckEIMSInvoiceInputNumber(List <EIMSInvoiceInfoEntityVM> invoiceList)
{
EIMSInvoiceEntryQueryFilter filter = new EIMSInvoiceEntryQueryFilter()
{
PagingInfo = new QueryFilter.Common.PagingInfo()
{
PageIndex = 0,
PageSize = int.MaxValue
}
};
int index = 0;
string invoiceNumber = string.Empty;
List <string> invoiceNumberList = new List <string>();
invoiceList.ForEach(entity =>
{
entity.EIMSInvoiceInputExtendList.ForEach(ex =>
{
if (!invoiceNumberList.Contains(ex.InvoiceNumber))
{
invoiceNumberList.Add(ex.InvoiceNumber);
}
});
});
invoiceNumberList.ForEach(delegate(string number)
{
if (index > 0)
{
invoiceNumber += ".";
}
invoiceNumber += number;
index++;
});
filter.InvoiceNumber = invoiceNumber;
invoiceNumberList.Clear();
invoiceNumber = string.Empty;
Facade.QueryInvoiceInfoList(filter, (obj, args) =>
{
if (args.FaultsHandle())
{
return;
}
List <EIMSInvoiceEntryVM> result = DynamicConverter <EIMSInvoiceEntryVM> .ConvertToVMList(args.Result.Rows);
result.ForEach(entity =>
{
if (entity.InvoiceInputStatus == "已录入")
{
invoiceNumberList.Add(entity.InvoiceNumber);
}
});
if (invoiceNumberList.Count > 0)
{
for (int i = 0; i < invoiceNumberList.Count; i++)
{
if (i > 0)
{
invoiceNumber += ";";
}
invoiceNumber += invoiceNumberList[i];
}
this.CurrentWindow.Alert((string.Format(ResEIMSInvoiceEntry.Msg_HadInputed, invoiceNumber)));
}
else
{
CreateInvoiceInput(InvoiceInfoEntityViewList, null);
}
});
}
/// <summary>
/// EIMS发票信息查询
/// </summary>
/// <param name="filter"></param>
/// <param name="callback"></param>
public void QueryInvoiceInfoList(EIMSInvoiceEntryQueryFilter filter, EventHandler <RestClientEventArgs <dynamic> > callback)
{
string relativeUrl = "ExternalSYSService/ExternalSYS/QueryInvoiceInfoList";
restClient.QueryDynamicData(relativeUrl, filter, callback);
}
public QueryResult InvoiceInfoListQuery(EIMSInvoiceEntryQueryFilter filter)
{
return(QueryList <EIMSInvoiceEntryQueryFilter>(filter, ObjectFactory <IEIMSEventMemoQueryFilterDA> .Instance.InvoiceInfoListQuery));
}
private void AddInvoiceInfoListParameters(EIMSInvoiceEntryQueryFilter filter, CustomDataCommand cmd, DynamicQuerySqlBuilder sb)
{
List <Object> receiveTypeList = new List <Object>();
receiveTypeList.Add(1);
receiveTypeList.Add(2);
List <Object> status = new List <Object>();
status.Add("O");
status.Add("F");
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ei.ReceiveType",
DbType.Int32,
"@MyReceiveType",
QueryConditionOperatorType.In,
receiveTypeList);
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ei.[STATUS]",
DbType.String,
"@MySTATUS",
QueryConditionOperatorType.In,
status);
if (!string.IsNullOrEmpty(filter.AssignedCode))
{
filter.AssignedCode = filter.AssignedCode.Replace(" ", string.Empty);
string[] spiltResults = filter.AssignedCode.Split('.');
string invoiceNumbers = string.Empty;
string assignedCodes = string.Empty;
int invoiceNumber = 0;
if (spiltResults.Length > 1)
{
for (int i = 0; i < spiltResults.Length; i++)
{
if (int.TryParse(spiltResults[i], out invoiceNumber))
{
if (invoiceNumbers.Length > 0)
{
invoiceNumbers += ",";
}
invoiceNumbers += spiltResults[i];
}
else
{
if (assignedCodes.Length > 0)
{
assignedCodes += ",";
}
assignedCodes += "'" + spiltResults[i] + "'";
}
}
if (invoiceNumbers.Length > 0 && assignedCodes.Length == 0)
{
sb.ConditionConstructor.AddSubQueryCondition(
QueryConditionRelationType.AND,
"ei.InvoiceNumber",
QueryConditionOperatorType.In,
invoiceNumbers);
}
if (assignedCodes.Length > 0 && invoiceNumbers.Length == 0)
{
sb.ConditionConstructor.AddSubQueryCondition(
QueryConditionRelationType.AND,
"ei.AssignedCode",
QueryConditionOperatorType.In,
assignedCodes);
}
if (invoiceNumbers.Length > 0 && assignedCodes.Length > 0)
{
sb.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sb.ConditionConstructor.AddSubQueryCondition(
QueryConditionRelationType.AND,
"ei.InvoiceNumber",
QueryConditionOperatorType.In,
invoiceNumbers);
sb.ConditionConstructor.AddSubQueryCondition(
QueryConditionRelationType.OR,
"ei.AssignedCode",
QueryConditionOperatorType.In,
assignedCodes);
sb.ConditionConstructor.EndGroupCondition();
}
}
else
{
if (int.TryParse(filter.AssignedCode, out invoiceNumber))
{
sb.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ei.AssignedCode",
DbType.String,
"@AssignedCode",
QueryConditionOperatorType.Equal,
filter.AssignedCode);
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.OR,
"ei.InvoiceNumber",
DbType.String,
"@InvoiceNumber",
QueryConditionOperatorType.Equal,
invoiceNumber);
sb.ConditionConstructor.EndGroupCondition();
}
else
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ei.AssignedCode",
DbType.String,
"@AssignedCode",
QueryConditionOperatorType.Equal,
filter.AssignedCode);
}
}
}
if (filter.EIMSType.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ei.EIMSType",
DbType.String,
"@EIMSType",
QueryConditionOperatorType.Equal,
filter.EIMSType);
}
if (!string.IsNullOrEmpty(filter.InvoiceInputStatus))
{
string subSql = "SELECT COUNT(1) " +
"FROM eims.dbo.EIMSInvoiceInput iei WITH(NOLOCK) " +
"INNER JOIN eims.dbo.EIMSInvoiceInput_Ex iex WITH(NOLOCK) " +
"ON iei.SysNo = iex.InvoiceInputSysNO AND iei.[status]=0 AND iex.[status]=0 " +
"WHERE iex.InvoiceNumber = ei.InvoiceNumber ";
if (filter.InvoiceInputStatus == "已录入")
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"(" + subSql + ")",
DbType.Int32,
"@InvoiceInputStatusSql",
QueryConditionOperatorType.MoreThan,
0);
}
else if (filter.InvoiceInputStatus == "未录入")
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"(" + subSql + ")",
DbType.Int32,
"@InvoiceInputStatusSql",
QueryConditionOperatorType.Equal,
0);
}
}
if (filter.ReceiveType.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ei.ReceiveType",
DbType.String,
"@ReceiveType",
QueryConditionOperatorType.Equal,
filter.ReceiveType);
}
if (!string.IsNullOrEmpty(filter.RuleAssignedCode))
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"er.AssignedCode",
DbType.String,
"@RuleAssignedCode",
QueryConditionOperatorType.Equal,
filter.RuleAssignedCode);
}
if (!string.IsNullOrEmpty(filter.Status))
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ei.[Status]",
DbType.String,
"@Status",
QueryConditionOperatorType.Equal,
filter.Status);
}
if (filter.VendorNumber.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ei.VendorNumber",
DbType.String,
"@VendorNumber",
QueryConditionOperatorType.Equal,
filter.VendorNumber);
}
if (!string.IsNullOrEmpty(filter.IsSAPImported))
{
if (filter.IsSAPImported == "Y")
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"fp.IsSAPImported",
DbType.String,
"@IsSAPImported",
QueryConditionOperatorType.Equal,
filter.IsSAPImported);
}
else if (filter.IsSAPImported == "N")
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"fp.IsSAPImported",
DbType.String,
"@IsSAPImported",
QueryConditionOperatorType.Equal,
filter.IsSAPImported);
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.OR,
"fp.IsSAPImported",
DbType.String,
"@InvoiceNumber",
QueryConditionOperatorType.IsNull,
null);
}
}
if (!string.IsNullOrEmpty(filter.InvoiceNumber))
{
string[] arr = filter.InvoiceNumber.Split('.');
List <Object> invoiceNumberList = new List <Object>();
string invoiceNumber = string.Empty;
for (int i = 0; i < arr.Length; i++)
{
invoiceNumberList.Add(arr[i]);
}
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ei.InvoiceNumber",
DbType.String,
"@InvoiceNumber",
QueryConditionOperatorType.In,
invoiceNumberList);
}
if (!string.IsNullOrEmpty(filter.InvoiceInputNo))
{
string subSql = "EXISTS(SELECT 1 " +
"FROM EIMS.dbo.EIMSInvoiceInput_Ex eiiex WITH(NOLOCK) " +
"INNER JOIN eims.dbo.EIMSInvoiceInput eii WITH(NOLOCK) " +
" ON eiiex.InvoiceInputSysNO = eii.SysNO "+
"WHERE eii.Status=0 " +
" AND eiiex.Status=0 " +
" AND eii.InvoiceInputNo="+ filter.InvoiceInputNo + " " +
" AND eiiex.InvoiceNumber=ei.InvoiceNumber)";
cmd.CommandText += subSql;
sb.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, subSql);
}
cmd.CommandText = sb.BuildQuerySql();
}
private static void MapSortField(EIMSInvoiceEntryQueryFilter filter)
{
if (filter.PagingInfo != null && !string.IsNullOrEmpty(filter.PagingInfo.SortBy))
{
var index = 0;
index = filter.PagingInfo.SortBy.Contains("asc") ? 4 : 5;
var sort = filter.PagingInfo.SortBy.Substring(0, filter.PagingInfo.SortBy.Length - index);
var sortField = filter.PagingInfo.SortBy;
switch (sort)
{
case "AssignedCode":
filter.PagingInfo.SortBy = sortField.Replace("AssignedCode", "ei.AssignedCode");
break;
case "InvoiceNumber":
filter.PagingInfo.SortBy = sortField.Replace("InvoiceNumber", "ei.InvoiceNumber");
break;
case "InvoiceName":
filter.PagingInfo.SortBy = sortField.Replace("InvoiceName", "ei.InvoiceName");
break;
case "RuleAssignedCode":
filter.PagingInfo.SortBy = sortField.Replace("RuleAssignedCode", "er.AssignedCode");
break;
case "VendorNumber":
filter.PagingInfo.SortBy = sortField.Replace("VendorNumber", "ei.VendorNumber");
break;
case "VendorName":
filter.PagingInfo.SortBy = sortField.Replace("VendorName", "vendor.VendorName");
break;
case "ReceiveType":
filter.PagingInfo.SortBy = sortField.Replace("ReceiveType", "ei.ReceiveType");
break;
case "EIMSType":
filter.PagingInfo.SortBy = sortField.Replace("EIMSType", "ei.EIMSType");
break;
case "InvoiceAmount":
filter.PagingInfo.SortBy = sortField.Replace("InvoiceAmount", "ei.InvoiceAmount");
break;
case "Status":
filter.PagingInfo.SortBy = sortField.Replace("Status", "ei.[Status]");
break;
case "CreateDate":
filter.PagingInfo.SortBy = sortField.Replace("CreateDate", "ei.CreateDate");
break;
case "ApproveDate":
filter.PagingInfo.SortBy = sortField.Replace("ApproveDate", "ei.ApproveDate");
break;
case "PM":
filter.PagingInfo.SortBy = sortField.Replace("PM", "PM.UserName");
break;
}
}
}
