public async Task <IActionResult> GetItemHistories(int?key) { if (key > 0) { var sqlCommand = new SqlCommandViewModel() { SelectCommand = $@"[R].[Description] AS [Fail] ,[M].[Description] AS [Fix] ,[R].[RequireDate] AS [Date] ,[R].[RequireDateTime] ,[M].[Remark] ,[M].[ItemMaintenanceId]", FromCommand = $@"[dbo].[RequireMaintenance] AS [R] LEFT OUTER JOIN [dbo].[ItemMaintenance] AS [M] ON [M].[RequireMaintenanceId] = [R].[RequireMaintenanceId]", WhereCommand = $@"[R].[ItemId] = {key} AND [R].[RequireStatus] != 4", OrderCommand = "[R].[RequireDate] DESC" }; var hasData = await this.dapper.GetListEntites <ItemHistorieViewModel>(sqlCommand); if (hasData.Any()) { return(new JsonResult(hasData, this.DefaultJsonSettings)); } } return(NoContent()); }
public async Task <IActionResult> PurchaseReqLineExtendByPurchaseRequest(string key) { var message = "Data not been found."; try { if (!string.IsNullOrEmpty(key)) { #region Where string sWhere = $" LOWER(PRD.PSHNUM_0) = '{key.Trim().ToLower()}'"; #endregion Where #region Sort string sSort = $" PRD.PSHNUM_0,PRD.PSDLIN_0 ASC ";//QueryData = QueryData.OrderByDescending(x => x.PAYM.Prqdat0); #endregion Sort #region Query var sqlCommnad = new SqlCommandViewModel() { SelectCommand = $@" PRD.ROWID AS [PrSageLineId], PRD.PSHNUM_0 AS [PrNumber], PRD.PSDLIN_0 AS [PrLine], PRD.ITMREF_0 AS [ItemCode], ISNULL(TXT.TEXTE_0,PRD.ITMDES_0) AS [ItemName], PRD.QTYPUU_0 AS [Quantity] ", FromCommand = $@" [x3v11].[VIPCO].PREQUISD PRD LEFT OUTER JOIN [x3v11].[VIPCO].TEXCLOB TXT ON PRD.LINTEX_0 = TXT.CODE_0 ", WhereCommand = sWhere, OrderCommand = sSort }; #endregion var result = await this.repositoryPrLinePure.GetEntities(sqlCommnad); foreach (var item in result) { if (item.ItemName.StartsWith("{\\rtf1")) { item.ItemName = Rtf.ToHtml(item.ItemName); } } return(new JsonResult(result, this.DefaultJsonSettings)); } } catch (Exception ex) { message = $"Has error {ex.ToString()}"; } return(BadRequest(new { message })); }
public async Task <List <Entity> > GetEntities(SqlCommandViewModel sqlCommand, int timeout = 60) { string sSqlCommand = $@"SELECT {sqlCommand.SelectCommand} FROM {sqlCommand.FromCommand} "; if (!string.IsNullOrEmpty(sqlCommand.WhereCommand)) { sSqlCommand += $@"WHERE {sqlCommand.WhereCommand} "; } if (!string.IsNullOrEmpty(sqlCommand.GroupCommand)) { sSqlCommand += $@"GROUP BY {sqlCommand.GroupCommand} "; } if (!string.IsNullOrEmpty(sqlCommand.OrderCommand)) { sSqlCommand += $@"ORDER BY {sqlCommand.OrderCommand} "; } using (IDbConnection conn = Connection) { conn.Open(); var result = await conn.QueryAsync <Entity>(sSqlCommand, commandTimeout : timeout); conn.Close(); return(result.ToList()); } }
public async Task <bool> ExecuteReturnNoResult(SqlCommandViewModel SqlCommand, int timeout = 60) { var sSqlCommands = ""; // Sql insert command if (!string.IsNullOrEmpty(SqlCommand.InsertCommand)) { sSqlCommands += $@"INSERT INTO {SqlCommand.InsertCommand} "; if (!string.IsNullOrEmpty(SqlCommand.SelectCommand)) { sSqlCommands += $@"SELECT {SqlCommand.SelectCommand} "; } if (!string.IsNullOrEmpty(SqlCommand.FromCommand)) { sSqlCommands += $@"FROM {SqlCommand.FromCommand} "; } if (!string.IsNullOrEmpty(SqlCommand.WhereCommand)) { sSqlCommands += $@"WHERE {SqlCommand.WhereCommand} "; } if (!string.IsNullOrEmpty(SqlCommand.ValueCommand)) { sSqlCommands += $@"VALUES {SqlCommand.ValueCommand}"; } } // Sql update command else if (!string.IsNullOrEmpty(SqlCommand.UpdateCommand)) { sSqlCommands += $@"UPDATE {SqlCommand.UpdateCommand} "; if (!string.IsNullOrEmpty(SqlCommand.SelectCommand)) { sSqlCommands += $@"SET {SqlCommand.SelectCommand} "; } if (!string.IsNullOrEmpty(SqlCommand.FromCommand)) { sSqlCommands += $@"FROM {SqlCommand.FromCommand} "; } if (!string.IsNullOrEmpty(SqlCommand.WhereCommand)) { sSqlCommands += $@"WHERE {SqlCommand.WhereCommand} "; } } // Sql select command else { } using (IDbConnection conn = Connection) { var result = await conn.ExecuteAsync(sql : sSqlCommands, commandTimeout : timeout); return(result > 0); } }
public async Task <IActionResult> ItemHistories([FromBody] ItemHistoryOptionViewModel Option) { var message = "Data not been found."; try { if (Option != null) { /* * var HasData = await this.repositoryRequireMaintenance.GetToListAsync(x => new ItemHistorieViewModel * { * Fail = x.Description, * Fix = x.ItemMaintenance == null ? "-" : x.ItemMaintenance.Description, * Date = x.RequireDate, * Remark = x.ItemMaintenance == null ? "-" : x.ItemMaintenance.Remark, * ItemMaintenanceId = x.ItemMaintenance == null ? 0 : x.ItemMaintenance.ItemMaintenanceId * }, * x => x.ItemId == Option.ItemId && * x.RequireStatus != RequireStatus.Cancel, * x => x.OrderByDescending(z => z.RequireDate), * x => x.Include(z => z.ItemMaintenance)); */ var sqlCommand = new SqlCommandViewModel() { SelectCommand = $@"[R].[Description] AS [Fail] ,[M].[Description] AS [Fix] ,[R].[RequireDate] AS [Date] ,[R].[RequireDateTime] ,[M].[Remark] ,[M].[ItemMaintenanceId]", FromCommand = $@"[dbo].[RequireMaintenance] AS [R] LEFT OUTER JOIN [dbo].[ItemMaintenance] AS [M] ON [M].[RequireMaintenanceId] = [R].[RequireMaintenanceId]", WhereCommand = $@"[R].[ItemId] = {Option.ItemId} AND [R].[RequireStatus] != 4", OrderCommand = "[R].[RequireDate] DESC" }; var hasData = await this.dapper.GetListEntites <ItemHistorieViewModel>(sqlCommand); if (hasData.Any()) { return(new JsonResult(hasData, this.DefaultJsonSettings)); } } } catch (Exception ex) { message = $"Has error {ex.ToString()}"; } return(BadRequest(new { message })); }
public void ExecuteSql() { var sqlCommand = new SqlCommandViewModel() { TableName = "Areas", SqlCommand = SqlCommands.AddColumn, NewColumnName = "TestColumn", DataType = "INT" }.ToSqlCommand(); var tables = this.context.Model.GetEntityTypes().Select(x => x.Relational().TableName); this.context.Database.ExecuteSqlCommand(sqlCommand); }
public async Task <IActionResult> GetByItem(int itemId) { var message = "Data not been found."; try { var sqlCommand = new SqlCommandViewModel() { SelectCommand = $@"ob.* ,im.ItemCode ,im.[Name] as [ItemName] ,im.RegisterDate ,im.Property as [SerialNumber] ,wg.GroupDesc as [WorkGroup]", FromCommand = $@"[dbo].[ObsoleteItem] ob LEFT OUTER JOIN [dbo].[Item] im ON ob.ItemId = im.ItemId LEFT OUTER JOIN [VipcoMachineDataBase].[dbo].[EmployeeGroupMIS] wg ON im.GroupMis = wg.GroupMIS", WhereCommand = $@"ob.ItemId = {itemId}", OrderCommand = $@"ob.ObsoleteItemId DESC" }; var hasData = await this.dapper.GetFirstEntity <ObsoleteItemViewModel>(sqlCommand); // Get lifetime if (hasData.RegisterDate != null && hasData.ObsoleteDate != null) { if (hasData.RegisterDate <= hasData.ObsoleteDate) { hasData.Lifetime = hasData.RegisterDate != null && hasData.ObsoleteDate != null? this.CalcLiftTime(hasData.RegisterDate.Value, hasData.ObsoleteDate.Value.DateTime) : "0 ปี 0 เดือน"; } } return(new JsonResult(hasData, this.DefaultJsonSettings)); } catch (Exception ex) { message = $"Has error {ex.ToString()}"; } return(BadRequest(new { message })); }
public async Task <IActionResult> ItemHistoriesExport(int?key) { var message = "Data not been found."; try { if (key != null && key > 0) { var dbItem = await this.repository.GetFirstOrDefaultAsync( x => x, x => x.ItemId == key, null, x => x.Include(z => z.Branch)); if (dbItem != null) { /* * var HasData = await this.repositoryRequireMaintenance * .GetToListAsync(x => new ItemHistorieViewModel * { * Fail = x.Description, * Fix = x.ItemMaintenance == null ? "-" : x.ItemMaintenance.Description, * Date = x.RequireDate, * ItemMaintenanceId = x.ItemMaintenance == null ? 0 : x.ItemMaintenance.ItemMaintenanceId, * Remark = x.ItemMaintenance == null ? "-" : x.ItemMaintenance.Remark * }, x => x.ItemId == dbItem.ItemId && x.RequireStatus != RequireStatus.Cancel, * x => x.OrderByDescending(z => z.RequireDate), * x => x.Include(z => z.ItemMaintenance)); */ var sqlCommand = new SqlCommandViewModel() { SelectCommand = $@"[R].[Description] AS [Fail] ,[M].[Description] AS [Fix] ,[R].[RequireDate] AS [Date] ,[R].[RequireDateTime] ,[M].[Remark] ,[M].[ItemMaintenanceId]", FromCommand = $@"[dbo].[RequireMaintenance] AS [R] LEFT OUTER JOIN [dbo].[ItemMaintenance] AS [M] ON [M].[RequireMaintenanceId] = [R].[RequireMaintenanceId]", WhereCommand = $@"[R].[ItemId] = {dbItem.ItemId} AND [R].[RequireStatus] != 4", OrderCommand = "[R].[RequireDate] DESC" }; var HasData = await this.dapper.GetListEntites <ItemHistorieViewModel>(sqlCommand); var mapItem = this.mapper.Map <Item, ItemViewModel>(dbItem); var memory = new MemoryStream(); var templateFolder = this.hosting.WebRootPath + "/reports/"; var fileExcel = templateFolder + $"ItemHistorie.xlsx"; if (!string.IsNullOrEmpty(dbItem.ItemImage)) { try { var base64 = ""; if (dbItem.ItemImage.Contains("data:image/jpeg;base64,")) { base64 = dbItem.ItemImage.Remove(0, 23); } else if (dbItem.ItemImage.Contains("data:image/png;base64,")) { base64 = dbItem.ItemImage.Remove(0, 22); } var imgBytes = Convert.FromBase64String(base64); using (var imageFile = new FileStream(templateFolder + $"template.png", FileMode.Create)) { imageFile.Write(imgBytes, 0, imgBytes.Length); imageFile.Flush(); } } finally {} } using (var wb = this.excelWorkBookService.Create(fileExcel)) { var ws = wb.Worksheet(1); // Image if (!string.IsNullOrEmpty(dbItem.ItemImage)) { var image1 = ws.AddPicture(templateFolder + $"template.png") .MoveTo(ws.Cell("A3").CellBelow(), 0, 5) .WithSize(450, 228); //.Scale(0.5); // optional: resize picture } // Set data to excel foreach (var field in mapItem.GetType().GetProperties()) // Loop through fields { string name = field.Name; // Get string name var value = field.GetValue(mapItem, null); if (value is DateTime && value != null) { DateTime temp = (DateTime)value; value = $"'{temp.ToString("dd/MMM/")}" + (temp.Year < 2550 ? (temp.Year + 543).ToString() : temp.ToString("yyyy")); } var filter = $"data:{name}"; var cell = ws.Search(filter, CompareOptions.Ordinal).ToList(); cell.ForEach(item => { if (item != null) { item.Value = value ?? ""; item.DataType = XLDataType.Text; } }); } var tableWithPeople = ws.Cell(16, 1).InsertTable(HasData.Select(x => new { x.Date, ItemFail = x.Fail, ItemFix = x.Fix, x.Remark }).AsEnumerable()); tableWithPeople.Style.Alignment.WrapText = true; tableWithPeople.Style.Font.FontName = "Angsana New"; tableWithPeople.Style.Font.FontSize = 14; wb.SaveAs(memory); } memory.Position = 0; return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "export.xlsx")); } } } catch (Exception ex) { message = $"Has error {ex.ToString()}"; } return(BadRequest(new { message })); }
private async Task <List <InvoiceOutStandingViewModel> > GetDataInvoiceOutStanding(ScrollViewModel Scroll, bool option = false) { if (Scroll != null) { // ACC_0 ลูกหนี้ในประเทศ 113101 และ ลูกหนี้ต่างประเทศ 113201 string sWhere = "GAC.FLGCLE_0 = 1 AND GAD.ACC_0 IN ('113101','113201')"; string sSort = ""; #region Where // Filter var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" } : Scroll.Filter.Split(null); foreach (string temp in filters) { if (string.IsNullOrEmpty(temp)) { continue; } string keyword = temp.ToLower(); sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $@"(LOWER(GAD.NUM_0) LIKE '%{keyword}%' OR LOWER(BPC.BPCNAM_0) LIKE '%{keyword}%')"; } // Where Customer if (Scroll.WhereBanks.Any()) { var list = new List <string>(); foreach (var item in Scroll.WhereBanks) { list.Add($"'{item}'"); } var customers = string.Join(',', list); sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $"BPC.BPCNUM_0 IN ({customers})"; } // Where Project if (!string.IsNullOrEmpty(Scroll.WhereProject)) { sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $"SINV.CCE_2 = '{Scroll.WhereProject}'"; } #endregion #region Sort switch (Scroll.SortField) { case "InvoiceNo": if (Scroll.SortOrder == -1) { sSort = $"GAD.NUM_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Pshnum0); } else { sSort = $"GAD.NUM_0 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Pshnum0); } break; case "CustomerNo": if (Scroll.SortOrder == -1) { sSort = $"BPC.BPCNUM_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Pjth0); } else { sSort = $"BPC.BPCNUM_0 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Pjth0); } break; case "CustomerName": if (Scroll.SortOrder == -1) { sSort = $"BPC.BPCNAM_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Prqdat0); } else { sSort = $"BPC.BPCNAM_0 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Prqdat0); } break; case "Project": if (Scroll.SortOrder == -1) { sSort = $"SINV.CCE_2 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Prqdat0); } else { sSort = $"SINV.CCE_2 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Prqdat0); } break; case "DocDateString": if (Scroll.SortOrder == -1) { sSort = $"GAC.DUDDAT_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Prqdat0); } else { sSort = $"GAC.DUDDAT_0 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Prqdat0); } break; case "DueDateString": if (Scroll.SortOrder == -1) { sSort = $"GAH.BPRDATVCR_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Prqdat0); } else { sSort = $"GAH.BPRDATVCR_0 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Prqdat0); } break; default: sSort = $"GAD.NUM_0 ASC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Prqdat0); break; } #endregion var sqlCommnad = new SqlCommandViewModel() { SelectCommand = $@" GAD.NUM_0 AS InvoiceNo, SINV.AMTATI_0 AS InvPriceInTax, SINV.AMTNOT_0 AS InvPriceExTax, GAH.CUR_0 AS Currency, BPC.BPCNUM_0 AS CustomerNo, BPC.ZCOMPNAME_0 AS CustomerName, SINV.CCE_2 AS Project, GAC.FLGCLE_0 AS StatusClose, (CASE WHEN GAH.CUR_0 = 'THB' THEN SINV.AMTATI_0--SIND.NETPRIATI_0 * SIND.QTY_0 END) AS [THB_TAX], (CASE WHEN GAH.CUR_0 = 'USD' THEN SINV.AMTATI_0--SIND.NETPRIATI_0 * SIND.QTY_0 END) AS [USD_TAX], (CASE WHEN GAH.CUR_0 = 'EUR' OR GAH.CUR_0 = 'GBP' THEN SINV.AMTATI_0--SIND.NETPRIATI_0 * SIND.QTY_0 END) AS [EUR_TAX], (CASE WHEN GAH.CUR_0 = 'THB' THEN SINV.AMTNOT_0--SIND.NETPRINOT_0 * SIND.QTY_0 END) AS [THB], (CASE WHEN GAH.CUR_0 = 'USD' THEN SINV.AMTNOT_0--SIND.NETPRINOT_0 * SIND.QTY_0 END) AS [USD], (CASE WHEN GAH.CUR_0 = 'EUR' OR GAH.CUR_0 = 'GBP' THEN SINV.AMTNOT_0--SIND.NETPRINOT_0 * SIND.QTY_0 END) AS [EUR], GAC.DUDDAT_0 AS DueDate, GAH.BPRDATVCR_0 AS DocDate, SYSDATETIME() AS NowDate, DATEDIFF(DAY,SYSDATETIME(),GAC.DUDDAT_0 ) AS DIFF", FromCommand = $@" VIPCO.GACCENTRYD GAD LEFT OUTER JOIN VIPCO.GACCENTRY GAH ON GAD.NUM_0 = GAH.NUM_0 AND GAD.TYP_0 = GAH.TYP_0 LEFT OUTER JOIN VIPCO.GACCDUDATE GAC ON GAD.ACCNUM_0 = GAC.ACCNUM_0 AND GAD.TYP_0 = GAC.TYP_0 AND GAD.LIN_0 = GAC.LIG_0 LEFT OUTER JOIN VIPCO.BPCUSTOMER BPC ON GAD.BPR_0 = BPC.BPCNUM_0 LEFT OUTER JOIN VIPCO.SINVOICE SINV ON GAC.NUM_0 = SINV.NUM_0", WhereCommand = sWhere, OrderCommand = sSort }; var result = await this.repositoryInvOutStand.GetEntitiesAndTotal(sqlCommnad, new { Skip = Scroll.Skip ?? 0, Take = Scroll.Take ?? 50 }); var dbData = result.Entities; Scroll.TotalRow = result.TotalRow; return(dbData); } return(null); }
/* * public async Task<ReturnViewModel<Entity>> GetEntitiesAndTotal<Parameter>(SqlCommandViewModel sqlCommand, Parameter parameter, int timeout = 60) * { * string sSqlCommand = $@"SELECT {sqlCommand.SelectCommand} * FROM {sqlCommand.FromCommand} * WHERE {sqlCommand.WhereCommand} "; * * if (!string.IsNullOrEmpty(sqlCommand.GroupCommand)) * sSqlCommand += $@"GROUP BY {sqlCommand.GroupCommand} "; * * if (!string.IsNullOrEmpty(sqlCommand.OrderCommand)) * sSqlCommand += $@"ORDER BY {sqlCommand.OrderCommand} "; * * sSqlCommand += $@"OFFSET @Skip ROWS -- skip 10 rows * FETCH NEXT @Take ROWS ONLY; -- take 10 rows; * SELECT COUNT(*) * FROM {sqlCommand.FromCommand} * WHERE {sqlCommand.WhereCommand};"; * * using (IDbConnection conn = Connection) * { * conn.Open(); * var result = await conn.QueryMultipleAsync(sSqlCommand, parameter, commandTimeout: timeout); * var dbData = new ReturnViewModel<Entity>() * { * Entities = result.Read<Entity>().ToList(), * TotalRow = result.Read<int>().FirstOrDefault() * }; * conn.Close(); * return dbData; * } * } */ public async Task <ReturnViewModel <Entity> > GetEntitiesAndTotal <Parameter>(SqlCommandViewModel sqlCommand, Parameter parameter, int timeout = 60) { string sSqlCommand = $@"SELECT {sqlCommand.SelectCommand} FROM {sqlCommand.FromCommand} "; if (!string.IsNullOrEmpty(sqlCommand.WhereCommand)) { sSqlCommand += $@"WHERE {sqlCommand.WhereCommand} "; } if (!string.IsNullOrEmpty(sqlCommand.GroupCommand)) { sSqlCommand += $@"GROUP BY {sqlCommand.GroupCommand} "; } // Query Total Record Befor Order By var sSubSqlCommand = $@"SELECT COUNT(*) FROM ( {sSqlCommand} ) AS CountTable;"; if (!string.IsNullOrEmpty(sqlCommand.OrderCommand)) { sSqlCommand += $@"ORDER BY {sqlCommand.OrderCommand} "; } sSqlCommand += $@"OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;"; sSqlCommand += sSubSqlCommand; using (IDbConnection conn = Connection) { conn.Open(); var result = await conn.QueryMultipleAsync(sSqlCommand, parameter, commandTimeout : timeout); var dbData = new ReturnViewModel <Entity>() { Entities = result.Read <Entity>().ToList(), TotalRow = result.Read <int>().FirstOrDefault() }; conn.Close(); return(dbData); } }
private async Task <List <PaymentSubConViewModel> > GetDataPaymentSub(ScrollViewModel Scroll) { if (Scroll != null) { // ACC_0 212403 Only Retention string sWhere = $@" PYH.ACC_0 IN('211101','211303','212403') AND PYH.BAN_0 = '21001'"; string sSort = ""; #region Where // Filter var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" } : Scroll.Filter.Split(null); foreach (string temp in filters) { if (string.IsNullOrEmpty(temp)) { continue; } string keyword = temp.ToLower(); sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $@"(LOWER(PYH.NUM_0) LIKE '%{keyword}%' OR LOWER(PYH.BPR_0) LIKE '%{keyword}%' OR LOWER(PAN.BPRNAM_0) LIKE '%{keyword}%')"; } // Where Partner if (Scroll.WhereBanks.Any()) { var list = new List <string>(); foreach (var item in Scroll.WhereBanks) { list.Add($"'{item}'"); } var partners = string.Join(',', list); sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $"PYH.BPR_0 IN ({partners})"; } // Where Project if (Scroll.WhereProjects.Any()) { var list = new List <string>(); foreach (var item in Scroll.WhereProjects) { list.Add($"'{item}'"); } var projects = string.Join(',', list); sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $"PYA.CCE_2 IN ({projects})"; } if (Scroll.SDate.HasValue) { sWhere += (string.IsNullOrEmpty(sWhere) ? "WHERE " : " AND ") + $"PYH.ACCDAT_0 >= '{Scroll.SDate.Value.ToString("yyyy-MM-dd")}'"; } if (Scroll.EDate.HasValue) { sWhere += (string.IsNullOrEmpty(sWhere) ? "WHERE " : " AND ") + $"PYH.ACCDAT_0 <= '{Scroll.EDate.Value.ToString("yyyy-MM-dd")}'"; } #endregion #region Sort switch (Scroll.SortField) { case "PartnerNo": if (Scroll.SortOrder == -1) { sSort = $"PYH.BPR_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Pshnum0); } else { sSort = $"PYH.BPR_0 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Pshnum0); } break; case "PartnerName": if (Scroll.SortOrder == -1) { sSort = $"PAN.BPRNAM_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Pjth0); } else { sSort = $"PAN.BPRNAM_0 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Pjth0); } break; case "PaymentNo": if (Scroll.SortOrder == -1) { sSort = $"PYH.NUM_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Prqdat0); } else { sSort = $"PYH.NUM_0 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Prqdat0); } break; case "PaymentDateString": if (Scroll.SortOrder == -1) { sSort = $"PYH.ACCDAT_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Prqdat0); } else { sSort = $"PYH.ACCDAT_0 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Prqdat0); } break; case "Reference": if (Scroll.SortOrder == -1) { sSort = $"PYD.VCRNUM_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Prqdat0); } else { sSort = $"PYD.VCRNUM_0 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Prqdat0); } break; case "Project": if (Scroll.SortOrder == -1) { sSort = $"PYA.CCE_2 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Prqdat0); } else { sSort = $"PYA.CCE_2 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Prqdat0); } break; default: sSort = $"PYH.NUM_0,PYH.ACCDAT_0 ASC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Prqdat0); break; } #endregion var sqlCommnad = new SqlCommandViewModel() { SelectCommand = $@" PYH.BPR_0 AS PartnerNo ,PYH.DES_0 AS Comment ,PAN.BPRNAM_0 AS PartnerName ,PYH.NUM_0 AS PaymentNo ,PYD.VCRNUM_0 AS Reference ,PYH.ACCDAT_0 AS PaymentDate ,PYA.CCE_2 AS Project ,PYD.COA_0 AS Currency ,PYD.DENCOD_0 AS Attribute ,PYD.VCRTYP_0 AS PayType -- Progress ,(CASE WHEN PYD.DENCOD_0 = 'PAY' AND PYD.VCRTYP_0 = 'ZSUB' THEN PYD.AMTLIN_0 ELSE NULL END) AS AmountProgress -- หัก Down Payment ,(CASE WHEN PYD.DENCOD_0 = 'PAY' AND PYD.VCRTYP_0 = 'ZDOWN' THEN PYD.AMTLIN_0 ELSE NULL END) AS AmountDown -- หัก Consume ,(CASE WHEN PYD.DENCOD_0 = 'PAY' AND PYD.VCRTYP_0 = 'SREG' THEN PYD.AMTLIN_0 ELSE NULL END) AS AmountConsume -- หัก Retention ,(CASE WHEN PYD.DENCOD_0 = 'GE' THEN PYD.AMTLIN_0 ELSE NULL END) AS AmountRetenion ,(CASE WHEN PYD.DENCOD_0 = 'PVAT' THEN PYD.AMTLIN_0 ELSE NULL END) AS AmountVat ,(CASE WHEN PYD.DENCOD_0 = 'PSVAT' THEN PYD.AMTLIN_0 ELSE NULL END) AS AmountVat2 ,(CASE WHEN PYD.DENCOD_0 = 'T3' THEN PYD.AMTLIN_0 ELSE NULL END) AS AmountTax ,(CASE WHEN PYD.DENCOD_0 = 'T5' THEN PYD.AMTLIN_0 ELSE NULL END) AS AmountTax2 ,(CASE WHEN PYD.DENCOD_0 = 'RET' THEN PYD.AMTLIN_0 ELSE NULL END) AS AmountDeduct ", FromCommand = $@" [VIPCO].[PAYMENTH] PYH INNER JOIN [VIPCO].[PAYMENTD] PYD ON PYH.NUM_0 = PYD.NUM_0 AND PYD.DENCOD_0 IN ('PAY','PVAT','PSVAT','T3','T5','GE','RET') AND PYD.VCRTYP_0 IN ('SREG','ZSUB','ZDOWN','') LEFT OUTER JOIN [VIPCO].[PAYMENTA] PYA ON PYD.NUM_0 = PYA.NUM_0 AND PYD.LIN_0 = PYA.LIN_0 LEFT OUTER JOIN [VIPCO].[BPARTNER] PAN ON PYH.BPR_0 = PAN.BPRNUM_0 ", WhereCommand = sWhere, OrderCommand = sSort }; var result = await this.repositoryPaymentSub.GetEntitiesAndTotal(sqlCommnad, new { Skip = Scroll.Skip ?? 0, Take = Scroll.Take ?? 50 }); var dbData = result.Entities; Scroll.TotalRow = result.TotalRow; return(dbData); } return(null); }
public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll) { var message = "Data not been found."; try { if (Scroll != null) { // ACC_0 ลูกหนี้ในประเทศ 113101 และ ลูกหนี้ต่างประเทศ 113201 string sWhere = ""; string sSort = ""; #region Where // Filter var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" } : Scroll.Filter.Split(null); foreach (string temp in filters) { if (string.IsNullOrEmpty(temp)) { continue; } string keyword = temp.ToLower(); sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $@"(LOWER([sp].[Name]) LIKE N'%{keyword}%' OR LOWER([sp].[Model]) LIKE N'%{keyword}%')"; } // Where if (!string.IsNullOrEmpty(Scroll.Where)) { Scroll.Where = Scroll.Where.ToLower(); sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $@"(LOWER([sp].[Code]) LIKE N'{Scroll.Where}%')"; } #endregion Where #region Sort switch (Scroll.SortField) { case "Code": if (Scroll.SortOrder == -1) { sSort = $"[sp].[Code] DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Pshnum0); } else { sSort = $"[sp].[Code] ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Pshnum0); } break; case "Name": if (Scroll.SortOrder == -1) { sSort = $"[sp].[Name] DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Prqdat0); } else { sSort = $"[sp].[Name] ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Prqdat0); } break; case "Model": if (Scroll.SortOrder == -1) { sSort = $"[sp].[Model] DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Pjth0); } else { sSort = $"[sp].[Model] ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Pjth0); } break; default: sSort = $"[sp].[Code] ASC,[sp].[Name] ASC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Prqdat0); break; } #endregion Sort var sqlCommnad = new SqlCommandViewModel() { SelectCommand = $@" [sp].[SparePartId] ,[sp].[Code] ,[sp].[Name] ,[sp].[Model] ,SUM( CASE [ms].[MovementStatus] WHEN 1 THEN [ms].[Quantity] WHEN 3 THEN [ms].[Quantity] WHEN 2 THEN [ms].[Quantity] * -1 WHEN 4 THEN [ms].[Quantity] * -1 ELSE 0 END ) AS [OnHand]", FromCommand = $@" [dbo].[SparePart] sp LEFT OUTER JOIN [dbo].[MovementStockSp] ms ON ms.SparePartId = sp.SparePartId AND ms.MovementStatus != 5", WhereCommand = sWhere, GroupCommand = $@"[sp].[SparePartId],[sp].[Name] ,[sp].[Model],[sp].[Code]", OrderCommand = sSort }; var result = await this.dapper.GetEntitiesAndTotal(sqlCommnad, new { Skip = Scroll.Skip ?? 0, Take = Scroll.Take ?? 50 }); var dbData = result.Entities; Scroll.TotalRow = result.TotalRow; return(new JsonResult(new ScrollDataViewModel <SparePartViewModel>(Scroll, dbData), this.DefaultJsonSettings)); } } catch (Exception ex) { message = $"Has error {ex.ToString()}"; } return(BadRequest(new { message })); }
private async void DoWork(object state) { var dateNow = DateTime.Now; var sendTime = new List <int>() { 7, 12, 18 }; if (sendTime.Contains(dateNow.Hour)) { try { using (var scope = this.Services.CreateScope()) { #region Get Data var repo3 = scope.ServiceProvider.GetRequiredService <IRepositorySageX3Extend <ReceiptExtend> >(); var repo2 = scope.ServiceProvider.GetRequiredService <IRepositorySageX3Extend <TaskStatusMaster> >(); var workGroups = (await repo2.GetToListAsync(x => new { x.WorkGroupCode, x.WorkGroupName }, x => x.TaskStatusDetails.Any())).ToList(); var lastGet = await repo3.GetFirstOrDefaultAsync(x => x, null, x => x.OrderByDescending(z => z.EndRange)); var newLast = new ReceiptExtend() { CreateDate = dateNow, Creator = "System", GetDate = dateNow, GetTime = dateNow.ToString("HH:mm"), StartRange = lastGet?.EndRange ?? 0, EndRange = 0 }; if (lastGet != null && workGroups.Any()) { var help = scope.ServiceProvider.GetRequiredService <IHelperService>(); var repo = scope.ServiceProvider.GetRequiredService <IRepositorySageX3Extend <TaskStatusDetail> >(); var scopedProcessingService = scope.ServiceProvider.GetRequiredService <IEmailSender>(); #region Create DataTable var table = new DataTable(); //Adding the Columns table.Columns.AddRange(new DataColumn[] { new DataColumn("PrNo", typeof(string)), new DataColumn("JobNo", typeof(string)), new DataColumn("Item-Name", typeof(string)), new DataColumn("Uom", typeof(string)), new DataColumn("BomLv", typeof(string)), new DataColumn("WorkGroup", typeof(string)), new DataColumn("Qty", typeof(int)), new DataColumn("PoNo", typeof(string)), new DataColumn("QtyPo", typeof(int)), new DataColumn("TypePo", typeof(string)), new DataColumn("QtyRc", typeof(int)), }); #endregion foreach (var workgroup in workGroups) { #region GetData var sqlCommand = new SqlCommandViewModel() { SelectCommand = $@" --RECIPT PRD.ROWID AS [RowId], PRD.POHNUM_0 AS [PoNumber], PRD.POPLIN_0 AS [PoLine], PRD.ITMREF_0 AS [ItemCode], PRD.QTYPUU_0 AS [RCQuantityPur], DIM.CCE_0 AS [Branch], (SELECT CAC.DES_0 FROM VIPCO.CACCE CAC WHERE CAC.CCE_0 = DIM.CCE_0) AS [BranchName], DIM.CCE_1 AS [WorkItem], (SELECT CAC.DES_0 FROM VIPCO.CACCE CAC WHERE CAC.CCE_0 = DIM.CCE_1) AS [WorkItemName], DIM.CCE_2 AS [Project], (SELECT CAC.DES_0 FROM VIPCO.CACCE CAC WHERE CAC.CCE_0 = DIM.CCE_2) AS [ProjectName], DIM.CCE_3 AS [WorkGroup], (SELECT CAC.DES_0 FROM VIPCO.CACCE CAC WHERE CAC.CCE_0 = DIM.CCE_3) AS [WorkGroupName], --PURCHASE ORDER POH.ZPO21_0 AS [PoStatusInt], POD.QTYPUU_0 AS [PoQuantityPur], --PURCHASE REQUEST PRO.PSHNUM_0 AS [PrNumber], PRO.PSDLIN_0 AS [PrLine], PRO.QTYPUU_0 AS [QuantityPur], PRO.PUU_0 AS [PurUom], --ITEMMASTER TXT.TEXTE_0 AS [ItemName]", FromCommand = $@" [VIPCO].[PRECEIPTD] PRD LEFT OUTER JOIN VIPCO.CPTANALIN DIM ON DIM.ABRFIC_0 = 'PTD' AND DIM.VCRTYP_0 = 0 AND DIM.VCRSEQ_0 = 0 AND DIM.CPLCLE_0 = '' AND DIM.ANALIG_0 = 1 AND PRD.PTHNUM_0 = DIM.VCRNUM_0 AND PRD.PTDLIN_0 = DIM.VCRLIN_0 LEFT OUTER JOIN VIPCO.PORDER POH ON PRD.POHNUM_0 = POH.POHNUM_0 LEFT OUTER JOIN VIPCO.PORDERQ POD ON PRD.POHNUM_0 = POD.POHNUM_0 AND PRD.POPLIN_0 = POD.POPLIN_0 LEFT OUTER JOIN VIPCO.PREQUISO PRO ON PRO.POHNUM_0 = PRD.POHNUM_0 AND PRO.POPLIN_0 = PRD.POPLIN_0 LEFT OUTER JOIN VIPCO.ITMMASTER ITM ON PRD.ITMREF_0 = ITM.ITMREF_0 LEFT OUTER JOIN VIPCO.TEXCLOB TXT ON TXT.CODE_0 = ITM.PURTEX_0", WhereCommand = $@" PRD.ROWID > {lastGet.EndRange} AND DIM.CCE_3 = '{workgroup.WorkGroupCode.Trim()}'", OrderCommand = $@" PRD.POHNUM_0" }; var dapper = scope.ServiceProvider.GetRequiredService <IRepositoryDapperSageX3 <ScheduleReceiptViewModel> >(); var hasData = await dapper.GetEntities(sqlCommand); var allJob = hasData.Select(x => x.Project).OrderBy(x => x).Distinct().ToList(); #endregion #region Create Message var message = $@"<body style='margin:0; padding:0;background-color:#EFF9FB;'> <table align='center' border='0' cellpadding='0' cellspacing='0' width='650' style='border-collapse: collapse;'> <tr> <td align='center' bgcolor='#70bbd9' style='padding: 40px 0 30px 0;font-family: Avenir, sans-serif; font-size: 18px;'> <h1 style='color:whitesmoke;'> <strong>Vipco SageX3 extend system</strong></h1> <hr style='width:80%;'/> <h4 style='font-family: Avenir, sans-serif; font-size: 15px;'> ระบบแจ้งเตือนการลงรับ วัตถุดิบ,วัสดุ หรือ เครื่องมือ จากระบบ SageX3</h4> <!--<img src='images/h1.gif' alt='Creating Email Magic' width='300' height='230' style='display: block;' />--> </td> </tr> <tr> <td bgcolor='#ffffff' style='padding: 30px 20px 30px 20px;'> <table border='0' cellpadding='0' cellspacing='0' width='100%'> <tr> <td style='padding: 5px 0 5px 0;font-family: Avenir, sans-serif; font-size: 18px;'> <b>เรียน</b><i> ผู้เกี่ยวข้องทุกท่าน</i> </td> </tr> <tr> <td style='padding: 20px 0 5px 0;font-family: Avenir, sans-serif; font-size: 16px;'> เนื่องด้วยระบบ Vipco SageX3 extend system ได้ตรวจพบการรับเข้า วัตถุดิบ,วัสดุ หรือ เครื่องมือ ของกลุ่มงานที่ท่านมีความเกี่ยวข้อง ผ่านทางระบบ SageX3 <br/><br/> ระบบจึงทำการแจ้งเตือนผู้เกี่ยวข้องทุกๆท่าน ให้เข้าตรวจสอบข้อมูลดังกล่าว โดยพร้อมทั้งนี้ระบบได้ทำการแนบ ข้อมูลคำขอสั่งซื้อและ ข้อมูลสั่งซื้อมาพร้อมกับอีเมล์ฉบับนี้ </td> </tr> <tr> <td style='padding: 5px 0 30px 0;font-family: Avenir, sans-serif; font-size: 16px;'> เลขที่งานที่ตรวจพบดังนี้ : {string.Join(",", allJob)} </td> </tr> </table> </td> </tr> <tr> <td bgcolor='#ee4c50' style='padding: 15px 15px 15px 15px;'> <table border='0' cellpadding='0' cellspacing='0' width='100%'> <tr> <td width='80%' style='color:whitesmoke;padding: 5px 0px 5px 0px;font-family: Avenir, sans-serif; font-size: 12px;'> This mail auto generated by VIPCO SageX3 extend system.<br/> Do not reply this email. </td> <td style='font-family: Avenir, sans-serif; font-size: 12px;'> <a href='http://192.168.2.31/extends-sagex3/purchase-request' >more information.</a> </td> </tr> </table> </td> </tr> </table> </body>"; #endregion #region Excel if (hasData.Any()) { #region Create Table // Update row index var last = hasData.Max(x => x.RowId); newLast.EndRange = last > newLast.EndRange ? last : newLast.EndRange; table.Rows.Clear(); //Adding the Rows foreach (var item in hasData) { if (item == null) { continue; } if (!string.IsNullOrEmpty(item.ItemName)) { if (item.ItemName.StartsWith("{\\rtf1")) { item.ItemName = Rtf.ToHtml(item.ItemName); } item.ItemName = help.ConvertHtmlToText(item.ItemName); item.ItemName = item.ItemName.Replace("\r\n", ""); item.ItemName = item.ItemName.Replace("\n", ""); } //var Receipt = ""; table.Rows.Add( item.PrNumber, item.Project, item.ItemName, item.PurUom, item.WorkItemName, item.WorkGroupName, item.QuantityPur, item.PoNumber, item.PoQuantityPur, item.PoStatus, item.RCQuantityPur); } var excel = help.CreateExcelFilePivotTables(table, "PurchaseStatus", "PurchaseStatusPivot"); #endregion #region GetMail Address var MailTos = await repo.GetToListAsync(x => x.Email, x => x.TaskStatusMaster.WorkGroupCode == workgroup.WorkGroupCode); #endregion #region Send Mail await scopedProcessingService.SendMail(new ViewModels.EmailViewModel() { MailFrom = MailTos.FirstOrDefault(), MailTos = MailTos.ToList(),//new List<string>() { "*****@*****.**" }, Message = message, NameFrom = "No-Reply", Subject = $"{workgroup.WorkGroupName} Notification mail from Vipco SageX3 extend system.", HasAttach = true, Attachment = new Attachment(excel, "Export.xlsx") }); #endregion excel.Dispose(); } #endregion } await repo3.AddAsync(newLast); } #endregion } } catch (Exception ex) { using (var scope = this.Services.CreateScope()) { var scopedProcessingService = scope.ServiceProvider.GetRequiredService <IEmailSender>(); await scopedProcessingService.SendMail(new ViewModels.EmailViewModel() { MailFrom = "*****@*****.**", MailTos = new List <string>() { "*****@*****.**" }, Message = $"Has error {ex.ToString()}", NameFrom = "Notification", Subject = $"Notification error.{dateNow}", }); } } } _logger.LogInformation($"Timed Background Service is working. at {dateNow}."); }
private async Task <List <ItemViewModel> > GetData2(ScrollViewModel scroll) { if (scroll != null) { string sWhere = ""; string sSort = ""; #region Where // Filter var filters = string.IsNullOrEmpty(scroll.Filter) ? new string[] { "" } : scroll.Filter.Split(null); foreach (string temp in filters) { if (string.IsNullOrEmpty(temp)) { continue; } string keyword = temp.ToLower(); sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $@"(LOWER([b].[Name]) LIKE '%{keyword}%' OR LOWER([i].[Description]) LIKE '%{keyword}%' OR LOWER([i].[ItemCode]) LIKE '%{keyword}%' OR LOWER([i].[Name]) LIKE '%{keyword}%')"; } if (scroll.WhereId.HasValue) { if (scroll.WhereId > 0) { sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $"[t].[ItemTypeId] = '{scroll.WhereId}'"; } } if (!string.IsNullOrEmpty(scroll.Where)) { sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $"[i].[Creator] = '{scroll.WhereId}'"; } if (scroll.Where2Id.HasValue) { sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $"[i].[ItemStatus] != '3'"; } if (scroll.SDate.HasValue) { sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $"[i].RegisterDate >= '{scroll.SDate.Value.ToString("yyyy-MM-dd")}'"; } if (scroll.EDate.HasValue) { sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $"[i].RegisterDate <= '{scroll.EDate.Value.ToString("yyyy-MM-dd")}'"; } #endregion #region Sort switch (scroll.SortField) { case "ItemCode": if (scroll.SortOrder == -1) { sSort = $"[i].[ItemCode] DESC"; } else { sSort = $"[i].[ItemCode] ASC"; } break; case "Name": if (scroll.SortOrder == -1) { sSort = $"[i].[EmpCode] DESC"; } else { sSort = $"[i].[EmpCode] ASC"; } break; case "ItemTypeString": if (scroll.SortOrder == -1) { sSort = $"[t].[Name] DESC"; } else { sSort = $"[t].[Name] ASC"; } break; default: sSort = $"[i].[ItemCode] ASC"; break; } #endregion var sqlCommnad = new SqlCommandViewModel() { SelectCommand = $@" [i].[ItemId] ,[i].[ItemCode] ,[i].[Name] ,[i].[Model] ,[i].[Property] ,[i].[EmpResponsible] ,[i].[GroupMis] ,[i].[RegisterDate] ,[i].[CancelDate] ,[b].[Name] AS [BranchString] ,[g].[GroupDesc] AS [GroupMisString] ,[t].[Name] AS [ItemTypeString] ,[el].[Namethai] AS [EmpResposibleString]", FromCommand = $@" [VipcoMaintenanceDataBase].[dbo].[Item] i LEFT OUTER JOIN [VipcoMaintenanceDataBase].[dbo].[Branch] b ON [b].[BranchId] = [i].[BranchId] LEFT OUTER JOIN [VipcoMaintenanceDataBase].[dbo].[ItemType] t ON [t].[ItemTypeId] = [i].[ItemTypeId] LEFT OUTER JOIN [VipcoMachineDataBase].[dbo].[Employee] el ON [el].[EmpCode] = [i].[EmpResponsible] LEFT OUTER JOIN [VipcoMachineDataBase].[dbo].[EmployeeGroupMIS] g ON [g].[GroupMIS] = [i].[GroupMis]", WhereCommand = sWhere, OrderCommand = sSort, }; var result = await this.dapper.GetEntitiesAndTotal <ScrollViewModel, ItemViewModel> (sqlCommnad, new ScrollViewModel { Skip = scroll.Skip ?? 0, Take = scroll.Take ?? 50 }); var dbData = result.Entities; scroll.TotalRow = result.TotalRow; // return json result return(dbData); } return(null); }
private async Task <List <PrOutStandingViewModel> > GetPrOutStanding(ScrollViewModel Scroll) { if (Scroll != null) { var listPr = (await this.repository.GetToListAsync(x => new { x.PRNumber, x.PrReceivedDate, x.PrReceivedTime, PrString = $"'{x.PRNumber}'" }, x => x.PrReceivedDate.Value.Date >= Scroll.SDate.Value.Date && x.PrReceivedDate.Value.Date <= Scroll.EDate.Value.Date)).Distinct().ToList(); if (listPr.Any()) { // ACC_0 ลูกหนี้ในประเทศ 113101 และ ลูกหนี้ต่างประเทศ 113201 string sWhere = ""; string sSort = ""; #region Where // Where PR-Number sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $"PRH.PSHNUM_0 IN ({string.Join(',', listPr.Select(x => x.PrString).ToList())})"; #endregion Where #region Sort sSort = $"PRH.PSHNUM_0,PRD.PSDLIN_0 ASC"; #endregion Sort var sqlCommnad = new SqlCommandViewModel() { SelectCommand = $@" PRD.PSHNUM_0 AS [PrNumber], PRD.PSDLIN_0 AS [PrLine], PRH.PJTH_0 AS [Project], PRH.PRQDAT_0 AS [PrDate], PRD.EXTRCPDAT_0 AS [RequestDate], PRH.ZPR11_0 AS [Other], PRH.ZPR30_0 AS [PrType], PRD.ITMREF_0 AS [ItemNo], PRD.ITMDES_0 AS [ItemName], TXT.TEXTE_0 AS [TextName], PRD.PUU_0 AS [Uom], DIM.CCE_0 AS [Branch], BOM.TEXTE_0 AS [WorkItem], DIM.CCE_2 AS [ProjectLine], WG.TEXTE_0 AS [WorkGroup], PRD.QTYPUU_0 AS [Quantity], PRH.CLEFLG_0 AS [StatusClose], PRH.ORDFLG_0 AS [StatusOrder], PRH.CREUSR_0 AS [CreateBy], ITM.ITMWEI_0 AS [ItemWeigth]", FromCommand = $@" VIPCO.PREQUISD PRD LEFT OUTER JOIN VIPCO.PREQUIS PRH ON PRD.PSHNUM_0 = PRH.PSHNUM_0 LEFT OUTER JOIN VIPCO.TEXCLOB TXT ON PRD.LINTEX_0 = TXT.CODE_0 LEFT OUTER JOIN VIPCO.CPTANALIN DIM ON DIM.ABRFIC_0 = 'PSD' AND DIM.VCRTYP_0 = 0 AND DIM.VCRSEQ_0 = 0 AND DIM.CPLCLE_0 = '' AND DIM.ANALIG_0 = 1 AND PRD.PSHNUM_0 = DIM.VCRNUM_0 AND PRD.PSDLIN_0 = DIM.VCRLIN_0 LEFT OUTER JOIN [VIPCO].[ATEXTRA] BOM ON DIM.CCE_1 = BOM.IDENT2_0 AND BOM.ZONE_0 = 'LNGDES' AND BOM.IDENT1_0 = '3000' LEFT OUTER JOIN [VIPCO].[ATEXTRA] WG ON DIM.CCE_3 = WG.IDENT2_0 AND WG.ZONE_0 = 'DESTRA' AND WG.IDENT1_0 = 'WG' LEFT OUTER JOIN [VIPCO].[ITMMASTER] ITM ON PRD.ITMREF_0 = ITM.ITMREF_0", WhereCommand = sWhere, OrderCommand = sSort }; var dbData = await this.repositoryPrOutStanding.GetEntities(sqlCommnad); // Get purchase request no // New requirement 03/04/19 foreach (var item in dbData) { if (!string.IsNullOrEmpty(item.TextName)) { if (item.TextName.StartsWith("{\\rtf1")) { item.TextName = Rtf.ToHtml(item.TextName); } } else { item.TextName = item.ItemName; } // New requirement 03/04/19 var prExline = listPr.FirstOrDefault(x => x.PRNumber.ToLower() == item.PrNumber.ToLower()); if (prExline != null) { item.ReceivedDate = prExline.PrReceivedDate == null ? "" : prExline.PrReceivedDate.Value.ToString("dd/MM/yy ") + prExline.PrReceivedTime; } else { item.ReceivedDate = ""; item.PurchaseComment = ""; } } return(dbData); } } return(null); }
private async Task <List <MiscAndAcountViewModel> > GetData2(ScrollViewModel scroll, bool option = false) { if (scroll != null) { string sWhere = ""; string sSort = ""; string sQuery = ""; #region Where var filters = string.IsNullOrEmpty(scroll.Filter) ? new string[] { "" } : scroll.Filter.Split(null); foreach (string temp in filters) { if (string.IsNullOrEmpty(temp)) { continue; } string keyword = temp.ToLower(); sWhere += (string.IsNullOrEmpty(sWhere) ? "WHERE " : " AND ") + $@"(LOWER(SMH.VCRNUM_0) LIKE '%{keyword}%' OR LOWER(SMH.VCRDES_0) LIKE '%{keyword}%' OR LOWER(ACH.NUM_0) LIKE '%{keyword}%' OR LOWER(ACH.BPRVCR_0) LIKE '%{keyword}%')"; } if (!string.IsNullOrEmpty(scroll.WhereProject)) { sWhere += (string.IsNullOrEmpty(sWhere) ? "WHERE " : " AND ") + $"SMH.PJT_0 = '{scroll.WhereProject}'"; } if (scroll.SDate.HasValue) { sWhere += (string.IsNullOrEmpty(sWhere) ? "WHERE " : " AND ") + $"SMH.IPTDAT_0 >= '{scroll.SDate.Value.ToString("yyyy-MM-dd")}'"; } if (scroll.EDate.HasValue) { sWhere += (string.IsNullOrEmpty(sWhere) ? "WHERE " : " AND ") + $"SMH.IPTDAT_0 <= '{scroll.EDate.Value.ToString("yyyy-MM-dd")}'"; } if (scroll.SDate2.HasValue) { sWhere += (string.IsNullOrEmpty(sWhere) ? "WHERE " : " AND ") + $"ACH.BPRDATVCR_0 >= '{scroll.SDate2.Value.ToString("yyyy-MM-dd")}'"; } if (scroll.EDate2.HasValue) { sWhere += (string.IsNullOrEmpty(sWhere) ? "WHERE " : " AND ") + $"ACH.BPRDATVCR_0 <= '{scroll.EDate2.Value.ToString("yyyy-MM-dd")}'"; } #endregion Where #region Sort switch (scroll.SortField) { case "MiscNumber": if (scroll.SortOrder == -1) { sSort = $"SMH.VCRNUM_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.SMH.Pshnum0); } else { sSort = $"SMH.VCRNUM_0 ASC"; //QueryData = QueryData.OrderBy(x => x.SMH.Pshnum0); } break; case "MiscDateString": if (scroll.SortOrder == -1) { sSort = $"SMH.IPTDAT_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.SMH.Pjth0); } else { sSort = $"SMH.IPTDAT_0 ASC"; //QueryData = QueryData.OrderBy(x => x.SMH.Pjth0); } break; case "DocDateString": if (scroll.SortOrder == -1) { sSort = $"ACH.BPRDATVCR_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.SMH.Pjth0); } else { sSort = $"ACH.BPRDATVCR_0 ASC"; //QueryData = QueryData.OrderBy(x => x.SMH.Pjth0); } break; case "ProjectCode": if (scroll.SortOrder == -1) { sSort = $"SMH.PJT_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.SMH.Prqdat0); } else { sSort = $"SMH.PJT_0 ASC"; //QueryData = QueryData.OrderBy(x => x.SMH.Prqdat0); } break; case "Description": if (scroll.SortOrder == -1) { sSort = $"SMH.VCRDES_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.prd.Itmdes0); } else { sSort = $"SMH.VCRDES_0 ASC"; //QueryData = QueryData.OrderBy(x => x.prd.Itmdes0); } break; case "AccNumber": if (scroll.SortOrder == -1) { sSort = $"ACH.NUM_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.SMH.Cce0); } else { sSort = $"ACH.NUM_0 ASC"; //QueryData = QueryData.OrderBy(x => x.SMH.Cce0); } break; case "AccDateString": if (scroll.SortOrder == -1) { sSort = $"ACH.ACCDAT_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.SMH.Cce1); } else { sSort = $"ACH.ACCDAT_0 ASC"; //QueryData = QueryData.OrderBy(x => x.SMH.Cce1); } break; case "AccIssue": if (scroll.SortOrder == -1) { sSort = $"ACH.DESVCR_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.SMH.Cce3); } else { sSort = $"ACH.DESVCR_0 ASC"; //QueryData = QueryData.OrderBy(x => x.SMH.Cce3); } break; default: sSort = $"SMH.IPTDAT_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.SMH.Prqdat0); break; } #endregion Sort #region Query // Query mulitple command sQuery = $@"SELECT SMH.VCRNUM_0 AS [MiscNumber], SMH.IPTDAT_0 AS [MiscDate], SMH.PJT_0 AS [Project], SMH.VCRDES_0 AS [Description], ACH.NUM_0 AS [AccNumber], ACH.ACCDAT_0 AS [AccDate], ACH.BPRVCR_0 AS [MiscLink], ACH.JOU_0 AS [AccType], ACH.CAT_0 AS [AccCat], ACH.DESVCR_0 AS [AccIssue], ACH.BPRDATVCR_0 AS [DocDate] FROM VIPCO.SMVTH SMH INNER JOIN VIPCO.GACCENTRY ACH WITH(INDEX(GACCENTRY_ROWID)) ON SMH.VCRNUM_0 = ACH.BPRVCR_0 AND ACH.DESVCR_0 = 'Miscellaneous issue' AND ACH.JOU_0 = 'STOCK' {sWhere} ORDER BY {sSort} OFFSET @Skip ROWS -- skip 10 rows FETCH NEXT @Take ROWS ONLY; -- take 10 rows; SELECT COUNT(*) FROM VIPCO.SMVTH SMH INNER JOIN VIPCO.GACCENTRY ACH WITH(INDEX(GACCENTRY_ROWID)) ON SMH.VCRNUM_0 = ACH.BPRVCR_0 AND ACH.DESVCR_0 = 'Miscellaneous issue' AND ACH.JOU_0 = 'STOCK' {sWhere};"; #endregion Query var result = await this.repositoryMiscAndAcc.GetListEntitesAndTotalRow(sQuery, new { Skip = scroll.Skip ?? 0, Take = scroll.Take ?? 15 }); var dbData = result.Entities; scroll.TotalRow = result.TotalRow; // Get MiscNumber var list = new List <string>(); var sSubWhere = " SMD.VCRTYP_0 = 20"; foreach (var item in dbData.Select(z => z.MiscNumber).ToList()) { list.Add($"'{item}'"); } var miscnumbers = string.Join(',', list); sSubWhere += (string.IsNullOrEmpty(sSubWhere) ? " " : " AND ") + $"SMD.VCRNUM_0 IN ({miscnumbers})"; var sqlCommnad = new SqlCommandViewModel() { SelectCommand = $@" SMD.VCRNUM_0 AS [MiscNumber], SMD.VCRLIN_0 AS [MiscLine], SMD.ITMREF_0 AS [ItemCode], SMD.ITMDES1_0 AS [ItemName], SMD.PCU_0 AS [Uom], SUM(SJU.QTYPCU_0 * -1) AS [Qty], SJU.CCE_0 AS [Branch], SJU.CCE_1 AS [WorkItem], SJU.CCE_2 AS [Project], SJU.CCE_3 AS [WorkGroup], ITM.PURTEX_0 AS [ItemNameREF], TXT.TEXTE_0 AS [ItemNameRFT] ", FromCommand = $@" VIPCO.SMVTD SMD LEFT OUTER JOIN VIPCO.ITMMASTER ITM ON ITM.ITMREF_0 = SMD.ITMREF_0 LEFT OUTER JOIN VIPCO.STOJOU SJU WITH(INDEX(STOJOU_ROWID)) ON SMD.VCRNUM_0 = SJU.VCRNUM_0 AND SMD.VCRLIN_0 = SJU.VCRLIN_0 AND SJU.TRSTYP_0 = 2 AND SJU.REGFLG_0 = 1 LEFT JOIN VIPCO.TEXCLOB TXT ON TXT.CODE_0 = ITM.PURTEX_0 ", WhereCommand = sSubWhere, GroupCommand = $@" SMD.VCRNUM_0,SMD.VCRLIN_0,SMD.ITMREF_0,SMD.ITMDES1_0,SMD.PCU_0, SJU.CCE_0,SJU.CCE_1,SJU.CCE_2,SJU.CCE_3,ITM.PURTEX_0,TXT.TEXTE_0 ", OrderCommand = " SMD.VCRNUM_0,SMD.VCRLIN_0 " }; var issues = await this.repositoryIssue.GetEntities(sqlCommnad); // Get Acc foreach (var item in dbData.Select(z => z.AccNumber).ToList()) { list.Add($"'{item}'"); } var accnumbers = string.Join(',', list); sSubWhere = $" ACA.NUM_0 IN ({accnumbers})"; sqlCommnad = new SqlCommandViewModel() { SelectCommand = $@" ACA.NUM_0 AS [AccNumber], ACA.LIN_0 AS [AccLine], ACA.SNS_0 AS [CurType], ACA.AMTCUR_0 AS [AmountCurrency], ACA.ACC_0 AS [AccountCode], ACA.CCE_0 AS [Branch], ACA.CCE_1 AS [WorkItem], ACA.CCE_2 AS [Project], ACA.CCE_3 AS [WorkGroup], ACD.ACCNUM_0 AS [AccountNumber], ACD.DES_0 AS [Description], ACD.FREREF_0 AS [FreeREF] ", FromCommand = $@" VIPCO.GACCENTRYA ACA LEFT OUTER JOIN VIPCO.GACCENTRYD ACD ON ACA.NUM_0 = ACD.NUM_0 AND ACA.TYP_0 = ACD.TYP_0 AND ACA.LIN_0 = ACD.LIN_0 AND ACA.LEDTYP_0 = ACD.LEDTYP_0 ", WhereCommand = sSubWhere, OrderCommand = " ACA.CCE_2,ACA.LIN_0 " }; var journals = await this.repositoryJournal.GetEntities(sqlCommnad); foreach (var item in dbData) { #region Issue foreach (var newIssue in issues.Where(z => z.MiscNumber == item.MiscNumber).ToList()) { if (string.IsNullOrEmpty(newIssue.ItemNameRFT)) { newIssue.ItemNameRFT = newIssue.ItemName; } else { if (newIssue.ItemNameRFT.StartsWith("{\\rtf1") && !option) { newIssue.ItemNameRFT = Rtf.ToHtml(newIssue.ItemNameRFT); } } item.Issues.Add(newIssue); } #endregion Issue #region Journal foreach (var newJournal in journals.Where(z => z.AccNumber == item.AccNumber).ToList()) { item.Journals.Add(newJournal); } #endregion Journal } return(dbData); } return(null); }
public async Task <IActionResult> GetBranchScroll([FromBody] ScrollViewModel Scroll) { var message = "Data not been found."; try { if (Scroll == null) { return(BadRequest()); } string sWhere = $@" BCH.IDENT1_0 = 'BCH' AND BCH.ZONE_0 = 'DESTRA' AND BCH.CODFIC_0 = 'CACCE'"; string sSort = ""; #region Where // Filter var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" } : Scroll.Filter.Split(null); foreach (string temp in filters) { if (string.IsNullOrEmpty(temp)) { continue; } string keyword = temp.ToLower(); sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $@"(LOWER(BCH.TEXTE_0) LIKE '%{keyword}%' OR LOWER(BCH.IDENT2_0) LIKE '%{keyword}%')"; } // Where Branch #endregion #region Sort switch (Scroll.SortField) { case "BranchName": if (Scroll.SortOrder == -1) { sSort = $"BCH.TEXTE_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Pshnum0); } else { sSort = $"BCH.TEXTE_0 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Pshnum0); } break; case "BranchCode": if (Scroll.SortOrder == -1) { sSort = $"BCH.IDENT2_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Pjth0); } else { sSort = $"BCH.IDENT2_0 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Pjth0); } break; default: sSort = $"BCH.IDENT2_0 ASC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Prqdat0); break; } #endregion var sqlCommnad = new SqlCommandViewModel() { SelectCommand = $@" BCH.TEXTE_0 AS [BranchName], BCH.IDENT2_0 AS [BranchCode], ROWID AS [RowId]", FromCommand = $@" VIPCO.ATEXTRA BCH", WhereCommand = sWhere, OrderCommand = sSort }; var result = await this.repositoryBranch.GetEntitiesAndTotal(sqlCommnad, new { Skip = Scroll.Skip ?? 0, Take = Scroll.Take ?? 50 }); var dbData = result.Entities; Scroll.TotalRow = result.TotalRow; return(new JsonResult(new ScrollDataViewModel <BranchViewModel>(Scroll, dbData), this.DefaultJsonSettings)); } catch (Exception ex) { message = $"Has error {ex.ToString()}"; } return(BadRequest(new { message })); }
public override async Task <IActionResult> Get(int key) { /* * var HasItem = await this.repository.GetFirstOrDefaultAsync( * z => new ItemViewModel * { * BranchId = z.BranchId, * BranchString = z.Branch.Name, * Brand = z.Brand, * Name = z.Name, * CancelDate = z.CancelDate, * CreateDate = z.CreateDate, * Creator = z.Creator, * Description = z.Description, * EmpResponsible =z.EmpResponsible, * GroupMis = z.GroupMis, * ItemCode = z.ItemCode, * ItemId = z.ItemId, * ItemImage = z.ItemImage, * ItemStatus = z.ItemStatus, * ItemStatusString = System.Enum.GetName(typeof(ItemStatus), z.ItemStatus), * ItemTypeId = z.ItemTypeId, * ItemTypeString = z.ItemType == null ? "-" : z.ItemType.Name, * Model = z.Model, * ModifyDate = z.ModifyDate, * Modifyer = z.Modifyer, * Property = z.Property, * Property2 = z.Property2, * Property3 = z.Property3, * RegisterDate = z.RegisterDate, * }, * z => z.ItemId == key, null, * z => z.Include(x => x.ItemType).Include(x => x.Branch)); * * if (HasItem != null) * { * if (!string.IsNullOrEmpty(HasItem.EmpResponsible)) * HasItem.EmpResposibleString = (await this.repositoryEmp.GetAsync(HasItem.EmpResponsible)).NameThai; * if (!string.IsNullOrEmpty(HasItem.GroupMis)) * HasItem.GroupMisString = (await this.repositoryGroupMis.GetAsync(HasItem.GroupMis)).GroupDesc ?? "-"; * HasItem.ItemImage = HasItem.ItemImage; * return new JsonResult(HasItem, this.DefaultJsonSettings); * } */ if (key > 0) { var sqlCommand = new SqlCommandViewModel() { SelectCommand = $@" [i].* ,[t].[Name] AS [ItemTypeString] ,[b].[Name] AS [BranchString] ,[g].[GroupDesc] AS [GroupMisString] ,[el].[Namethai] AS [EmpResposibleString]", FromCommand = $@" [VipcoMaintenanceDataBase].[dbo].[Item] i LEFT OUTER JOIN [VipcoMaintenanceDataBase].[dbo].[Branch] b ON [b].[BranchId] = [i].[BranchId] LEFT OUTER JOIN [VipcoMaintenanceDataBase].[dbo].[ItemType] t ON [t].[ItemTypeId] = [i].[ItemTypeId] LEFT OUTER JOIN [VipcoMachineDataBase].[dbo].[Employee] el ON [el].[EmpCode] = [i].[EmpResponsible] LEFT OUTER JOIN [VipcoMachineDataBase].[dbo].[EmployeeGroupMIS] g ON [g].[GroupMIS] = [i].[GroupMis]", WhereCommand = $@" [i].[ItemId] = '{key}'" }; var hasData = await this.dapper.GetFirstEntity <ItemViewModel>(sqlCommand); return(new JsonResult(hasData, this.DefaultJsonSettings)); } return(NoContent()); }
public async Task <IActionResult> GetScrollMk2([FromBody] ScrollViewModel Scroll) { var message = "Data not been found."; try { if (Scroll != null) { string sWhere = ""; string sSort = ""; #region Where // Filter var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" } : Scroll.Filter.Split(null); foreach (string temp in filters) { if (string.IsNullOrEmpty(temp)) { continue; } string keyword = temp.ToLower(); sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $@"(LOWER([im].[ItemCode]) LIKE '%{keyword}%' OR LOWER([im].[Name]) LIKE '%{keyword}%' OR LOWER([ty].[Name]) LIKE '%{keyword}%')"; } // Where Return Type if (Scroll.WhereId2.HasValue) { sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $"[im].[ItemTypeId] = {Scroll.WhereId2}"; } // Where Obsolete Item if (Scroll.WhereId3.HasValue) { sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $"[ob].[ItemId] IS NULL"; } #endregion #region Sort switch (Scroll.SortField) { case "ItemCode": if (Scroll.SortOrder == -1) { sSort = $"[im].[ItemCode] DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Pshnum0); } else { sSort = $"[im].[ItemCode] ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Pshnum0); } break; case "Name": if (Scroll.SortOrder == -1) { sSort = $"[im].[Name] DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Pjth0); } else { sSort = $"[im].[Name] ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Pjth0); } break; case "ItemTypeString": if (Scroll.SortOrder == -1) { sSort = $"[ty].[Name] DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Prqdat0); } else { sSort = $"[ty].[Name] ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Prqdat0); } break; default: sSort = $"[im].[ItemCode] ASC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Prqdat0); break; } #endregion var sqlCommnad = new SqlCommandViewModel() { SelectCommand = $@" [im].[ItemCode] ,[im].[Name] ,[im].[Model] ,[im].[ItemId] ,[im].[Property] ,[im].[Property2] ,[im].[EmpResponsible] ,[im].[GroupMis] ,[im].[RegisterDate] ,[im].[CancelDate] ,[ty].[Name] AS [ItemTypeString] ,[wg].[GroupDesc] AS [GroupMisString]", FromCommand = $@" [dbo].[Item] im LEFT OUTER JOIN [dbo].[ItemType] ty ON [im].[ItemTypeId] = [ty].[ItemTypeId] LEFT OUTER JOIN [VipcoMachineDataBase].[dbo].[EmployeeGroupMIS] wg ON [im].[GroupMis] = [wg].[GroupMIS] LEFT JOIN [dbo].ObsoleteItem ob ON [im].[ItemId] = [ob].[ItemId] AND [ob].[Status] != 6", WhereCommand = sWhere, OrderCommand = sSort }; var result = await this.dapper.GetEntitiesAndTotal(sqlCommnad, new { Skip = Scroll.Skip ?? 0, Take = Scroll.Take ?? 50 }); var dbData = result.Entities; Scroll.TotalRow = result.TotalRow; return(new JsonResult(new ScrollDataViewModel <ItemViewModel>(Scroll, dbData), this.DefaultJsonSettings)); } } catch (Exception ex) { message = $"Has error {ex.ToString()}"; } return(BadRequest(new { message })); }
public async Task <IActionResult> GetPartnerScroll([FromBody] ScrollViewModel Scroll) { var message = "Data not been found."; try { if (Scroll == null) { return(BadRequest()); } string sWhere = ""; string sSort = ""; #region Where // Filter var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" } : Scroll.Filter.Split(null); foreach (string temp in filters) { if (string.IsNullOrEmpty(temp)) { continue; } string keyword = temp.ToLower(); sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $@"(LOWER(PAR.BPRNUM_0) LIKE N'%{keyword}%' OR LOWER(PAR.BPRNAM_0) LIKE N'%{keyword}%')"; } // Where Customer #endregion #region Sort switch (Scroll.SortField) { case "PartnerNo": if (Scroll.SortOrder == -1) { sSort = $"PAR.BPRNUM_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Pshnum0); } else { sSort = $"PAR.BPRNUM_0 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Pshnum0); } break; case "PartnerName": if (Scroll.SortOrder == -1) { sSort = $"PAR.BPRNAM_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Pjth0); } else { sSort = $"PAR.BPRNAM_0 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Pjth0); } break; default: sSort = $"PAR.BPRNUM_0 ASC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Prqdat0); break; } #endregion var sqlCommnad = new SqlCommandViewModel() { SelectCommand = $@" [PAR].[BPRNUM_0] AS [PartnerNo], [PAR].[BPRNAM_0] AS [PartnerName], [PAR].[ROWID] AS [Rowid]", FromCommand = $@" [VIPCO].[BPARTNER] PAR", WhereCommand = sWhere, OrderCommand = sSort }; var result = await this.repositoryPartner.GetEntitiesAndTotal(sqlCommnad, new { Skip = Scroll.Skip ?? 0, Take = Scroll.Take ?? 50 }); var dbData = result.Entities; Scroll.TotalRow = result.TotalRow; return(new JsonResult(new ScrollDataViewModel <PartnerViewModel>(Scroll, dbData), this.DefaultJsonSettings)); } catch (Exception ex) { message = $"Has error {ex.ToString()}"; } return(BadRequest(new { message })); }
public async Task <IActionResult> PurchaseExtendScroll([FromBody] ScrollViewModel Scroll) { var message = "Data not been found."; try { if (Scroll != null) { string sWhere = ""; string sSort = ""; #region Where var rowIds = await this.repository.GetToListAsync(x => x.PrSageHeaderId); if (rowIds != null && rowIds.Any()) { var rowid = string.Join(',', rowIds); sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $"PRH.ROWID NOT IN ({rowid})"; } // Filter var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" } : Scroll.Filter.Split(null); foreach (string temp in filters) { if (string.IsNullOrEmpty(temp)) { continue; } string keyword = temp.ToLower(); sWhere += (string.IsNullOrEmpty(sWhere) ? " " : " AND ") + $@"(LOWER(PRH.PSHNUM_0) LIKE '%{keyword}%')"; } #endregion Where #region Sort //switch(Scroll) //{ // case ScrollViewModel s when s.SortField == "Test": // break; //} switch (Scroll.SortField) { case "PrNumber": if (Scroll.SortOrder == -1) { sSort = $"PRH.PSHNUM_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Pshnum0); } else { sSort = $"PRH.PSHNUM_0 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Pshnum0); } break; case "PrDate": if (Scroll.SortOrder == -1) { sSort = $"PRH.PRQDAT_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Prqdat0); } else { sSort = $"PRH.PRQDAT_0 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Prqdat0); } break; default: sSort = $"PRH.PRQDAT_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Prqdat0); break; } #endregion Sort #region Query var sqlCommnad = new SqlCommandViewModel() { SelectCommand = $@" PRH.ROWID AS [PrSageHeaderId], PRH.PSHNUM_0 AS [PrNumber], PRH.PRQDAT_0 AS [PrDate], (CASE WHEN PRH.ZPR30_0 = 1 THEN 'Purchase' WHEN PRH.ZPR30_0 = 2 THEN 'Hire' ELSE '-' END) AS [PrType], (CASE WHEN PRH.CLEFLG_0 = 1 THEN 'No' WHEN PRH.CLEFLG_0 = 2 THEN 'Yes' ELSE '-' END) AS [StatusClose], (CASE WHEN PRH.ORDFLG_0 = 1 THEN 'No' WHEN PRH.ORDFLG_0 = 2 THEN 'Yes' ELSE '-' END) AS [StatusOrder]", FromCommand = $@" [VIPCO].[PREQUIS] PRH ", WhereCommand = sWhere, OrderCommand = sSort }; #endregion var result = await this.repositoryPrPure.GetEntitiesAndTotal(sqlCommnad, new { Skip = Scroll.Skip ?? 0, Take = Scroll.Take ?? 50 }); var dbData = result.Entities; Scroll.TotalRow = result.TotalRow; return(new JsonResult( new ScrollDataViewModel <PurchaseRequestPureViewModel>(Scroll, dbData), this.DefaultJsonSettings)); } } catch (Exception ex) { message = $"Has error {ex.ToString()}"; } return(BadRequest(new { message })); }