public dtoResult UpdateStatus(int id,int status)
{
var result = new dtoResult();
try
{
var user = DBContext.tbl_users.FirstOrDefault(d => d.userId == id);
if (user != null)
{
user.status = status;
}
DBContext.SaveChanges();
//t.userName = obj.userName;
result.isSuccessful = true;
result.returnObj = id;
}
catch (Exception ex)
{
result.isSuccessful = false;
result.errorMsg = ex.ToString();
}
return result;
}
public dtoResult Insert(dtoUserAccount t)
{
string hash = "";
using (MD5 md5Hash = MD5.Create())
{
hash= GetMd5Hash(md5Hash, t.passWord);
}
var result = new dtoResult();
try
{
var obj = new tbl_users
{
userName = t.userName,
passWord = hash,
firstName = t.firstName,
lastName = t.lastName,
middleName = t.middleName,
branchId = t.branchId,
emailAddress = t.emailAddress,
status = 1,
dateCreated = DateTime.Now
};
DBContext.tbl_users.Add(obj);
DBContext.SaveChanges();
t.userName = obj.userName;
result.isSuccessful = true;
result.returnObj = t;
}
catch (Exception ex)
{
result.isSuccessful = false;
result.errorMsg = ex.ToString();
}
return result;
}
public dtoResult Update(dtoUserAccount t)
{
string hash = "";
var result = new dtoResult();
try
{
using (MD5 md5Hash = MD5.Create())
{
hash = GetMd5Hash(md5Hash, t.passWord);
}
var user = DBContext.tbl_users.FirstOrDefault(d => d.userId == t.userId);
if (user != null)
{
user.userName = t.userName;
user.firstName = t.firstName;
user.middleName = t.userName;
user.lastName = t.lastName;
user.passWord = hash;
user.branchId = t.branchId;
}
DBContext.SaveChanges();
//t.userName = obj.userName;
result.isSuccessful = true;
result.returnObj = t;
}
catch (Exception ex)
{
result.isSuccessful = false;
result.errorMsg = ex.ToString();
}
return result;
}
public dtoResult Update(dtoPayment t)
{
dtoResult objResult = new dtoResult();
try
{
var item = DBContext.tbl_payment.FirstOrDefault(d => d.paymentId == t.paymentId);
item.supplierId = t.supplierId;
item.referenceNumber = t.referenceNumber;
item.paymentDate = t.paymentDate;
item.TypeOfPayment = t.TypeOfPayment;
item.ModeOfPayment = t.ModeOfPayment;
item.cashAmount = t.cashAmount;
item.chequeAmount = t.chequeAmount;
item.chequeNumber = t.chequeNumber;
item.chequeDate = t.chequeDate;
item.chequeBank = t.chequeBank;
item.totalPayment = t.totalPayment;
DBContext.SaveChanges();
}
catch (Exception ex)
{
objResult.isSuccessful = false;
objResult.errorMsg = ex.Message;
}
return objResult;
}
public dtoResult SavePurchaseTransaction(dtoDocument document, List<dtoTransaction> products)
{
var result = new dtoResult();
try
{
int docType = 0;
switch (document.documentNumber.Substring(0, 2))
{
//purchase order
case "PO":
docType = 2;
break;
//purchase return
case "RT":
docType = 6;
break;
//Delivery Receipt - still sales invoice instead of 7.
case "DR":
docType = 1;
break;
//Delivery Receipt - still sales invoice instead of 8.
case "OS":
docType = 1;
break;
}
var incompletePrice = products
.Where(_=>_.unitPrice ==0)
.Select(_ => _.transactionId).ToList();
document.payment = incompletePrice.Count > 0 ? 0 : 1;
document.documentType = docType;
if (document.documentId == 0)
{
AddDocument(ref document);
}
else
{
EditDocument(document);
}
foreach (
var item in
DBContext.tbl_transaction.ToList()
.Where( d => d.documentId == document.documentId ))
{
var product = DBContext.tbl_product.FirstOrDefault(d => d.productId == item.productId);
if (product != null)
{
//product.unitPrice = item.unitPrice;
//product.incoming = product.incoming - item.quantity;
//product.ending = (product.beginning + product.incoming) - product.outgoing;
switch (document.documentType)
{
case 8:
case 7:
case 2:
//product.unitPrice = item.unitPrice;
product.incoming = product.incoming - item.quantity;
product.ending = (product.beginning + product.incoming) - product.outgoing;
break;
case 6:
//If return do not change price
//product.unitPrice = item.unitPrice;
product.outgoing = product.outgoing - item.quantity;
product.ending = (product.beginning + product.incoming) - product.outgoing;
break;
}
}
DBContext.tbl_transaction.Remove(item);
}
if (products != null)
foreach (var item in products)
{
//if (item.transactionId != 0) continue;
item.documentId = document.documentId;
switch (document.documentType)
{
case 8:
case 7:
case 2:
item.transactionType = 1;
break;
case 6:
item.transactionType = 6;
break;
}
AddTransaction(item, document.createdBy);
}
DBContext.SaveChanges();
result.isSuccessful = true;
//result.returnObj = t;
}
catch (Exception ex)
{
result.isSuccessful = false;
result.errorMsg = ex.ToString();
}
return result;
}
public dtoResult Insert(dtoProduct t)
{
dtoResult objResult = new dtoResult();
List<object> parameterList = new List<object>();
object[] parameters1 = null;
try
{
string sQuery = string.Format(@"INSERT INTO tbl_product(categoryId
, productCode
, productDescription
, UOM
, unitPrice
, reorderLevel
, branchId
, createdBy
, dateCreated
, beginning
, incoming
, outgoing
, ending)
VALUES(@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, GETDATE(), @P8, @P9, @P10, @P11)");
parameterList.Add(t.categoryId);
parameterList.Add(t.productCode);
parameterList.Add(t.productDescription);
parameterList.Add(t.UOM);
parameterList.Add(t.unitPrice);
parameterList.Add(t.reorderLevel);
parameterList.Add(t.branchId);
parameterList.Add(t.createdBy);
parameterList.Add(t.beginning);
parameterList.Add(t.incoming);
parameterList.Add(t.outgoing);
parameterList.Add(t.beginning + t.incoming - t.outgoing);
parameters1 = parameterList.ToArray();
DBContext.Database.ExecuteSqlCommand(sQuery, parameters1);
objResult.isSuccessful = true;
}
catch (Exception ex)
{
objResult.isSuccessful = false;
objResult.errorMsg = ex.Message;
}
parameterList = null;
parameters1 = null;
return objResult;
}
public dtoResult SavePaymentTransaction(dtoPayment Header, List<dtoPaymentDetail> Details)
{
int? originalPaymentId = Header.paymentId;
var respose = new dtoResult { isSuccessful = true };
try
{
if (Header.paymentId == null || Header.paymentId == 0)
{
Insert(ref Header);
}
else
{
//Update Header
Update(Header);
//Delete Removed details.
var updatedIds = Details.Where(i => i.paymentDetailsId != null).Select(i => i.paymentDetailsId).ToList();
var deletedDetails = DBContext.tbl_paymentDetails.Where(l => l.paymentId == originalPaymentId && !updatedIds.Contains(l.paymentDetailsId));
foreach (var del in deletedDetails)
{
DBContext.tbl_paymentDetails.Remove(del);
}
}
foreach (var item in Details)
{
if (originalPaymentId == null || originalPaymentId == 0)
{
var objDT = new tbl_paymentDetails
{
paymentId = Header.paymentId,
documentId = item.documentId,
paymentPrice = item.paymentPrice,
createdBy = 1,
dateCreated = DateTime.Now
};
DBContext.tbl_paymentDetails.Add(objDT);
DBContext.SaveChanges();
}
else
{
if (item.paymentDetailsId != null || item.paymentDetailsId > 0)
{
var objDT = DBContext.tbl_paymentDetails.FirstOrDefault(d => d.paymentDetailsId == item.paymentDetailsId);
objDT.paymentId = Header.paymentId;
objDT.documentId = item.documentId;
objDT.paymentPrice = item.paymentPrice;
objDT.lastModifiedBy = 1;
objDT.dateLastModified = DateTime.Now;
DBContext.SaveChanges();
}
else
{
var objDT = new tbl_paymentDetails
{
paymentId = Header.paymentId,
documentId = item.documentId,
paymentPrice = item.paymentPrice,
createdBy = 1,
dateCreated = DateTime.Now
};
DBContext.tbl_paymentDetails.Add(objDT);
DBContext.SaveChanges();
}
}
}
DBContext.SaveChanges();
}
catch (Exception ex)
{
respose.isSuccessful = false;
respose.errorMsg = ex.Message;
}
return respose;
}
public dtoResult Insert(dtoSupplier t)
{
dtoResult objResult = new dtoResult();
List<object> parameterList = new List<object>();
object[] parameters1 = null;
try
{
string sQuery = string.Format(@"INSERT INTO tbl_supplier(supplierCode,supplierName,supplierAddress,supplierContactNumber,supplierContactPerson,[status],createdBy,dateCreated,branchId)
VALUES(@P0,@P1,@P2,@P3,@P4,@P5,@P6,GETDATE(),@P7)");
parameterList.Add(t.supplierCode);
parameterList.Add(t.supplierName);
parameterList.Add(t.supplierAddress);
parameterList.Add(t.supplierContactNumber);
parameterList.Add(t.supplierContactPerson);
parameterList.Add(t.status);
parameterList.Add(t.createdBy);
parameterList.Add(t.branchId);
parameters1 = parameterList.ToArray();
DBContext.Database.ExecuteSqlCommand(sQuery, parameters1);
objResult.isSuccessful = true;
}
catch (Exception ex)
{
objResult.isSuccessful = false;
objResult.errorMsg = ex.Message;
}
parameterList = null;
parameters1 = null;
return objResult;
}
public dtoResult Update(dtoSupplier t)
{
dtoResult objResult = new dtoResult();
List<object> parameterList = new List<object>();
object[] parameters1 = null;
try
{
string sQuery = string.Format(@"UPDATE tbl_supplier SET supplierCode = @P0,supplierName = @P1,supplierAddress=@P2,supplierContactNumber=@P3,supplierContactPerson=@P4,[status]=@P5,modifiedBy=@P6,dateLastModified=GETDATE()
WHERE supplierId=@P7");
parameterList.Add(t.supplierCode);
parameterList.Add(t.supplierName);
parameterList.Add(t.supplierAddress);
parameterList.Add(t.supplierContactNumber);
parameterList.Add(t.supplierContactPerson);
parameterList.Add(t.status);
parameterList.Add(t.modifiedBy);
parameterList.Add(t.supplierId);
parameters1 = parameterList.ToArray();
DBContext.Database.ExecuteSqlCommand(sQuery, parameters1);
objResult.isSuccessful = true;
}
catch (Exception ex)
{
objResult.isSuccessful = false;
objResult.errorMsg = ex.Message;
}
parameterList = null;
parameters1 = null;
return objResult;
}
public dtoResult Update(dtoClient t)
{
var result = new dtoResult();
result.isSuccessful = true;
try
{
var entity = DBContext.tbl_client.FirstOrDefault(d => d.clientId == t.clientId);
entity.clientCode = t.clientCode;
entity.clientName = t.clientName;
entity.clientAddress = t.clientAddress;
entity.clientContactNumber = t.clientContactNumber;
entity.clientContactPerson = t.clientContactPerson;
entity.status = t.status;
entity.modifiedBy = t.modifiedBy;
entity.dateLastModified = t.dateLastModified;
DBContext.SaveChanges();
}
catch (Exception ex)
{
result.isSuccessful = false;
result.errorMsg = ex.ToString();
}
return result;
}
public dtoResult Save(dtoSupplier objSupplier)
{
dtoResult objResult = new dtoResult();
dtoSupplier objValidation;
try
{
if (objSupplier == null)
throw new Exception("Please provide valid Supplier details.");
objSupplier.supplierCode = ("" + objSupplier.supplierCode).Trim();
objSupplier.supplierName = ("" + objSupplier.supplierName).Trim();
if (objSupplier.supplierCode == string.Empty)
throw new Exception("Please provide valid Supplier Code.");
if (objSupplier.supplierName == string.Empty)
throw new Exception("Please provide valid Supplier Name.");
if (objSupplier.supplierId == 0)
{
if (objSupplier.createdBy < 1)
throw new Exception("Please provide valid User ID.");
objValidation = repo.GetByCode(objSupplier.supplierCode);
if (objValidation != null)
throw new Exception("Cannot be saved because Supplier Code already exists.");
objValidation = null;
objValidation = repo.GetByName(objSupplier.supplierName);
if (objValidation != null)
throw new Exception("Cannot be saved because Suppier Name already exists.");
objResult = repo.Insert(objSupplier);
}
else
{
if (objSupplier.modifiedBy < 1)
throw new Exception("Please provide valid User ID.");
objResult = repo.Update(objSupplier);
}
}
catch (Exception ex)
{
objResult.isSuccessful = false;
objResult.errorMsg = ex.Message;
}
objValidation = null;
return objResult;
}
public dtoResult Insert(dtoClient t)
{
var result = new dtoResult();
result.isSuccessful = true;
try
{
var entiry = new tbl_client {
clientCode = t.clientCode,
clientName = t.clientName,
clientAddress = t.clientAddress,
clientContactNumber = t.clientContactNumber,
clientContactPerson = t.clientContactPerson,
createdBy = t.createdBy,
dateCreated = t.dateCreated,
status = t.status,
branchId = t.branchId
};
DBContext.tbl_client.Add(entiry);
DBContext.SaveChanges();
t.clientId = entiry.clientId;
result.returnObj = t;
}
catch (Exception ex)
{
result.isSuccessful = false;
result.errorMsg = ex.ToString();
}
return result;
}
public dtoResult Save(dtoProduct objProduct)
{
dtoResult objResult = new dtoResult();
try
{
if (objProduct == null)
throw new Exception("Please provide valid Product details.");
objProduct.productCode = ("" + objProduct.productCode).Trim();
if (objProduct.productCode == string.Empty)
throw new Exception("Please provide valid Product Code.");
if (objProduct.productId == 0)
{
if (objProduct.createdBy < 1)
throw new Exception("Please provide valid User ID.");
objResult = repo.Insert(objProduct);
}
else
{
if (objProduct.modifiedBy < 1)
throw new Exception("Please provide valid User ID.");
objResult = repo.Update(objProduct);
}
}
catch (Exception ex)
{
objResult.isSuccessful = false;
objResult.errorMsg = ex.Message;
}
return objResult;
}
public dtoResult Update(dtoProduct t)
{
dtoResult objResult = new dtoResult();
List<object> parameterList = new List<object>();
object[] parameters1 = null;
try
{
string sQuery = string.Format(@"UPDATE tbl_product SET categoryId = @P0
, productCode = @P1
, productDescription=@P2
, UOM=@P3
, unitPrice=@P4
, reorderLevel=@P5
, modifiedBy=@P6
, dateLastModified=GETDATE()
, beginning=@P8
, incoming=@P9
, outgoing=@P10
, ending =@P11
WHERE productId=@P7");
parameterList.Add(t.categoryId);
parameterList.Add(t.productCode);
parameterList.Add(t.productDescription);
parameterList.Add(t.UOM);
parameterList.Add(t.unitPrice);
parameterList.Add(t.reorderLevel);
parameterList.Add(t.modifiedBy);
parameterList.Add(t.productId);
parameterList.Add(t.beginning);
parameterList.Add(t.incoming);
parameterList.Add(t.outgoing);
parameterList.Add(t.beginning + t.incoming - t.outgoing);
parameters1 = parameterList.ToArray();
DBContext.Database.ExecuteSqlCommand(sQuery, parameters1);
objResult.isSuccessful = true;
}
catch (Exception ex)
{
objResult.isSuccessful = false;
objResult.errorMsg = ex.Message;
}
parameterList = null;
parameters1 = null;
return objResult;
}
public dtoResult SaveInvoiceTransaction(dtoDocument document, List<dtoTransaction> products)
{
var result = new dtoResult();
try
{
int docType = 0;
switch (document.documentNumber.Substring(0, 2))
{
//sales invoice
case "SI":
docType = 1;
break;
//sales refund
case "RF":
docType = 5;
break;
//Delivery Receipt - still sales invoice instead of 7.
case "DR":
docType = 1;
break;
//Delivery Receipt - still sales invoice instead of 8.
case "OS":
docType = 1;
break;
}
document.documentType = docType;
if (document.documentId == 0)
{
AddDocument(ref document);
}
else
{
EditDocument(document);
}
foreach (
var item in
DBContext.tbl_transaction.ToList()
.Where( d => d.documentId == document.documentId))
{
var product = DBContext.tbl_product.FirstOrDefault(d => d.productId == item.productId);
if (product != null)
{
switch (document.documentType)
{
case 8:
case 7:
case 1:
product.outgoing = product.outgoing - item.quantity;
product.ending = (product.beginning + product.incoming) - product.outgoing;
break;
case 5:
product.incoming = product.incoming - item.quantity;
product.ending = (product.beginning + product.incoming) - product.outgoing;
break;
}
}
DBContext.tbl_transaction.Remove(item);
}
if(products != null)
foreach (var item in products)
{
//if (item.transactionId == 0)
//{
item.documentId = document.documentId;
switch (document.documentType)
{
case 8:
case 7:
case 1:
item.transactionType = 3;
break;
case 5:
item.transactionType = 7;
break;
}
AddTransaction(item, document.createdBy);
//}
}
DBContext.SaveChanges();
result.isSuccessful = true;
//result.returnObj = t;
}
catch(Exception ex)
{
result.isSuccessful = false;
result.errorMsg = ex.ToString();
}
return result;
}
public dtoResult Insert(ref dtoPayment t)
{
dtoResult objResult = new dtoResult();
try
{
var objpayment = new tbl_payment
{
supplierId = t.supplierId,
referenceNumber = t.referenceNumber,
paymentDate = DateTime.Now,
cashAmount = t.cashAmount,
chequeAmount = t.chequeAmount,
chequeNumber = t.chequeNumber,
TypeOfPayment = t.TypeOfPayment,
ModeOfPayment = t.ModeOfPayment,
chequeDate = t.chequeDate,
chequeBank = t.chequeBank,
totalPayment = t.totalPayment,
dateCreated = DateTime.Now,
createdBy = t.createdBy
};
DBContext.tbl_payment.Add(objpayment);
DBContext.SaveChanges();
t.paymentId = objpayment.paymentId;
objResult.isSuccessful = true;
}
catch (Exception ex)
{
objResult.isSuccessful = false;
objResult.errorMsg = ex.Message;
}
return objResult;
}
public dtoResult SaveReceivableTransaction(dtoReceivable header, List<dtoReceivableDetail> details)
{
var result = new dtoResult();
try
{
if (header.isNew)
{
AddReceivable(ref header);
}
if (details != null)
foreach (var item in details)
{
item.receivableId = header.receivableId;
item.paymentPrice = item.totalPayAmount;
item.createdBy = header.createdBy;
item.dateCreated = header.dateCreated;
AddReceivableDetail(item);
}
DBContext.SaveChanges();
result.isSuccessful = true;
}
catch (Exception ex)
{
result.isSuccessful = false;
result.errorMsg = ex.ToString();
}
return result;
}
public dtoResult InLineUpdate(dtoProduct t)
{
dtoResult objResult = new dtoResult();
List<object> parameterList = new List<object>();
object[] parameters1 = null;
try
{
string sQuery = string.Format(@"UPDATE tbl_product SET
modifiedBy=@P0
, dateLastModified=GETDATE()
, beginning=@P1
, incoming=@P2
, outgoing=@P3
, ending =@P4
WHERE productId=@P5");
parameterList.Add(t.modifiedBy);
parameterList.Add(t.beginning);
parameterList.Add(t.incoming);
parameterList.Add(t.outgoing);
parameterList.Add(t.beginning + t.incoming - t.outgoing);
parameterList.Add(t.productId);
parameters1 = parameterList.ToArray();
DBContext.Database.ExecuteSqlCommand(sQuery, parameters1);
objResult.isSuccessful = true;
}
catch (Exception ex)
{
objResult.isSuccessful = false;
objResult.errorMsg = ex.Message;
}
return objResult;
}