public string InsertConsumption(Consumption objConsumption)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
var internalId = DatabaseCommonRepository.GetNewDocNo(connection, objConsumption.OrganizationId, 23, true, txn);
objConsumption.ConsumptionNo = internalId;
objConsumption.TotalAmount = objConsumption.ConsumptionItems.Sum(m => m.Amount);
string sql = @"insert into Consumption(ConsumptionNo,ConsumptionDate,JobCardId,TotalAmount,SpecialRemarks,CreatedBy,CreatedDate,OrganizationId) Values (@ConsumptionNo,@ConsumptionDate,@JobCardId,@TotalAmount,@SpecialRemarks,@CreatedBy,@CreatedDate,@OrganizationId);
SELECT CAST(SCOPE_IDENTITY() as int)";
var id = connection.Query <int>(sql, objConsumption, txn).Single();
foreach (ConsumptionItem item in objConsumption.ConsumptionItems)
{
item.ConsumptionId = id;
new ConsumptionItemRepository().InsertConsumptionItem(item, connection, txn);
}
InsertLoginHistory(dataConnection, objConsumption.CreatedBy, "Create", "Consumption", id.ToString(), "0");
txn.Commit();
return(id + "|CON/" + internalId);
}
catch (Exception)
{
txn.Rollback();
return("0");
}
}
}
public int InsertJobCardQC(JobCardQC objJobCardQC)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction trn = connection.BeginTransaction();
try
{
int id = 0;
string internalId = DatabaseCommonRepository.GetNewDocNo(connection, objJobCardQC.OrganizationId, 17, true, trn);
objJobCardQC.JobCardQCRefNo = internalId;
string sql = @"INSERT INTO JobCardQC(JobCardId,JobCardQCRefNo,EmployeeId,JobCardQCDate,IsQCPassed,CreatedBy,CreatedDate,OrganizationId,Remarks, PunchingNo)
VALUES (@JobCardId,@JobCardQCRefNo,@EmployeeId,GETDATE(),@IsQCPassed,@CreatedBy,GETDATE(),@OrganizationId,@Remarks, @PunchingNo);
SELECT CAST(SCOPE_IDENTITY() as int)";
id = connection.Query <int>(sql, objJobCardQC, trn).Single();
var JobCardQCParamRepo = new JobCardQCParamRepository();
foreach (var item in objJobCardQC.JobCardQCParams)
{
item.JobCardQCId = id;
JobCardQCParamRepo.InsertSaleOrderItem(item, connection, trn);
}
InsertLoginHistory(dataConnection, objJobCardQC.CreatedBy, "Create", "Job Card QC", id.ToString(), "0");
trn.Commit();
return(id);
}
catch (Exception)
{
trn.Rollback();
return(0);
}
}
}
public string InsertQuerySheet(QuerySheet objQuerySheet)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
var internalId = DatabaseCommonRepository.GetNewDocNo(connection, objQuerySheet.OrganizationId, 5, true, txn);
objQuerySheet.QuerySheetRefNo = internalId;
string sql = @"insert into QuerySheet(QuerySheetRefNo,QuerySheetDate,ProjectName,ContactPerson,ContactNumber,Email,Type,CreatedBy,CreatedDate,OrganizationId)
Values (@QuerySheetRefNo,@QuerySheetDate,@ProjectName,@ContactPerson,@ContactNumber,@Email,@Type,@CreatedBy,@CreatedDate,@OrganizationId);
SELECT CAST(SCOPE_IDENTITY() as int)";
var id = connection.Query <int>(sql, objQuerySheet, txn).Single();
foreach (QuerySheetItem item in objQuerySheet.QuerySheetItems)
{
item.QuerySheetId = id;
new ProjectCostRepository().InsertQuerySheetItem(item, connection, txn);
}
InsertLoginHistory(dataConnection, objQuerySheet.CreatedBy, "Create", "Query Sheet", id.ToString(), "0");
txn.Commit();
return(id + "|" + internalId);
}
catch (Exception)
{
txn.Rollback();
return("0");
}
}
}
public string InsertPurchaseBill(PurchaseBill objPurchaseBill)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction trn = connection.BeginTransaction();
try
{
var internalId = DatabaseCommonRepository.GetNewDocNo(connection, objPurchaseBill.OrganizationId, 32, true, trn);
objPurchaseBill.PurchaseBillRefNo = internalId;
var id = _InsertPurchaseBill(objPurchaseBill, connection, trn);
InsertLoginHistory(dataConnection, objPurchaseBill.CreatedBy, "Create", "Purchase Bill", id.ToString(), "0");
trn.Commit();
return id + "|" + internalId;
}
catch (Exception)
{
trn.Rollback();
return "0";
}
}
}
public string Insert(ExpenseBill expenseBill)
{
int id = 0;
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction trn = connection.BeginTransaction();
try
{
var internalId = DatabaseCommonRepository.GetNewDocNo(connection, expenseBill.OrganizationId, 14, true, trn);
expenseBill.ExpenseNo = internalId;
if (expenseBill.SoOrJc == "JC")
{
expenseBill.SaleOrderId = null;
}
else
{
expenseBill.JobCardId = null;
}
string sql = string.Empty;
sql += "insert into ExpenseBill(ExpenseNo,ExpenseDate,ExpenseBillRef,ExpenseBillDate,ExpenseBillDueDate,SupplierId,ExpenseRemarks,TotalAddition,TotalDeduction,TotalAmount,CurrencyId,SaleOrderId,JobCardId,OrganizationId)";
sql += " values(@ExpenseNo,@ExpenseDate,@ExpenseBillRef,@ExpenseBillDate,@ExpenseBillDueDate,@SupplierId,@ExpenseRemarks,@TotalAddition,@TotalDeduction,@TotalAmount,@CurrencyId,@SaleOrderId,@JobCardId,@OrganizationId);";
sql += " SELECT CAST(SCOPE_IDENTITY() as int);";
id = connection.Query <int>(sql, expenseBill, trn).Single();
foreach (var item in expenseBill.ExpenseBillItem)
{
sql = string.Empty;
sql += "insert into ExpenseBillItem(ExpenseId, AddDedId, ExpenseItemRate, ExpenseItemQty, ExpenseItemAmount, ExpenseItemAddDed)";
sql += " values(@ExpenseId,ISNULL(@AddDedId,0), @ExpenseItemRate, @ExpenseItemQty, @ExpenseItemAmount, @ExpenseItemAddDed)";
item.ExpenseId = id;
item.ExpenseItemAddDed = 1;
connection.Query(sql, item, trn);
}
foreach (var item in expenseBill.deductions)
{
sql = string.Empty;
sql += "insert into ExpenseBillItem(ExpenseId, AddDedId, ExpenseItemRate, ExpenseItemQty, ExpenseItemAmount, ExpenseItemAddDed)";
sql += " values(@ExpenseId, ISNULL(@AddDedId,0), @ExpenseItemRate, @ExpenseItemQty, @ExpenseItemAmount, @ExpenseItemAddDed)";
item.ExpenseId = id;
item.ExpenseItemAddDed = 2;
connection.Query(sql, item, trn);
}
InsertLoginHistory(dataConnection, expenseBill.CreatedBy, "Create", "Expense Bill", id.ToString(), "0");
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
}
}
return(expenseBill.ExpenseNo);
}
public string InsertPurchaseIndent(DirectPurchaseRequest model)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
model.PurchaseRequestNo = DatabaseCommonRepository.GetNewDocNo(connection, model.OrganizationId, 36, true, txn);
string sql = @"insert into PurchaseRequest
(PurchaseRequestNo,PurchaseRequestDate,SpecialRemarks,RequiredDate,CreatedBy,CreatedDate,OrganizationId) Values (@PurchaseRequestNo,@PurchaseRequestDate,@SpecialRemarks,@RequiredDate,@CreatedBy,@CreatedDate,@OrganizationId);
SELECT CAST(SCOPE_IDENTITY() as int)";
var id = connection.Query <int>(sql, model, txn).FirstOrDefault();
foreach (DirectPurchaseRequestItem item in model.items)
{
item.DirectPurchaseRequestId = id;
if (item.Quantity == null || item.Quantity == 0)
{
continue;
}
new DirectPurchaseItemRepository().InsertPurchaseIndentItem(item, connection, txn);
}
InsertLoginHistory(dataConnection, model.CreatedBy, "Create", "Purchase Request", id.ToString(), "0");
txn.Commit();
return(model.PurchaseRequestNo);
}
catch (Exception)
{
txn.Rollback();
throw;
}
}
}
/// <summary>
/// Insert PurchaseRequest
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public string InsertPurchaseRequest(PurchaseRequest objPurchaseRequest)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction trn = connection.BeginTransaction();
try
{
var internalId = DatabaseCommonRepository.GetNewDocNo(connection, objPurchaseRequest.OrganizationId, 8, true, trn);
objPurchaseRequest.PurchaseRequestNo = internalId;
var id = Insert(objPurchaseRequest, connection, trn);
InsertLoginHistory(dataConnection, objPurchaseRequest.CreatedBy, "Create", "Purchase Request", id.ToString(), "0");
trn.Commit();
return(id + "|" + internalId);
}
catch (Exception)
{
trn.Rollback();
return("0");
}
}
}
public int InsertJobCardDailyActivity(JobCardDailyActivity objJobCardDailyActivity)
{
JobCardDailyActivityTaskRepository task = new JobCardDailyActivityTaskRepository();
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction trn = connection.BeginTransaction();
int id = 0;
string internalId = "";
try
{
if (objJobCardDailyActivity.isProjectBased == 1)
{
internalId = DatabaseCommonRepository.GetNewDocNo(connection, objJobCardDailyActivity.OrganizationId, 38, true, trn);
}
else
{
internalId = DatabaseCommonRepository.GetNewDocNo(connection, objJobCardDailyActivity.OrganizationId, 27, true, trn);
}
objJobCardDailyActivity.JobCardDailyActivityRefNo = internalId.ToString();
string sql = @"insert into JobCardDailyActivity (JobCardDailyActivityDate,JobCardId,JobCardDailyActivityRefNo,Remarks,EmployeeId,CreatedBy,CreatedDate,OrganizationId)
Values (@JobCardDailyActivityDate,@JobCardId,@JobCardDailyActivityRefNo,@Remarks,@EmployeeId,@CreatedBy,@CreatedDate,@OrganizationId);
SELECT CAST(SCOPE_IDENTITY() as int)";
id = connection.Query <int>(sql, objJobCardDailyActivity, trn).Single();
foreach (var item in objJobCardDailyActivity.JobCardDailyActivityTask)
{
if (item.ActualHours == null || item.ActualHours == 0)
{
continue;
}
item.JobCardDailyActivityId = id;
item.CreatedDate = DateTime.Now;
sql = @"insert into JobCardDailyActivityTask (JobCardDailyActivityId,JobCardTaskId,TaskStartDate,TaskEndDate,OverTime,ActualHours,CreatedBy,CreatedDate,OrganizationId, EmployeeId, StartTime, EndTime) Values
(@JobCardDailyActivityId,@JobCardTaskId,@TaskStartDate,@TaskEndDate,@OverTime,@ActualHours,@CreatedBy,@CreatedDate,@OrganizationId, NULLIF(@EmployeeId, 0), @StartTime, @EndTime);
SELECT CAST(SCOPE_IDENTITY() as int)";
var taskid = connection.Query <int>(sql, item, trn).Single();
}
InsertLoginHistory(dataConnection, objJobCardDailyActivity.CreatedBy, "Create", "Job Card", id.ToString(), "0");
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
throw ex;
}
return(id);
}
}
/// <summary>
/// Insert into stock return head table. (StockReturn table)
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int InsertStockReturn(StockReturn model)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
model.StockReturnRefNo = DatabaseCommonRepository.GetNewDocNo(connection, model.OrganizationId, 21, true, txn);
string sql = @"insert into StockReturn(
StockReturnRefNo, StockReturnDate,StockPointId,JobCardId,
SpecialRemarks,CreatedBy,CreatedDate,OrganizationId) Values
(@StockReturnRefNo, @StockReturnDate,@StockPointId,@JobCardId,
@SpecialRemarks,@CreatedBy,@CreatedDate,@OrganizationId);
SELECT CAST(SCOPE_IDENTITY() as int)";
var id = connection.Query <int>(sql, model, txn).Single();
foreach (var item in model.Items)
{
item.StockReturnId = id;
new StockReturnItemRepository().InsertStockReturnItem(item, connection, txn);
new StockUpdateRepository().InsertStockUpdate(new StockUpdate
{
OrganizationId = model.OrganizationId,
CreatedBy = model.CreatedBy,
CreatedDate = model.CreatedDate,
StockPointId = model.StockPointId,
StockType = "StockReturn",
StockInOut = "IN",
stocktrnDate = System.DateTime.Today,
ItemId = item.ItemId,
Quantity = item.Quantity * (1),
StocktrnId = id,
StockUserId = model.StockReturnRefNo
}, connection, txn);
}
InsertLoginHistory(dataConnection, model.CreatedBy, "Create", "Stock Return", id.ToString(), "0");
txn.Commit();
return(id);
}
catch (Exception)
{
txn.Rollback();
return(0);
}
}
}
public int InsertStockJournal(StockJournal model)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction trn = connection.BeginTransaction();
try
{
int id = 0;
string sql = @"INSERT INTO StockJournal (StockJournalRefno,StockPointId,Remarks,IssuedBy,StockJournalDate,CreatedBy,CreatedDate,OrganizationId) VALUES (@StockJournalRefno,@StockPointId,@Remarks,@IssuedBy,GETDATE(),@CreatedBy,GETDATE(),@OrganizationId);
SELECT CAST(SCOPE_IDENTITY() as int)";
model.StockJournalRefno = DatabaseCommonRepository.GetNewDocNo(connection, model.OrganizationId ?? 0, 22, true, trn);
id = connection.Query <int>(sql, model, trn).Single();
var StockJournalItemsRepo = new StockJournalItemsRepository();
int? StockPointId = model.StockPointId;
string RefNo = model.StockJournalRefno;
string Remarks = model.Remarks;
string CreatedBy = model.CreatedBy;
int? OrgId = model.OrganizationId;
foreach (var item in model.StockJournelItems)
{
item.StockJournalId = id;
item.StockPointId = StockPointId;
item.Remarks = Remarks;
item.CreatedBy = CreatedBy;
item.OrganizationId = OrgId;
item.StockJournalRefno = RefNo;
new StockJournalItemsRepository().InsertStockJournalItem(item, connection, trn);
}
InsertLoginHistory(dataConnection, model.CreatedBy, "Create", "Stock Journal", id.ToString(), "0");
trn.Commit();
return(id);
}
catch (Exception)
{
trn.Rollback();
return(0);
}
}
}
public string InsertSupplyOrder(SupplyOrder objSupplyOrder)
{
int id = 0;
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction trn = connection.BeginTransaction();
try
{
objSupplyOrder.SupplyOrderNo = DatabaseCommonRepository.GetNewDocNo(connection, objSupplyOrder.OrganizationId, 9, true, trn);
string sql = @"insert into SupplyOrder(SupplyOrderNo,SupplyOrderDate,SupplierId,QuotaionNoAndDate,SpecialRemarks,PaymentTerms,
DeliveryTerms,RequiredDate,CreatedBy,CreatedDate,OrganizationId,CurrencyId,NetDiscount,DiscountRemarks,NetAmount)
Values (@SupplyOrderNo,@SupplyOrderDate,@SupplierId,@QuotaionNoAndDate,@SpecialRemarks,@PaymentTerms,@DeliveryTerms,
@RequiredDate,@CreatedBy,@CreatedDate,@OrganizationId, @CurrencyId,@NetDiscount,@DiscountRemarks,@NetAmount);
SELECT CAST(SCOPE_IDENTITY() as int)";
id = connection.Query <int>(sql, objSupplyOrder, trn).Single <int>();
var supplyorderitemrepo = new SupplyOrderItemRepository();
foreach (var item in objSupplyOrder.SupplyOrderItems)
{
if (item.OrderedQty > 0)
{
item.SupplyOrderId = id;
item.OrganizationId = objSupplyOrder.OrganizationId;
supplyorderitemrepo.InsertSupplyOrderItem(item, connection, trn);
}
}
InsertLoginHistory(dataConnection, objSupplyOrder.CreatedBy, "Create", "LPO", id.ToString(), "0");
trn.Commit();
}
catch (Exception ex)
{
logger.Error(ex.Message);
trn.Rollback();
throw;
}
return(objSupplyOrder.SupplyOrderNo);
}
}
public string InsertProjectCompletion(ProjectCompletion model)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
string query = @"INSERT INTO ProjectCompletion
(ProjectCompletionRefNo,ProjectCompletionDate,ChillerTemperature,
ChillerDimension,ChillerCondensingUnit,ChillerEvaporator,
ChillerRefrigerant,ChillerQuantity,FreezerTemperature,FreezerDimension,
FreezerCondensingUnit,FreezerEvaporator,FreezerRefrigerant,FreezerQuantity,
SaleOrderId,OrganizationId,CreatedDate,CreatedBy,isActive, ProjectWarrantyExpiryDate)
VALUES
(@ProjectCompletionRefNo,@ProjectCompletionDate,@ChillerTemperature,
@ChillerDimension,@ChillerCondensingUnit,@ChillerEvaporator,
@ChillerRefrigerant,@ChillerQuantity,@FreezerTemperature,@FreezerDimension,
@FreezerCondensingUnit,@FreezerEvaporator,@FreezerRefrigerant,@FreezerQuantity,
@SaleOrderId,@OrganizationId,@CreatedDate,@CreatedBy,1, @ProjectWarrantyExpiryDate);
SELECT CAST(SCOPE_IDENTITY() AS INT)";
model.ProjectCompletionRefNo = DatabaseCommonRepository.GetNewDocNo(connection, model.OrganizationId, 30, true, txn);
model.ProjectCompletionId = connection.Query <int>(query, model, txn).First();
if (model.ItemBatches != null && model.ItemBatches.Count > 0)
{
InsertItemBatch(model, connection, txn);
}
InsertLoginHistory(dataConnection, model.CreatedBy, "Create", typeof(ProjectCompletion).Name, model.ProjectCompletionId.ToString(), model.OrganizationId.ToString());
txn.Commit();
return(model.ProjectCompletionRefNo);
}
catch (Exception ex)
{
txn.Rollback();
throw ex;
}
}
}
public string CreateStockTransfer(StockTransfer model)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
model.StockTransferRefNo = DatabaseCommonRepository.GetNewDocNo(connection, model.OrganizationId, 29, true, txn);
int id = Create(model, connection, txn);
InsertLoginHistory(dataConnection, model.CreatedBy, "Create", "StockTransfer", id.ToString(), model.OrganizationId.ToString());
txn.Commit();
return(model.StockTransferRefNo);
}
catch (Exception)
{
txn.Rollback();
throw;
}
}
}
public string InsertStoreIssue(StoreIssue objStoreIssue)
{
try
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
string referenceNo = DatabaseCommonRepository.GetNewDocNo(connection, objStoreIssue.OrganizationId, 24, true, txn);
objStoreIssue.StoreIssueRefNo = referenceNo;
string sql = @" INSERT INTO StoreIssue(
StoreIssueRefNo,StoreIssueDate,StockPointId,WorkShopRequestId,EmployeeId,
Remarks,CreatedBy,CreatedDate,OrganizationId,isActive)
VALUES (
@StoreIssueRefNo,@StoreIssueDate,@StockPointId,@WorkShopRequestId,@EmployeeId,
@Remarks,@CreatedBy,@CreatedDate,@OrganizationId,1);
SELECT CAST(SCOPE_IDENTITY() AS INT)";
var id = connection.Query <int>(sql, objStoreIssue, txn).Single();
foreach (var item in objStoreIssue.Items)
{
if (item.CurrentIssuedQuantity != 0)
{
item.StoreIssueId = id;
new StoreIssueItemRepository().InsertStoreIssueItem(item, connection, txn);
new StockUpdateRepository().InsertStockUpdate(new StockUpdate
{
OrganizationId = objStoreIssue.OrganizationId,
CreatedBy = objStoreIssue.CreatedBy,
CreatedDate = objStoreIssue.CreatedDate,
StockPointId = objStoreIssue.StockpointId,
StockType = "StoreIssue",
StockInOut = "OUT",
stocktrnDate = System.DateTime.Today,
ItemId = item.ItemId,
Quantity = item.CurrentIssuedQuantity * (-1),
StocktrnId = id,
StockUserId = objStoreIssue.StoreIssueRefNo
}, connection, txn);
}
}
InsertLoginHistory(dataConnection, objStoreIssue.CreatedBy, "Create", "Store Issue", id.ToString(), "0");
txn.Commit();
return(referenceNo);
}
catch (Exception ex)
{
txn.Rollback();
throw ex;
}
}
}
catch (SqlException sx)
{
throw sx;
}
catch (Exception ex)
{
throw ex;
}
}
public string InsertProformaInvoice(ProformaInvoice objProInvoice)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
var internalId = DatabaseCommonRepository.GetNewDocNo(connection, objProInvoice.OrganizationId, 6, true, txn);
objProInvoice.ProformaInvoiceRefNo = internalId;
string sql = @"
insert into ProformaInvoice(ProformaInvoiceRefNo,ProformaInvoiceDate,SaleOrderId,SpecialRemarks,PaymentTerms,CreatedBy,CreatedDate,OrganizationId,isProjectBased)
Values (@ProformaInvoiceRefNo,@ProformaInvoiceDate,@SaleOrderId,@SpecialRemarks,@PaymentTerms,@CreatedBy,@CreatedDate,@OrganizationId,@isProjectBased);
SELECT CAST(SCOPE_IDENTITY() as int) ProformaInvoiceId";
var id = connection.Query <int>(sql, objProInvoice, txn).Single();
foreach (ProformaInvoiceItem item in objProInvoice.Items)
{
item.ProformaInvoiceId = id;
new ProformaInvoiceItemRepository().InsertProformaInvoiceItem(item, connection, txn);
}
if (objProInvoice.isProjectBased == 1)
{
#region delete and inserting print description
try
{
sql = @"DELETE FROM PrintDescription WHERE SaleOrderId = " + objProInvoice.SaleOrderId;
connection.Execute(sql, transaction: txn);
sql = @"INSERT INTO PrintDescription (SaleOrderId, Description, UoM, Quantity, PriceEach, Amount, CreatedBy, CreatedDate, OrganizationId)
VALUES (@SaleOrderId, @Description, @UoM, @Quantity, @PriceEach, @Amount, @CreatedBy, GETDATE(), @OrganizationId)";
foreach (var item in objProInvoice.PrintDescriptions)
{
if (item.Description == null)
{
continue;
}
item.SaleOrderId = objProInvoice.SaleOrderId;
item.CreatedBy = int.Parse(objProInvoice.CreatedBy);
item.OrganizationId = objProInvoice.OrganizationId;
item.Amount = (item.Quantity ?? 0) * item.PriceEach;
if (connection.Execute(sql, item, txn) <= 0)
{
throw new Exception();
}
}
}
catch (Exception) { throw new Exception(); }
#endregion
}
InsertLoginHistory(dataConnection, objProInvoice.CreatedBy, "Create", "Proforma Invoice", id.ToString(), "0");
txn.Commit();
return(id + "|" + internalId);
}
catch (Exception)
{
txn.Rollback();
return("0");
}
}
}
public int InsertVehicleInPass(VehicleInPass objVehicleInPass)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
string sql = @"SELECT SaleOrderItemId FROM VehicleInPass WHERE SaleOrderItemId = @id";
if (connection.Query <int>(sql, new { id = objVehicleInPass.SaleOrderItemId }, txn).FirstOrDefault() != 0)
{
throw new DuplicateNameException();
}
var internalId = DatabaseCommonRepository.GetNewDocNo(connection, objVehicleInPass.OrganizationId, 15, true, txn);
objVehicleInPass.VehicleInPassNo = internalId;
sql = @"INSERT INTO VehicleInPass(
VehicleInPassNo,
SaleOrderItemId,
SaleOrderId,
RegistrationNo,
ChassisNo,
VehicleInPassDate,
EmployeeId,
Remarks,
CreatedBy,
CreatedDate,
OrganizationId,
isActive)
VALUES(
@VehicleInPassNo,
@SaleOrderItemId,
(SELECT SaleOrderId FROM SaleOrderItem WHERE SaleOrderItemId = @SaleOrderItemId),
@RegistrationNo,
@ChassisNo,
@VehicleInPassDate,
@EmployeeId,
@Remarks,
@CreatedBy,
@CreatedDate,
@OrganizationId,
1
);
SELECT CAST(SCOPE_IDENTITY() AS INT)VehicleInPassId";
var id = connection.Query <int>(sql, objVehicleInPass, txn).Single();
InsertLoginHistory(dataConnection, objVehicleInPass.CreatedBy, "Create", "Vehicle Inpass", id.ToString(), "0");
txn.Commit();
return(id);
}
catch (DuplicateNameException)
{
txn.Rollback();
throw;
}
catch (Exception)
{
txn.Rollback();
return(id1);
}
}
}
public string InsertDirectPurchaseRequest(DirectPurchaseRequest model)
{
int id = 0;
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
var internalId = DatabaseCommonRepository.GetNewDocNo(connection, model.OrganizationId, 10, true, txn);
model.PurchaseRequestNo = internalId;
if (model.SoOrJc == "JC")
{
model.SaleOrderId = null;
}
else if (model.SoOrJc == "SO")
{
model.JobCardId = null;
}
else
{
model.isStockOrder = true;
}
model.TotalAmount = model.items.Sum(m => (m.Quantity * m.Rate));
string sql = @"INSERT INTO DirectPurchaseRequest
(
[PurchaseRequestNo],
[PurchaseRequestDate],
[SpecialRemarks],
[RequiredDate],
[TotalAmount],
[CreatedBy],
[CreatedDate],
[OrganizationId],
[isActive],
[isApproved],
[SaleOrderId],
[JobCardId],
[isStockOrder]
)
VALUES
(
@PurchaseRequestNo,
@PurchaseRequestDate,
@SpecialRemarks,
@RequiredDate,
@TotalAmount,
@CreatedBy,
@CreatedDate,
@OrganizationId,
1,
0,
@SaleOrderId,
@JobCardId,
@isStockOrder
)
SELECT CAST(SCOPE_IDENTITY() AS INT)";
id = connection.Query <int>(sql, model, txn).Single <int>();
var supplyorderitemrepo = new DirectPurchaseItemRepository();
foreach (var item in model.items)
{
item.DirectPurchaseRequestId = id;
new DirectPurchaseItemRepository().InsertDirectPurchaseRequestItem(item, connection, txn);
}
InsertLoginHistory(dataConnection, model.CreatedBy, "Create", "Direct Purchase", id.ToString(), "0");
txn.Commit();
}
catch (Exception ex)
{
logger.Error(ex.Message);
txn.Rollback();
throw;
}
return(model.PurchaseRequestNo);
}
}
public int InsertProjectDailyActivity(JobCardDailyActivity model)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
//int _DailyActivityId = 0;
model.JobCardDailyActivityRefNo = DatabaseCommonRepository.GetNewDocNo(connection, model.OrganizationId, 38, true, txn);
string sql1 = @"SELECT JobCardTaskId FROM JobCardTask WHERE
JobCardTaskMasterId = @JobCardTaskMasterId AND
TaskDate = CONVERT(VARCHAR, @TaskStartDate, 106) AND
JobCardId = @id AND EmployeeId IS NULL",
sql2 = @"DELETE FROM JobCardTask WHERE JobCardTaskId IN @id",
sql3 = @"INSERT INTO JobCardTask (JobCardId, JobCardTaskMasterId, SlNo, EmployeeId, TaskDate, [Hours], CreatedBy, OrganizationId, isActive)
OUTPUT inserted.JobCardTaskId VALUES (@JobCardId, @JobCardTaskMasterId, @SlNo, @EmployeeId, @TaskDate, @Hours, @CreatedBy, @OrganizationId, 1)",
sql4 = @"SELECT JobCardTaskId FROM JobCardTask WHERE
JobCardTaskMasterId = @JobCardTaskMasterId AND
TaskDate = CONVERT(VARCHAR, @TaskStartDate, 106) AND
JobCardId = @id AND EmployeeId = @EmployeeId";
model.JobCardDailyActivityId = InsertDailyActivityHead(model, connection, txn);
foreach (var item in model.JobCardDailyActivityTask)
{
if (item.ActualHours == null || item.ActualHours == 0 ||
item.EmployeeId == 0 || item.JobCardTaskMasterId == 0)
{
continue;
}
item.JobCardDailyActivityId = model.JobCardDailyActivityId;
List <int> existingTasks = connection.Query <int>(sql1, new
{
JobCardTaskMasterId = item.JobCardTaskMasterId,
TaskStartDate = item.TaskStartDate,
id = model.JobCardId
}, txn).ToList();
if (existingTasks.Count > 0)
{
connection.Execute(sql2, new { id = existingTasks }, txn);
}
item.JobCardTaskId = connection.Query <int>(sql4, new
{
JobCardTaskMasterId = item.JobCardTaskMasterId,
TaskStartDate = item.TaskStartDate,
id = model.JobCardId,
EmployeeId = item.EmployeeId
}, txn).FirstOrDefault();
if (item.JobCardTaskId == 0)
{
item.JobCardTaskId = connection.Query <int>(sql3, new
{
JobCardId = model.JobCardId,
JobCardTaskMasterId = item.JobCardTaskMasterId,
SlNo = 0, //SlNo should be given here
EmployeeId = item.EmployeeId,
TaskDate = item.TaskStartDate,
Hours = item.ActualHours,
CreatedBy = model.CreatedBy,
OrganizationId = model.OrganizationId
}, txn).FirstOrDefault();
if (item.JobCardTaskId <= 0)
{
throw new Exception("Insertion of task failed.");
}
InsertDailyActivityTasks(item, connection, txn);
}
else
{
InsertDailyActivityTasks(item, connection, txn);
}
//}
//else
//{
// InsertDailyActivityTasks(item, connection, txn);
//}
}
InsertLoginHistory(dataConnection, model.CreatedBy, "Create", "DailyActivity", model.JobCardDailyActivityId.ToString(), model.OrganizationId.ToString());
txn.Commit();
return(model.JobCardDailyActivityId);
}
catch (Exception ex)
{
txn.Rollback();
throw ex;
}
}
}
public string CreateStock(StockCreation model)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
model.StockCreationRefNo = DatabaseCommonRepository.GetNewDocNo(connection, model.OrganizationId, 25, true, txn);
string query = @"INSERT INTO StockCreation
(
StockCreationRefNo,
StockCreationDate,
CreatedBy,
CreatedDate,
OrganizationId,
ConsumedStockpointId,
FinishedStockpointId)
VALUES
(
@StockCreationRefNo,
@StockCreationDate,
@CreatedBy,
@CreatedDate,
@OrganizationId,
@ConsumedStockpointId,
@FinishedStockpointId)
;
SELECT CAST(SCOPE_IDENTITY() AS INT)";
int id = connection.Query <int>(query, model, txn).First();
#region Inserting Finished Goods + Stock Updation
foreach (var item in model.FinishedGoods)
{
item.StockCreationId = id;
if (item.Quantity > 0)
{
var i = new StockCreationFinishedGoodsRepository().InsertFinishedGoods(item, connection, txn);
}
new StockUpdateRepository().InsertStockUpdate(new StockUpdate
{
OrganizationId = model.OrganizationId,
CreatedBy = model.CreatedBy,
CreatedDate = model.CreatedDate,
StockPointId = model.FinishedStockpointId,
StockType = "StockCreation",
StockInOut = "IN",
stocktrnDate = System.DateTime.Today,
ItemId = item.ItemId,
Quantity = item.Quantity,
StocktrnId = id,
StockUserId = model.StockCreationRefNo
}, connection, txn);
}
#endregion
#region Inserting Consumed Items + Stock Updation
foreach (var item in model.ConsumedItems)
{
item.StockCreationId = id;
if (item.Quantity > 0)
{
var i = new StockCreationConsumedItemsRepository().InsertConsumedItems(item, connection, txn);
}
new StockUpdateRepository().InsertStockUpdate(new StockUpdate
{
OrganizationId = model.OrganizationId,
CreatedBy = model.CreatedBy,
CreatedDate = model.CreatedDate,
StockPointId = model.ConsumedStockpointId,
StockType = "StockCreation",
StockInOut = "OUT",
stocktrnDate = System.DateTime.Today,
ItemId = item.ItemId,
Quantity = item.Quantity * (-1),
StocktrnId = id,
StockUserId = model.StockCreationRefNo
}, connection, txn);
}
#endregion
InsertLoginHistory(dataConnection, model.CreatedBy, "Create", "Stock Creation", id.ToString(), "0");
txn.Commit();
}
catch (Exception ex)
{
txn.Rollback();
throw ex;
}
return(model.StockCreationRefNo);
}
}