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 }));
}
