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 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); }
/// <summary> /// Fn for Insert Employee /// </summary> /// <param name="model"></param> /// <returns></returns> public Employee Insert(Employee model) { using (IDbConnection connection = OpenConnection(dataConnection)) { IDbTransaction trn = connection.BeginTransaction(); string sql = @"insert into Employee(EmployeeRefNo,EmployeeName,GenderId,DesignationId,CategoryId,LocationId,HourlyCost,CreatedBy,CreatedDate,OrganizationId) Values (@EmployeeRefNo,@EmployeeName,@GenderId,@DesignationId,@CategoryId,@LocationId,@HourlyCost,@CreatedBy,@CreatedDate,@OrganizationId); SELECT CAST(SCOPE_IDENTITY() as int)"; int id = 0; try { int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(Employee).Name, "0", 1); model.EmployeeRefNo = "EMP/" + internalid; id = connection.Query <int>(sql, model, trn).Single(); model.EmployeeId = id; InsertLoginHistory(dataConnection, model.CreatedBy, "Create", "Employee", id.ToString(), model.OrganizationId.ToString()); trn.Commit(); } catch (Exception e) { trn.Rollback(); model.EmployeeId = 0; model.EmployeeRefNo = null; } return(model); } }
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; } } }
public CommissionAgent InsertCommissionAgent(CommissionAgent model) { using (IDbConnection connection = OpenConnection(dataConnection)) { IDbTransaction trn = connection.BeginTransaction(); string sql = @"INSERT INTO CommissionAgent (CommissionAgentRefNo,CommissionAgentName,Address1,Address2,Address3,Phone,CreatedBy,CreatedDate,OrganizationId) VALUES(@CommissionAgentRefNo,@CommissionAgentName,@Address1,@Address2,@Address3,@Phone,@CreatedBy,getDate(),@OrganizationId); SELECT CAST(SCOPE_IDENTITY() as int)"; int id = 0; try { int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(CommissionAgent).Name, "0", 1); model.CommissionAgentRefNo = "CA/" + internalid; id = connection.Query <int>(sql, model, trn).Single(); model.CommissionAgentId = id; trn.Commit(); } catch (Exception e) { trn.Rollback(); model.CommissionAgentId = 0; model.CommissionAgentRefNo = null; } return(model); } }
public QCParameters InsertQCPara(QCParameters objQCParameters) { using (IDbConnection connection = OpenConnection(dataConnection)) { var result = new QCParameters(); IDbTransaction trn = connection.BeginTransaction(); string sql = @"INSERT INTO QCParam (QCParamName,QCParaId,QCRefNo,CreatedBy,CreatedDate,OrganizationId,isActive) VALUES(@QCParamName,@QCParaId,@QCRefNo,@CreatedBy,@CreatedDate,@OrganizationId,1); SELECT CAST(SCOPE_IDENTITY() as int)"; try { int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(QCParameters).Name, "0", 1); objQCParameters.QCRefNo = "QC/" + internalid; int id = connection.Query <int>(sql, objQCParameters, trn).Single(); objQCParameters.QCParaId = id; InsertLoginHistory(dataConnection, objQCParameters.CreatedBy, "Create", "Box", internalid.ToString(), "0"); //connection.Dispose(); trn.Commit(); } catch (Exception ex) { trn.Rollback(); objQCParameters.QCParaId = 0; objQCParameters.QCRefNo = null; } return(objQCParameters); } }
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 Organization InsertOrganization(Organization objOrganization) { using (IDbConnection connection = OpenConnection(dataConnection)) { var result = new Organization(); IDbTransaction trn = connection.BeginTransaction(); string sql = @"INSERT INTO Organization (OrganizationRefNo,OrganizationName,DoorNo,Street,State,Country,CurrencyId,Zip,Phone,Fax,Email,ContactPerson,Image1,isActive,cmpCode, FyId) VALUES(@OrganizationRefNo,@OrganizationName,@DoorNo,@Street,@State,@Country,@CurrencyId,@Zip,@Phone,@Fax,@Email,@ContactPerson,@Image1,1,@cmpCode,@FyId); SELECT CAST(SCOPE_IDENTITY() as int)"; try { int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(Organization).Name, "0", 1); objOrganization.OrganizationRefNo = "ORG/" + internalid; int id = connection.Query <int>(sql, objOrganization, trn).Single(); objOrganization.OrganizationId = id; //connection.Dispose(); InsertLoginHistory(dataConnection, objOrganization.CreatedBy, "Create", "Organization", id.ToString(), "0"); trn.Commit(); } catch (Exception ex) { trn.Rollback(); objOrganization.OrganizationId = 0; objOrganization.OrganizationRefNo = null; } return(objOrganization); } }
public ItemGroup InsertItemGroup(ItemGroup objItemGroup) { using (IDbConnection connection = OpenConnection(dataConnection)) { var result = new ItemGroup(); IDbTransaction trn = connection.BeginTransaction(); string sql = @"INSERT INTO ItemGroup (ItemGroupRefNo,ItemGroupName,ItemCategoryId,CreatedBy,CreatedDate,OrganizationId) VALUES(@ItemGroupRefNo,@ItemGroupName,@ItemCategoryId,@CreatedBy,getDate(),@OrganizationId); SELECT CAST(SCOPE_IDENTITY() as int)"; try { int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(ItemGroup).Name, "0", 1); objItemGroup.ItemGroupRefNo = "ITMGRP/" + internalid; int id = connection.Query <int>(sql, objItemGroup, trn).Single(); objItemGroup.ItemGroupId = id; InsertLoginHistory(dataConnection, objItemGroup.CreatedBy, "Create", "Item Group", id.ToString(), "0"); //connection.Dispose(); trn.Commit(); } catch (Exception ex) { trn.Rollback(); objItemGroup.ItemGroupId = 0; objItemGroup.ItemGroupRefNo = null; } return(objItemGroup); } }
public SupplierCategory InsertSupplierCategory(SupplierCategory objSupplierCategory) { using (IDbConnection connection = OpenConnection(dataConnection)) { var result = new SupplierCategory(); IDbTransaction trn = connection.BeginTransaction(); string sql = @"INSERT INTO SupplierCategory(SupCategoryRefNo,SupCategoryName,SupCategoryShortName,CreatedBy,CreatedDate,OrganizationId) VALUES(@SupCategoryRefNo,@SupCategoryName,@SupCategoryShortName,@CreatedBy,getDate(),@OrganizationId); SELECT CAST(SCOPE_IDENTITY() as int)"; try { int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(SupplierCategory).Name, "0", 1); objSupplierCategory.SupCategoryRefNo = "SUPCAT/" + internalid; int id = connection.Query <int>(sql, objSupplierCategory, trn).Single(); objSupplierCategory.SupCategoryId = id; //connection.Dispose(); InsertLoginHistory(dataConnection, objSupplierCategory.CreatedBy, "Create", "Supplier Category", id.ToString(), "0"); trn.Commit(); } catch (Exception ex) { trn.Rollback(); objSupplierCategory.SupCategoryId = 0; objSupplierCategory.SupCategoryRefNo = null; } return(objSupplierCategory); } }
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 JobCardTaskMaster InsertTask(JobCardTaskMaster objTask) { using (IDbConnection connection = OpenConnection(dataConnection)) { var result = new JobCardTaskMaster(); IDbTransaction trn = connection.BeginTransaction(); string sql = @"INSERT INTO JobCardTaskMaster(JobCardTaskRefNo,JobCardTaskName,MinimumRate,ActualHrs,CreatedBy,CreatedDate,OrganizationId) VALUES(@JobCardTaskRefNo,@JobCardTaskName,@MinimumRate,@ActualHrs,@CreatedBy,@CreatedDate,@OrganizationId); SELECT CAST(SCOPE_IDENTITY() as int)"; try { int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(JobCardTaskMaster).Name, "0", 1); objTask.JobCardTaskRefNo = "T/" + internalid; int id = connection.Query <int>(sql, objTask, trn).Single(); objTask.JobCardTaskMasterId = id; InsertLoginHistory(dataConnection, objTask.CreatedBy, "Create", "Task", id.ToString(), "0"); //connection.Dispose(); trn.Commit(); } catch (Exception ex) { trn.Rollback(); objTask.JobCardTaskMasterId = 0; objTask.JobCardTaskRefNo = null; } return(objTask); } }
public FreezerUnit InsertFreezerUnit(FreezerUnit objFreezerUnit) { using (IDbConnection connection = OpenConnection(dataConnection)) { var result = new FreezerUnit(); IDbTransaction trn = connection.BeginTransaction(); string sql = @"INSERT INTO FreezerUnit (FreezerUnitRefNo,FreezerUnitName,CreatedBy,CreatedDate,OrganizationId) VALUES(@FreezerUnitRefNo,@FreezerUnitName,@CreatedBy,@CreatedDate,@OrganizationId); SELECT CAST(SCOPE_IDENTITY() as int)"; try { int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(FreezerUnit).Name, "0", 1); objFreezerUnit.FreezerUnitRefNo = "FU/" + internalid; int id = connection.Query <int>(sql, objFreezerUnit, trn).Single(); objFreezerUnit.FreezerUnitId = id; //connection.Dispose(); InsertLoginHistory(dataConnection, objFreezerUnit.CreatedBy, "Create", "Freezer Unit", id.ToString(), "0"); trn.Commit(); } catch (Exception ex) { trn.Rollback(); objFreezerUnit.FreezerUnitId = 0; objFreezerUnit.FreezerUnitRefNo = null; } return(objFreezerUnit); } }
public SalesTarget InsertSalesTarget(SalesTarget salestarget) { using (IDbConnection connection = OpenConnection(dataConnection)) { var result = new SalesTarget(); IDbTransaction trn = connection.BeginTransaction(); string sql = @"INSERT INTO SalesTarget (SalesTargetRefNo,MonthId,WorkDescriptionId,Target,OrganizationId,FyId,CreatedBy,CreatedDate,isActive) VALUES(@SalesTargetRefNo,@MonthId,@WorkDescriptionId,@Target,@OrganizationId,@FyId,@CreatedBy,@CreatedDate,1); SELECT CAST(SCOPE_IDENTITY() as int)"; try { int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(SalesTarget).Name, "0", 1); salestarget.SalesTargetRefNo = "ST/" + internalid; int id = connection.Query <int>(sql, salestarget, trn).Single(); salestarget.SalesTargetId = id; InsertLoginHistory(dataConnection, salestarget.CreatedBy, "Create", "SalesTarget", internalid.ToString(), "0"); //connection.Dispose(); trn.Commit(); } catch (Exception ex) { trn.Rollback(); salestarget.SalesTargetId = 0; salestarget.SalesTargetRefNo = null; } return(salestarget); } }
public PaymentTerms InsertPaymentTerms(PaymentTerms objPaymentTerms) { using (IDbConnection connection = OpenConnection(dataConnection)) { var result = new PaymentTerms(); IDbTransaction trn = connection.BeginTransaction(); string sql = @"INSERT INTO PaymentTerms (PaymentTermsRefNo,PaymentTermsName,CreatedBy,CreatedDate,OrganizationId,isActive) VALUES(@PaymentTermsRefNo,@PaymentTermsName,@CreatedBy,getDate(),@OrganizationId,1); SELECT CAST(SCOPE_IDENTITY() as int)"; try { int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(PaymentTerms).Name, "0", 1); objPaymentTerms.PaymentTermsRefNo = "PT/" + internalid; int id = connection.Query <int>(sql, objPaymentTerms, trn).Single(); objPaymentTerms.PaymentTermsId = id; //connection.Dispose(); InsertLoginHistory(dataConnection, objPaymentTerms.CreatedBy, "Create", "Payment Terms", id.ToString(), "0"); trn.Commit(); } catch (Exception ex) { trn.Rollback(); objPaymentTerms.PaymentTermsId = 0; objPaymentTerms.PaymentTermsRefNo = null; } return(objPaymentTerms); } }
public Currency InsertCurrency(Currency model) { using (IDbConnection connection = OpenConnection(dataConnection)) { IDbTransaction trn = connection.BeginTransaction(); string sql = @" INSERT INTO [Currency] (CurrencyRefNo,CurrencyName,Elementary,CurrencyExRate,CurrencySymbolId,OrganizationId) VALUES (@CurrencyRefNo,@CurrencyName,@Elementary,@CurrencyExRate,@CurrencySymbolId,@OrganizationId); SELECT CAST(SCOPE_IDENTITY() as int)"; int id = 0; try { int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(Currency).Name, "0", 1); model.CurrencyRefNo = "CUR/" + internalid; id = connection.Query <int>(sql, model, trn).Single(); model.CurrencyId = id; InsertLoginHistory(dataConnection, model.CreatedBy, "Create", "Currency", id.ToString(), "0"); trn.Commit(); } catch (Exception e) { trn.Rollback(); model.CurrencyId = 0; model.CurrencyRefNo = null; } return(model); } }
public Box InsertBox(Box objBox) { using (IDbConnection connection = OpenConnection(dataConnection)) { var result = new Box(); IDbTransaction trn = connection.BeginTransaction(); string sql = @"INSERT INTO Box (BoxRefNo,BoxName,CreatedBy,CreatedDate,OrganizationId) VALUES(@BoxRefNo,@BoxName,@CreatedBy,@CreatedDate,@OrganizationId); SELECT CAST(SCOPE_IDENTITY() as int)"; try { int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(Box).Name, "0", 1); objBox.BoxRefNo = "B/" + internalid; int id = connection.Query <int>(sql, objBox, trn).Single(); objBox.BoxId = id; InsertLoginHistory(dataConnection, objBox.CreatedBy, "Create", "Box", internalid.ToString(), "0"); //connection.Dispose(); trn.Commit(); } catch (Exception ex) { trn.Rollback(); objBox.BoxId = 0; objBox.BoxRefNo = null; } return(objBox); } }
public EmployeeLocation InsertEmployeeLocation(EmployeeLocation objEmployeeLocation) { using (IDbConnection connection = OpenConnection(dataConnection)) { var result = new EmployeeLocation(); IDbTransaction trn = connection.BeginTransaction(); string sql = @"INSERT INTO EmployeeLocation(LocationRefNo,LocationName,CreatedBy,CreatedDate,OrganizationId,isActive) VALUES(@LocationRefNo,@LocationName,@CreatedBy,@CreatedDate,@OrganizationId,1); SELECT CAST(SCOPE_IDENTITY() as int)"; try { int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(EmployeeLocation).Name, "0", 1); objEmployeeLocation.LocationRefNo = "L/" + internalid; int id = connection.Query <int>(sql, objEmployeeLocation, trn).Single(); objEmployeeLocation.LocationId = id; //connection.Dispose(); InsertLoginHistory(dataConnection, objEmployeeLocation.CreatedBy, "Create", "EmployeeLocation", id.ToString(), "0"); trn.Commit(); } catch (Exception ex) { trn.Rollback(); objEmployeeLocation.LocationId = 0; objEmployeeLocation.LocationRefNo = null; } return(objEmployeeLocation); } }
public Stockpoint InsertStockpoint(Stockpoint model) { using (IDbConnection connection = OpenConnection(dataConnection)) { IDbTransaction trn = connection.BeginTransaction(); string sql = @"insert into StockPoint(StockPointRefNo,StockPointName,StockPointShrtName,StockPointDoorNo,StockPointZip,StockPointArea, StockPointPhone,StockPointCity,StockPointFax,CreatedBy,CreatedDate,OrganizationId) Values (@StockPointRefNo,@StockPointName,@StockPointShrtName,@StockPointDoorNo,@StockPointZip, @StockPointArea,@StockPointPhone,@StockPointCity,@StockPointFax,@CreatedBy,getDate(),@OrganizationId); SELECT CAST(SCOPE_IDENTITY() as int)"; int id = 0; try { int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(Stockpoint).Name, "0", 1); model.StockPointRefNo = "SP/" + internalid; id = connection.Query <int>(sql, model, trn).Single(); model.StockPointId = id; InsertLoginHistory(dataConnection, model.CreatedBy, "Create", "Stock Point", id.ToString(), "0"); trn.Commit(); } catch (Exception e) { trn.Rollback(); model.StockPointId = 0; model.StockPointRefNo = null; } return(model); } }
public VehicleModel InsertVehicleModel(VehicleModel model) { using (IDbConnection connection = OpenConnection(dataConnection)) { IDbTransaction trn = connection.BeginTransaction(); string sql = @"INSERT INTO VehicleModel(VehicleModelRefNo,VehicleModelName,VehicleModelDescription,CreatedBy,CreatedDate,OrganizationId) VALUES(@VehicleModelRefNo,@VehicleModelName,@VehicleModelDescription,@CreatedBy,getDate(),@OrganizationId); SELECT CAST(SCOPE_IDENTITY() as int)"; int id = 0; try { int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(VehicleModel).Name, "0", 1); model.VehicleModelRefNo = "VM/" + internalid; id = connection.Query <int>(sql, model, trn).Single(); model.VehicleModelId = id; InsertLoginHistory(dataConnection, model.CreatedBy, "Create", "Vehicle Model", id.ToString(), "0"); trn.Commit(); } catch (Exception e) { trn.Rollback(); model.VehicleModelId = 0; model.VehicleModelRefNo = null; } return(model); } }
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"; } } }
/// <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 EmployeeCategory InsertEmployeeCategory(EmployeeCategory model) { using (IDbConnection connection = OpenConnection(dataConnection)) { IDbTransaction trn = connection.BeginTransaction(); string sql = @"INSERT INTO EmployeeCategory(EmpCategoryRefNo,EmpCategoryName,CreatedBy,CreatedDate,OrganizationId) VALUES(@EmpCategoryRefNo,@EmpCategoryName,@CreatedBy,getDate(),@OrganizationId); SELECT CAST(SCOPE_IDENTITY() as int)"; int id = 0; try { int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(EmployeeCategory).Name, "0", 1); model.EmpCategoryRefNo = "EMPC/" + internalid; id = connection.Query <int>(sql, model, trn).Single(); model.EmpCategoryId = id; InsertLoginHistory(dataConnection, model.CreatedBy, "Create", "Employee Category", id.ToString(), "0"); trn.Commit(); } catch (Exception e) { trn.Rollback(); model.EmpCategoryId = 0; model.EmpCategoryRefNo = null; } return(model); } }
public AdditionOrDeduction Insert(AdditionOrDeduction objAdditionOrDeduction) { using (IDbConnection connection = OpenConnection(dataConnection)) { var result = new AdditionOrDeduction(); IDbTransaction trn = connection.BeginTransaction(); string sql = @"INSERT INTO AdditionDeduction (AddDedRefNo,AddDedName,AddDedType,AddDedRemarks,CreatedBy,CreatedDate,OrganizationId,isActive) VALUES(@AddDedRefNo,@AddDedName, @AddDedType, @AddDedRemarks,@CreatedBy,@CreatedDate,@OrganizationId,1); SELECT CAST(SCOPE_IDENTITY() as int)"; try { int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(AdditionOrDeduction).Name, "0", 1); objAdditionOrDeduction.AddDedRefNo = "AD/" + internalid; int id = connection.Query <int>(sql, objAdditionOrDeduction, trn).Single(); objAdditionOrDeduction.AddDedId = id; //connection.Dispose(); trn.Commit(); } catch (Exception ex) { trn.Rollback(); objAdditionOrDeduction.AddDedId = 0; objAdditionOrDeduction.AddDedRefNo = null; } return(objAdditionOrDeduction); } }
public WorkDescription InsertWorkDescription(WorkDescription objWorkDescription) { using (IDbConnection connection = OpenConnection(dataConnection)) { //var result = new WorkDescription(); IDbTransaction trn = connection.BeginTransaction(); string sql = @"insert into WorkDescription(WorkDescriptionRefNo,VehicleModelId,FreezerUnitId,BoxId, WorkDescr,WorkDescrShortName,isNewInstallation,isRepair,isSubAssembly,isProjectBased,CreatedBy,CreatedDate,OrganizationId) Values (@WorkDescriptionRefNo,@VehicleModelId,@FreezerUnitId,@BoxId,@WorkDescr,@WorkDescrShortName,@isNewInstallation, @isRepair,@isSubAssembly,0,@CreatedBy,@CreatedDate,@OrganizationId); SELECT CAST(SCOPE_IDENTITY() as int)"; try { int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(WorkDescription).Name, "0", 1); objWorkDescription.WorkDescriptionRefNo = "WD/" + internalid; var id = connection.Query <int>(sql, objWorkDescription, trn).Single(); var worksitemrepo = new WorkVsItemRepository(); foreach (var item in objWorkDescription.WorkVsItems) { if (item.ItemId == 0 || item.Quantity == 0) { continue; } item.WorkDescriptionId = id; item.CreatedBy = objWorkDescription.CreatedBy; item.CreatedDate = objWorkDescription.CreatedDate; worksitemrepo.InsertWorkVsItem(item, connection, trn); } var workstaskepo = new WorkVsTaskRepository(); foreach (var item in objWorkDescription.WorkVsTasks) { if (item.JobCardTaskMasterId == 0) { continue; } item.WorkDescriptionId = id; item.CreatedBy = objWorkDescription.CreatedBy; item.CreatedDate = objWorkDescription.CreatedDate; workstaskepo.InsertWorkVsTask(item, connection, trn); } objWorkDescription.WorkDescriptionId = id; InsertLoginHistory(dataConnection, objWorkDescription.CreatedBy, "Create", "Work Description", id.ToString(), "0"); trn.Commit(); } catch (Exception ex) { trn.Rollback(); objWorkDescription.WorkDescriptionId = 0; objWorkDescription.WorkDescriptionRefNo = null; } return(objWorkDescription); } }
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); } }
private int CreateMatchingWorkDescription(IDbConnection connection, IDbTransaction txn, SalesQuotationItem model) { try { string freezerName = model.FreezerUnitId == null ? String.Empty : new ItemRepository().GetItem(model.FreezerUnitId ?? 0).ItemName, boxName = model.BoxId == null ? String.Empty : new ItemRepository().GetItem(model.BoxId ?? 0).ItemName, ref_no = "WD/" + DatabaseCommonRepository.GetInternalIDFromDatabase(connection, txn, typeof(WorkDescription).Name, "0", 1), query = @"INSERT INTO WorkDescription (WorkDescriptionRefNo, FreezerUnitId, BoxId, WorkDescr, WorkDescrShortName, isNewInstallation, CreatedDate, OrganizationId, isActive, isProjectBased, VehicleModelId) VALUES ( @WorkDescriptionRefNo, @FreezerUnitId, @BoxId, @WorkDescr, @WorkDescrShortName, @isNewInstallation, @CreatedDate, @OrganizationId, @isActive, @isProjectBased, @VehicleModelId ) SELECT CAST(SCOPE_IDENTITY() AS INT)"; int?id = connection.Query <int>(query, new { WorkDescriptionRefNo = ref_no, FreezerUnitId = model.FreezerUnitId, BoxId = model.BoxId, WorkDescr = freezerName + (freezerName == String.Empty || boxName == String.Empty ? String.Empty : " + ") + boxName, WorkDescrShortName = freezerName + (freezerName == String.Empty || boxName == String.Empty ? String.Empty : " + ") + boxName, isNewInstallation = 1, CreatedDate = System.DateTime.Today, OrganizationId = model.OrganizationId, isActive = 1, isProjectBased = 0, VehicleModelId = model.VehicleModelId }, txn).FirstOrDefault(); if (id == null) { throw new Exception(); } else { return(id ?? 0); } } catch (Exception) { throw; } }
/// <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 Supplier InsertSupplier(Supplier objSupplier) { using (IDbConnection connection = OpenConnection(dataConnection)) { var result = new Supplier(); IDbTransaction trn = connection.BeginTransaction(); string sql = @"insert into Supplier(SupplierRefNo,SupplierName,PurchaseTypeId,SupplierPrintName, SupCategoryId,ContractDate,ContactPerson,Active, DoorNo,City,State,CountryId, PostBoxNo,Phone,Fax,Email, Bank,Branch,AccountDetails,SwiftCode, RtgsNo,AccountNo,DiscountTermsId,DiscountRate, CurrencyId,SupRefAccNo,PanNo,TinNo, CreditPeriod,CreditLimit,PaymentTerms, CreatedBy,CreatedDate,OrganizationId) Values (@SupplierRefNo,@SupplierName,@PurchaseTypeId,@SupplierPrintName, @SupCategoryId,@ContractDate,@ContactPerson,@Active, @DoorNo,@City,@State,@CountryId, @PostBoxNo,@Phone,@Fax,@Email, @Bank,@Branch,@AccountDetails,@SwiftCode, @RtgsNo,@AccountNo,@DiscountTermsId,@DiscountRate, @CurrencyId,@SupRefAccNo,@PanNo,@TinNo, @CreditPeriod,@CreditLimit,@PaymentTerms, @CreatedBy,@CreatedDate,@OrganizationId); SELECT CAST(SCOPE_IDENTITY() as int)"; try { int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(Supplier).Name, "0", 1); objSupplier.SupplierRefNo = "SUP/" + internalid; int id = connection.Query <int>(sql, objSupplier, trn).Single(); objSupplier.SupplierId = id; InsertLoginHistory(dataConnection, objSupplier.CreatedBy, "Create", "Supplier", id.ToString(), "0"); //connection.Dispose(); trn.Commit(); } catch (Exception ex) { trn.Rollback(); objSupplier.SupplierId = 0; objSupplier.SupplierRefNo = null; } return(objSupplier); } }
public int InsertCustomerReceipt(CustomerReceipt model) { using (IDbConnection connection = OpenConnection(dataConnection)) { IDbTransaction txn = connection.BeginTransaction(); try { int internalId = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, txn, typeof(CustomerReceipt).Name, "0", 1); model.CustomerReceiptRefNo = "CR/" + internalId; if (model.Against == "JC") { model.SaleOrderId = null; model.SalesInvoiceId = null; } if (model.Against == "SO") { model.JobCardId = null; model.SalesInvoiceId = null; } if (model.Against == "SI") { model.JobCardId = null; model.SaleOrderId = null; } string sql = @"INSERT INTO CustomerReceipt (CustomerReceiptRefNo,CustomerReceiptDate,CustomerId, SaleOrderId,JobCardId,SalesInvoiceId,SaleOrderAmount, Amount,SpecialRemarks,CreatedBy,CreatedDate,OrganizationId,isActive) VALUES (@CustomerReceiptRefNo,@CustomerReceiptDate,@CustomerId, @SaleOrderId,@JobCardId,@SalesInvoiceId,@SaleOrderAmount, @Amount,@SpecialRemarks,@CreatedBy,@CreatedDate,@OrganizationId,1) SELECT CAST(SCOPE_IDENTITY() AS INT)"; var id = connection.Query <int>(sql, model, txn).Single(); InsertLoginHistory(dataConnection, model.CreatedBy, "Create", "Customer Receipt", id.ToString(), "0"); txn.Commit(); return(id); } catch (Exception) { txn.Rollback(); return(0); } } }