/// <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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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);
}
}
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;
}
}
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);
}
}
}
public Item InsertItem(Item objItem)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
var result = new Item();
IDbTransaction trn = connection.BeginTransaction();
string sql = @"insert into Item(ItemRefNo,PartNo,ItemName,ItemPrintName,ItemShortName,ItemGroupId,ItemSubGroupId,
ItemCategoryId,ItemUnitId,MinLevel,ReorderLevel,MaxLevel,BatchRequired,StockRequired,
CriticalItem,FreezerUnit,Box,OrganizationId,CreatedBy,CreatedDate, isConsumable,CondenserUnit,EvaporatorUnit,Door) Values
(@ItemRefNo,@PartNo,@ItemName,@ItemPrintName,@ItemShortName,@ItemGroupId,@ItemSubGroupId,
@ItemCategoryId,@ItemUnitId,@MinLevel,@ReorderLevel,@MaxLevel,@BatchRequired,@StockRequired,
@CriticalItem,@FreezerUnit,@Box,@OrganizationId,@CreatedBy,@CreatedDate, @isConsumable,@CondenserUnit,@EvaporatorUnit,@Door);
SELECT CAST(SCOPE_IDENTITY() as int)";
try
{
int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(Item).Name, "0", 1);
objItem.ItemRefNo = "ITM/" + internalid;
int id = connection.Query <int>(sql, objItem, trn).Single();
objItem.ItemId = id;
if (objItem.FreezerUnit || objItem.Box || objItem.CondenserUnit || objItem.Door || objItem.EvaporatorUnit)
{
InsertItemVsBOM(connection, trn, objItem);
InsertItemVsTasks(connection, trn, objItem);
}
InsertLoginHistory(dataConnection, objItem.CreatedBy, "Create", "Item", id.ToString(), "0");
//connection.Dispose();
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
objItem.ItemId = 0;
objItem.ItemRefNo = null;
}
return(objItem);
}
}
public string GetRefNo(CostingParameters objCosting)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
string RefNo = "";
var result = new CostingParameters();
IDbTransaction trn = connection.BeginTransaction();
try
{
int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(CostingParameters).Name, "0", 0);
RefNo = "cost/" + internalid;
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
}
return(RefNo);
}
}
public string GetRefNo(SupplierCategory objSupplierCategory)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
string RefNo = "";
var result = new SupplierCategory();
IDbTransaction trn = connection.BeginTransaction();
try
{
int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(SupplierCategory).Name, "0", 0);
RefNo = "SUPCAT/" + internalid;
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
}
return(RefNo);
}
}
public string GetRefNo(JobCardTaskMaster objTask)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
string RefNo = "";
var result = new JobCardTaskMaster();
IDbTransaction trn = connection.BeginTransaction();
try
{
int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(JobCardTaskMaster).Name, "0", 0);
RefNo = "T/" + internalid;
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
}
return(RefNo);
}
}
public Company InsertCompany(Company objCompany)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
var result = new Company();
IDbTransaction trn = connection.BeginTransaction();
string sql = @"insert into mstAccCompany(cmpUsercode,cmpName,cmpShrtName,cmpRemarks,cmpDoorNo,cmpStreet,
cmpArea,cmpState,cmpPhNo,cmpFax,cmpEmail,cmpWeb,cmpPAN,cmpSSN,
cmpVATNo,cmpCSTNo,cmpIECode,cmpWorkSheetNo,cmpFormPrefix,CreatedBy,CreatedDate,IsActive)
Values (@cmpUsercode,@cmpName,@cmpShrtName,@cmpRemarks,@cmpDoorNo,
@cmpStreet,@cmpArea,@cmpState,@cmpPhNo,@cmpFax,@cmpEmail,@cmpWeb,@cmpPAN,
@cmpSSN,@cmpVATNo,@cmpCSTNo,@cmpIECode,@cmpWorkSheetNo,@cmpFormPrefix,@CreatedBy,@CreatedDate,1);
SELECT CAST(SCOPE_IDENTITY() as int)";
try
{
int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(Company).Name, "0", 1);
objCompany.cmpUsercode = "C/" + internalid;
int id = connection.Query <int>(sql, objCompany, trn).Single();
objCompany.cmpCode = id;
//connection.Dispose();
InsertLoginHistory(dataConnection, objCompany.CreatedBy, "Create", "mstAccCompany", id.ToString(), "0");
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
objCompany.cmpCode = 0;
objCompany.cmpUsercode = null;
}
return(objCompany);
}
}
public Customer InsertCustomer(Customer objCustomer)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
var result = new Customer();
IDbTransaction trn = connection.BeginTransaction();
string sql = @"insert into Customer(CustomerRefNo,CustomerName,CustomerPrintName,CategoryId,LeadSourceId,CurrencyId,
DoorNo,Street,State,Country,Zip,Phone,Fax,Email,ContactPerson,CreditPeriod,
CreditLimit,CreatedBy,CreatedDate,OrganizationId,approve)
Values (@CustomerRefNo,@CustomerName,@CustomerPrintName,@CategoryId,@LeadSourceId,
@CurrencyId,@DoorNo,@Street,@State,@Country,@Zip,@Phone,@Fax,@Email,@ContactPerson,
@CreditPeriod,@CreditLimit,@CreatedBy,@CreatedDate,@OrganizationId,0);
SELECT CAST(SCOPE_IDENTITY() as int)";
try
{
int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(Customer).Name, "0", 1);
objCustomer.CustomerRefNo = "CUS/" + internalid;
int id = connection.Query <int>(sql, objCustomer, trn).Single();
objCustomer.CustomerId = id;
InsertLoginHistory(dataConnection, objCustomer.CreatedBy, "Create", "Customer", id.ToString(), "0");
//connection.Dispose();
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
objCustomer.CustomerId = 0;
objCustomer.CustomerRefNo = null;
}
return(objCustomer);
}
}