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