public string AddTransactionQuery(int employeeID, TransactionClass cls, ref string recordID)
{
var column = "";
var updateRecord = "";
switch (cls)
{
case TransactionClass.Order:
column = "OrderID"; recordID = String.IsNullOrEmpty(recordID) ? OrderID.DbValue() : recordID;
updateRecord = String.Format("update [Order] set Paid = (select isnull(sum(Amount), 0) from Transactions where OrderID = {0}) where ID = {0}", recordID);
break;
case TransactionClass.Repair: column = "RepairID"; recordID = String.IsNullOrEmpty(recordID) ? RepairID.DbValue() : recordID; break;
case TransactionClass.Warranty: column = "WarrantyID"; recordID = String.IsNullOrEmpty(recordID) ? WarrantyID.DbValue() : recordID; break;
default: break;
}
var result = String.Format(
@" insert into Transactions(EmployeeID, {0}, Method, Amount, SubmitDate, TransferDate)
values ({1}, {2}, N'{3}', {4}, {5}, {6}) {7}",
column, employeeID, recordID, Method, Amount, SubmitDate.DbValue(), TransferDate.DbValue(), updateRecord);
return(result);
}
public ActionResult UpDate([FromBody] SubmitDate date)
{
DBCode code = _access.CheckExercisePermission(date.ExerciseID, Role.Teacher);
if (code != DBCode.OK)
{
return(HandleDatabaseOutput(code));
}
return(HandleDatabaseOutput(_access.UpdateDate(date)));
}
public EmployeeFineList Save(int userID, int employeeID, int bussinessID, string action, string code = "Name")
{
QueryOutput queryOutput;
var query = String.Format(
@"insert into EmployeeFine(EmployeeID, SubmitDate, Amount, Reason)
values ({0}, {1}, {2}, N'{3}') {4}",
EmployeeID, SubmitDate.DbValue(), Amount, Reason, Query(bussinessID, new EmployeeFineFilter(EmployeeID)));
return(new EmployeeFineList(EmployeeID, Query <EmployeeFine>(new DbQuery(userID, employeeID, action, query, true, EmployeeID, code), out queryOutput)));
}
public ActionResult <int> AddDate(string exerciseId, [FromBody] SubmitDate date)
{
DBCode code = _access.CheckExercisePermission(exerciseId, Role.Teacher);
if (code != DBCode.OK)
{
return(HandleDatabaseOutput(code));
}
date.ExerciseID = exerciseId;
return(HandleDatabaseOutput(_access.AddDate(date)));
}
public Warranty Save(ModelStateDictionary modelState, int userID, int employeeID, int bussinessID, string employeeName = null)
{
QueryOutput queryResult;
if (!Validate(modelState))
{
Result = false;
return(null);
}
var query = "";
var action = "";
var id = ID.ToString();
if (ID > 0)
{
query = String.Format(
@"update Warranty
set TransferDate = {1}, ReceivedDate = {2}, ProcessedDate = {3}, ReturnedDate = {4}, FinishDate = {5}, Service = N'{13}',
ProductState = N'{6}', Fee = {7}, Discount = {8}, Other = N'{9}', Note = N'{10}', ContactName = N'{11}', ContactPhone = N'{12}'
where ID = {0}",
new object[] {
ID, TransferDate.DbValue(), ReceivedDate.DbValue(), ProcessedDate.DbValue(), ReturnedDate.DbValue(),
FinishDate.DbValue(), ProductState, Fee, Discount, Other, Note, ContactName, ContactPhone, Service
});
action = DbAction.Warranty.Modify;
}
else
{
Code = NewUniqueCode(userID, employeeID, bussinessID, "Warranty");
EmployeeID = employeeID;
BussinessID = bussinessID;
EmployeeName = employeeName;
SubmitDate = DateTime.Now;
var clientID = "@ClientID";
query = String.Format("declare {0} int = {1}", clientID, ClientID.DbValue());
if ((!ClientID.HasValue || ClientID <= 0) && !String.IsNullOrEmpty(ClientName))
{
query += String.Format(
@" declare @client table (ID int)
insert Client(BussinessID, Code, Name, Phone, Address, Point, Status)
output inserted.ID into @client
values ({0}, '{1}', N'{2}', '{3}', N'{4}', 0, 'active')
set {5} = (select top 1 ID from @client)",
bussinessID, String.IsNullOrEmpty(ClientCode) ? NewUniqueCode(userID, employeeID, bussinessID, "Client", 3, null) : ClientCode, ClientName, ClientPhone, ClientAddress, clientID);
}
query += String.Format(
@" declare @output table (ID int)
insert Warranty(BussinessID, EmployeeID, WarehouseID, ProductID, Code, SubmitDate, TransferDate, ReceivedDate, ProcessedDate, ReturnedDate, FinishDate,
ProductState, Fee, Discount, Other, Note, Status, ClientID, OrderID, OrderCode, ContactName, ContactPhone, ReceiveWarehouseID, Service)
output inserted.ID into @output
values ({0}, {1}, {2}, {3}, N'{4}', {5}, {6}, {7}, {8}, {9}, {10}, N'{11}', {12}, {13}, N'{14}', N'{15}', 'active', {16}, {17}, '{18}', N'{19}', N'{20}', {21}, N'{22}')",
new object[] {
BussinessID, EmployeeID, WarehouseID, ProductID, Code, SubmitDate.DbValue(), TransferDate.DbValue(), ReceivedDate.DbValue(),
ProcessedDate.DbValue(), ReturnedDate.DbValue(), FinishDate.DbValue(), ProductState, Fee, Discount, Other, Note,
clientID, OrderID.DbValue(), OrderCode, ContactName, ContactPhone, ReceiveWarehouseID, Service
});
id = "(select top 1 ID from @output)";
if (Transactions != null)
{
foreach (var tran in Transactions)
{
query += tran.AddTransactionQuery(employeeID, TransactionClass.Warranty, ref id);
}
}
action = DbAction.Warranty.Create;
}
query += String.Format(
@" select top 1 w.ID, w.BussinessID, w.EmployeeID, w.WarehouseID, w.ProductID, w.ClientID, w.OrderID, w.Code, w.Service,
w.SubmitDate, w.TransferDate, w.ReceivedDate, w.ProcessedDate, w.ReturnedDate, w.FinishDate, w.ProductState, w.Fee, w.Discount, w.Other, w.Note,
rwh.Name as [ReceiveWarehouseName], wh.Name as [WarehouseName], w.ContactName, w.ContactPhone, e.Name as [EmployeeName],
c.Name as [ClientName], c.Code as [ClientCode], c.Address as [ClientAddress], c.Phone as [ClientPhone],
case when w.OrderID is not null then o.Code else w.OrderCode end as [OrderCode], isnull(sum(t.Amount), 0) as [Paid]
from Warranty w
join Warehouse wh on w.WarehouseID = wh.ID and w.Status = 'active' and ((select Username from Login where ID = {1}) = 'admin' or wh.ID in (select WarehouseID from LoginWarehouse where LoginID = {1}))
join Warehouse rwh on w.ReceiveWarehouseID = rwh.ID
join Employee e on w.EmployeeID = e.ID
join Product p on p.ID = w.ProductID
left join Client c on w.ClientID = c.ID
left join [Order] o on w.OrderID = o.ID
left join Transactions t on w.ID = t.WarrantyID
where w.ID = {0} and w.Status = 'active'
group by w.ID, w.BussinessID, w.EmployeeID, w.WarehouseID, w.ProductID, w.ClientID, w.OrderID, w.Code, w.ContactName, w.ContactPhone,
w.SubmitDate, w.TransferDate, w.ReceivedDate, w.ProcessedDate, w.ReturnedDate, w.FinishDate, w.ProductState, w.Service,
w.Fee, w.Discount, w.Other, w.Note, wh.Name, rwh.Name, e.Name, c.Name, c.Code, c.Address, c.Phone, o.Code, w.OrderCode" , id, userID);
var record = Query <Warranty>(new DbQuery(userID, employeeID, action, query, true, id), out queryResult).FirstOrDefault();
if (Result = (queryResult == QueryOutput.Success))
{
Messages = new List <string>()
{
"Lưu thông tin thành công"
}
}
;
return(record);
}
