public int InsertItemMasterData()
{
try
{
string query = "select * from MASTERS where slcd = 'IT'";
DataTable dtItemMaster = dbConnection.GetData(query);
List <PharmaDAL.Entity.ItemMaster> listItemMaster = new List <PharmaDAL.Entity.ItemMaster>();
int _result = 0;
companyCodeCounts = TotalItemsByCompany();
using (PharmaDBEntities context = new PharmaDBEntities())
{
if (dtItemMaster != null && dtItemMaster.Rows.Count > 0)
{
var companyMaster = context.CompanyMaster.Select(p => p).ToList();
var accountLedgerMaster = context.AccountLedgerMaster.Select(p => p).ToList();
foreach (DataRow dr in dtItemMaster.Rows)
{
try
{
string originalItemCompanyCode = Convert.ToString(dr["CompCD"]).TrimEnd();
string companyCode = Common.companyCodeMap.Where(p => p.OriginalCompanyCode == originalItemCompanyCode).FirstOrDefault().MappedCompanyCode;
int companyID = companyMaster.Where(p => p.CompanyCode == companyCode).FirstOrDefault().CompanyId;
int totalItemsFromSameCompany = companyCodeCounts.Where(p => p.CompanyCode == companyCode).Select(p => p.CompanyCodeCount).FirstOrDefault();
totalItemsFromSameCompany++;
if (totalItemsFromSameCompany > 1)
{
foreach (var item in companyCodeCounts.Where(p => p.CompanyCode == companyCode))
{
item.CompanyCodeCount = totalItemsFromSameCompany;
}
}
else
{
companyCodeCounts.Add(new CompanyCodeCounts()
{
CompanyCode = companyCode, CompanyCodeCount = totalItemsFromSameCompany
});
}
string itemCode = string.Concat(companyCode, totalItemsFromSameCompany.ToString().PadLeft((9 - companyCode.Length), '0'));
string originalItemCode = Convert.ToString(dr["ACNO"]).TrimEnd();
Common.itemCodeMap.Add(new ItemCodeMap()
{
OriginalItemCode = originalItemCode, MappedItemCode = itemCode
});
string originalSleType = Convert.ToString(dr["SType"]).TrimEnd();
string saleLedgerCode = Common.accountLedgerCodeMap.Where(q => q.OriginalAccountLedgerCode == originalSleType).FirstOrDefault().MappedAccountLedgerCode;
var saleType = accountLedgerMaster.Where(p => p.AccountLedgerCode == saleLedgerCode).FirstOrDefault();
PharmaDAL.Entity.AccountLedgerMaster purchaseType = null;
if (saleType.SalePurchaseTaxType == 5)
{
purchaseType = accountLedgerMaster.Where(p => p.SalePurchaseTaxType == 5 && p.AccountLedgerType.SystemName == Constants.AccountLedgerType.PurchaseLedger).FirstOrDefault();
}
else if (saleType.SalePurchaseTaxType == 12)
{
purchaseType = accountLedgerMaster.Where(p => p.SalePurchaseTaxType == 12 && p.AccountLedgerType.SystemName == Constants.AccountLedgerType.PurchaseLedger).FirstOrDefault();
}
else if (saleType.SalePurchaseTaxType == 0)
{
purchaseType = accountLedgerMaster.Where(p => p.SalePurchaseTaxType == 0 && p.AccountLedgerType.SystemName == Constants.AccountLedgerType.PurchaseLedger).FirstOrDefault();
}
PharmaDAL.Entity.ItemMaster newItemMaster = new PharmaDAL.Entity.ItemMaster()
{
ItemCode = itemCode,
ItemName = Convert.ToString(dr["ACName"]).TrimEnd(),
CompanyID = companyID,
ConversionRate = Convert.ToDecimal(dr["ConvRate"]),
ShortName = Convert.ToString(dr["ALT_Name"]).TrimEnd(),
Packing = Convert.ToString(dr["Size"]).TrimEnd(),
PurchaseRate = Convert.ToDecimal(dr["PRate"]),
MRP = Convert.ToDecimal(dr["MRP"]),
SaleRate = Convert.ToDecimal(dr["SRate"]),
SpecialRate = Convert.ToDecimal(dr["SPLRate"]),
WholeSaleRate = Convert.ToDecimal(dr["WSRATE"]),
SaleExcise = Convert.ToDecimal(dr["EXCISES"]),
SurchargeOnSale = Convert.ToDecimal(dr["SC"]),
TaxOnSale = Convert.ToDecimal(dr["STax"]),
Scheme1 = Convert.ToDecimal(dr["Scheme1"]),
Scheme2 = Convert.ToDecimal(dr["Scheme2"]),
PurchaseExcise = Convert.ToDecimal(dr["ExciseP"]),
UPC = Convert.ToString(dr["UPC"]).TrimEnd(),
IsHalfScheme = Convert.ToChar(dr["half"]) == 'Y' ? true : false,
IsQTRScheme = Convert.ToChar(dr["qtr"]) == 'Y' ? true : false,
SpecialDiscount = Convert.ToDecimal(dr["SPLDIS"]),
SpecialDiscountOnQty = Convert.ToDecimal(dr["DISQTY"]),
IsFixedDiscount = Convert.ToChar(dr["check_dis"]) == 'Y' ? true : false,
FixedDiscountRate = Convert.ToDecimal(dr["drate1"]),
//MaximumQty = Convert.ToDecimal(dr["MAX_QTY"]),
//MaximumDiscount = Convert.ToDecimal(dr["max_dis"]),
SurchargeOnPurchase = Convert.ToDecimal(dr["PSC"]),
TaxOnPurchase = Convert.ToDecimal(dr["PTax"]),
DiscountRecieved = Convert.ToDecimal(dr["PDIS"]),
SpecialDiscountRecieved = Convert.ToDecimal(dr["PSPLDIS"]),
QtyPerCase = Convert.ToDecimal(dr["Case_Qty"]),
Location = Convert.ToString(dr["location"]).TrimEnd(),
//MinimumStock = Convert.ToInt32(dr["min"]),
//MaximumStock = Convert.ToInt32(dr["max"]),
SaleTypeId = saleType.AccountLedgerID,
PurchaseTypeId = purchaseType.AccountLedgerID,
Status = Convert.ToChar(dr["ACSTS"]) == '*' ? false : true,
CreatedBy = "admin",
CreatedOn = DateTime.Now,
HSNCode = "HSNCODE001"
};
listItemMaster.Add(newItemMaster);
}
catch (Exception)
{
log.Info("ITEM MASTER : Error in ACName --> " + Convert.ToString(dr["ACName"]).TrimEnd() + " ACNO -->" + Convert.ToString(dr["ACName"]).TrimEnd() + " Comp Code -->" + Convert.ToString(dr["CompCD"]).TrimEnd());
}
}
}
context.ItemMaster.AddRange(listItemMaster);
_result = context.SaveChanges();
return(_result);
}
}
catch (DbEntityValidationException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
}
public int InsertGeneralLedgerData()
{
try
{
int debitCreditControlId = 0;
string query = "select * from ACM where slcd = '04'";
DataTable dtGeneralLedgerMaster = dbConnection.GetData(query);
List <PharmaDAL.Entity.AccountLedgerMaster> listGeneralLedgerMaster = new List <PharmaDAL.Entity.AccountLedgerMaster>();
int _result = 0;
using (PharmaDBEntities context = new PharmaDBEntities())
{
var maxAccountLedgerID = context.AccountLedgerMaster.Count();
int accountLedgerTypeID = context.AccountLedgerType.Where(p => p.SystemName == PharmaBusinessObjects.Common.Constants.AccountLedgerType.GeneralLedger).FirstOrDefault().AccountLedgerTypeID;
if (dtGeneralLedgerMaster != null && dtGeneralLedgerMaster.Rows.Count > 0)
{
foreach (DataRow dr in dtGeneralLedgerMaster.Rows)
{
try
{
maxAccountLedgerID++;
string accountTypeShortName = Convert.ToString(dr["Actyp"]).Trim();
int accountTypeID = context.AccountType.Where(p => p.AccountTypeShortName == accountTypeShortName).FirstOrDefault().AccountTypeID;
string accountLedgerCode = "GEN" + maxAccountLedgerID.ToString().PadLeft(7, '0');
string originalAccountLedgerCode = Convert.ToString(dr["ACNO"]).Trim();
string originalCreditControlCode = Convert.ToString(dr["Cac"]).Trim();
string mappedCreditControlCode = Common.controlCodeMap.Where(q => q.OriginalControlCode == originalCreditControlCode).FirstOrDefault().MappedControlCode;
int creditControlCodeID = context.AccountLedgerMaster.Where(p => p.AccountLedgerCode == mappedCreditControlCode).FirstOrDefault().AccountLedgerID;
string originalDebitControlCode = Convert.ToString(dr["Cad"]).Trim();
string mappedDebitControlCode = Common.controlCodeMap.Where(q => q.OriginalControlCode == originalDebitControlCode).FirstOrDefault().MappedControlCode;
int debitControlCodeID = context.AccountLedgerMaster.Where(p => p.AccountLedgerCode == mappedDebitControlCode).FirstOrDefault().AccountLedgerID;
if (Convert.ToString(dr["ACNO"]).Trim().ToUpper().Contains("PTAX01") || Convert.ToString(dr["ACNO"]).Trim().ToUpper().Contains("STAX01"))
{
debitCreditControlId = debitControlCodeID;
continue;
}
Common.accountLedgerCodeMap.Add(new AccountLedgerCodeMap()
{
OriginalAccountLedgerCode = originalAccountLedgerCode, MappedAccountLedgerCode = accountLedgerCode, AccountLedgerTypeID = accountLedgerTypeID
});
PharmaDAL.Entity.AccountLedgerMaster newGeneralLedgerMaster = new PharmaDAL.Entity.AccountLedgerMaster()
{
AccountLedgerName = Convert.ToString(dr["ACName"]).Trim(),
AccountLedgerCode = accountLedgerCode,
AccountLedgerTypeId = accountLedgerTypeID,
AccountTypeId = accountTypeID,
OpeningBalance = Convert.ToDecimal(dr["Abop"]),
CreditDebit = Convert.ToDecimal(dr["Abop"]) > 0 ? Convert.ToString(PharmaBusinessObjects.Common.Enums.TransType.D) : Convert.ToString(PharmaBusinessObjects.Common.Enums.TransType.C),
Status = Convert.ToChar(dr["ACSTS"]) == '*' ? false : true,
CreditControlCodeID = creditControlCodeID,
DebitControlCodeID = debitControlCodeID,
CreatedBy = "admin",
CreatedOn = DateTime.Now,
SalePurchaseTaxType = null
};
listGeneralLedgerMaster.Add(newGeneralLedgerMaster);
}
catch (Exception)
{
log.Info("GENERAL LEDGER : Error in ACName --> " + Convert.ToString(dr["ACName"]).Trim());
}
}
}
context.AccountLedgerMaster.AddRange(listGeneralLedgerMaster);
_result = context.SaveChanges();
try
{
var generlLedges = context.AccountLedgerMaster.Where(p => p.AccountLedgerType.SystemName == Constants.AccountLedgerType.GeneralLedger &&
(
p.AccountLedgerCode.StartsWith("IGST") ||
p.AccountLedgerCode.StartsWith("SGST") ||
p.AccountLedgerCode.StartsWith("CGST")
)
).ToList();
if (generlLedges != null && generlLedges.Count > 0)
{
foreach (var item in generlLedges)
{
item.DebitControlCodeID = debitCreditControlId;
item.CreditControlCodeID = debitCreditControlId;
}
context.SaveChanges();
}
}
catch (Exception)
{
log.Info("GENERAL LEDGER : Error in Set Debit/Credit contorl if for IGST/CGST/SGST ");
}
return(_result);
}
}
catch (Exception ex)
{
throw ex;
}
}
public int InsertIncomeLedgerData()
{
try
{
string query = "select * from ACM where slcd = '01'";
DataTable dtIncomeLedgerMaster = dbConnection.GetData(query);
List <PharmaDAL.Entity.AccountLedgerMaster> listIncomeLedgerMaster = new List <PharmaDAL.Entity.AccountLedgerMaster>();
int _result = 0;
using (PharmaDBEntities context = new PharmaDBEntities())
{
var maxAccountLedgerID = context.AccountLedgerMaster.Count();
int accountLedgerTypeID = context.AccountLedgerType.Where(p => p.SystemName == PharmaBusinessObjects.Common.Constants.AccountLedgerType.IncomeLedger).FirstOrDefault().AccountLedgerTypeID;
if (dtIncomeLedgerMaster != null && dtIncomeLedgerMaster.Rows.Count > 0)
{
foreach (DataRow dr in dtIncomeLedgerMaster.Rows)
{
try
{
maxAccountLedgerID++;
string accountTypeShortName = Convert.ToString(dr["Actyp"]).Trim();
int accountTypeID = context.AccountType.Where(p => p.AccountTypeShortName == accountTypeShortName).FirstOrDefault().AccountTypeID;
string accountLedgerCode = "INC" + maxAccountLedgerID.ToString().PadLeft(7, '0');
string originalAccountLedgerCode = Convert.ToString(dr["ACNO"]).Trim();
string originalCreditControlCode = Convert.ToString(dr["Cac"]).Trim();
string mappedCreditControlCode = Common.controlCodeMap.Where(q => q.OriginalControlCode == originalCreditControlCode).FirstOrDefault().MappedControlCode;
int creditControlCodeID = context.AccountLedgerMaster.Where(p => p.AccountLedgerCode == mappedCreditControlCode).FirstOrDefault().AccountLedgerID;
string originalDebitControlCode = Convert.ToString(dr["Cad"]).Trim();
string mappedDebitControlCode = Common.controlCodeMap.Where(q => q.OriginalControlCode == originalDebitControlCode).FirstOrDefault().MappedControlCode;
int debitControlCodeID = context.AccountLedgerMaster.Where(p => p.AccountLedgerCode == mappedDebitControlCode).FirstOrDefault().AccountLedgerID;
Common.accountLedgerCodeMap.Add(new AccountLedgerCodeMap()
{
OriginalAccountLedgerCode = originalAccountLedgerCode, MappedAccountLedgerCode = accountLedgerCode, AccountLedgerTypeID = accountLedgerTypeID
});
if (Convert.ToString(dr["ACName"]).Trim().ToUpper().Equals("ROUNDED OFF ADJUSTMENT A/C"))
{
accountLedgerCode = "ADJ" + Convert.ToString("1").PadLeft(7, '0');
}
PharmaDAL.Entity.AccountLedgerMaster newIncomeLedgerMaster = new PharmaDAL.Entity.AccountLedgerMaster()
{
AccountLedgerName = Convert.ToString(dr["ACName"]).Trim(),
AccountLedgerCode = accountLedgerCode,
AccountLedgerTypeId = accountLedgerTypeID,
AccountTypeId = accountTypeID,
OpeningBalance = Convert.ToDecimal(dr["Abop"]),
CreditDebit = Convert.ToDecimal(dr["Abop"]) > 0 ? Convert.ToString(PharmaBusinessObjects.Common.Enums.TransType.D) : Convert.ToString(PharmaBusinessObjects.Common.Enums.TransType.C),
Status = Convert.ToChar(dr["ACSTS"]) == '*' ? false : true,
CreditControlCodeID = creditControlCodeID,
DebitControlCodeID = debitControlCodeID,
CreatedBy = "admin",
CreatedOn = DateTime.Now,
SalePurchaseTaxType = null
};
listIncomeLedgerMaster.Add(newIncomeLedgerMaster);
}
catch (Exception)
{
log.Info("INCOME LEDGER : Error in ACName --> " + Convert.ToString(dr["ACName"]).Trim());
}
}
}
context.AccountLedgerMaster.AddRange(listIncomeLedgerMaster);
_result = context.SaveChanges();
return(_result);
}
}
catch (Exception ex)
{
throw ex;
}
}
public int InsertControlCodesData()
{
try
{
string query = "select * from ACM where slcd = 'CC'";
DataTable dtControlCodesMaster = dbConnection.GetData(query);
List <PharmaDAL.Entity.AccountLedgerMaster> listControlCodesMaster = new List <PharmaDAL.Entity.AccountLedgerMaster>();
int _result = 0;
using (PharmaDBEntities context = new PharmaDBEntities())
{
var maxAccountLedgerID = context.AccountLedgerMaster.Count();
int accountLedgerTypeID = context.AccountLedgerType.Where(p => p.SystemName == PharmaBusinessObjects.Common.Constants.AccountLedgerType.ControlCodes).FirstOrDefault().AccountLedgerTypeID;
if (dtControlCodesMaster != null && dtControlCodesMaster.Rows.Count > 0)
{
foreach (DataRow dr in dtControlCodesMaster.Rows)
{
try
{
maxAccountLedgerID++;
string accountTypeShortName = Convert.ToString(dr["Actyp"]).Trim();
int accountTypeID = context.AccountType.Where(p => p.AccountTypeShortName == accountTypeShortName).FirstOrDefault().AccountTypeID;
string accountLedgerCode = "CTRL" + maxAccountLedgerID.ToString().PadLeft(6, '0');
string originalAccountLedgerCode = Convert.ToString(dr["ACNO"]).Trim();
Common.controlCodeMap.Add(new ControlCodeMap()
{
OriginalControlCode = originalAccountLedgerCode, MappedControlCode = accountLedgerCode
});
PharmaDAL.Entity.AccountLedgerMaster newControlCodeMaster = new PharmaDAL.Entity.AccountLedgerMaster()
{
AccountLedgerName = Convert.ToString(dr["ACName"]).Trim(),
AccountLedgerCode = accountLedgerCode,
AccountLedgerTypeId = accountLedgerTypeID,
AccountTypeId = accountTypeID,
OpeningBalance = Convert.ToDecimal(dr["Abop"]),
CreditDebit = Convert.ToDecimal(dr["Abop"]) > 0 ? Convert.ToString(PharmaBusinessObjects.Common.Enums.TransType.D) : Convert.ToString(PharmaBusinessObjects.Common.Enums.TransType.C),
Status = Convert.ToChar(dr["ACSTS"]) == '*' ? false : true,
CreditControlCodeID = null,
DebitControlCodeID = null,
CreatedBy = "admin",
CreatedOn = DateTime.Now,
SalePurchaseTaxType = null
};
listControlCodesMaster.Add(newControlCodeMaster);
}
catch (Exception)
{
log.Info("CONTROL CODES: Error in ACName --> " + Convert.ToString(dr["ACName"]).Trim());
}
}
}
context.AccountLedgerMaster.AddRange(listControlCodesMaster);
_result = context.SaveChanges();
return(_result);
}
}
catch (Exception ex)
{
throw ex;
}
}
