public List <PharmaBusinessObjects.Common.AccountLedgerType> GetAccountLedgerTypes()
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
return(context.AccountLedgerType.Where(p => p.Status).Select(p => new PharmaBusinessObjects.Common.AccountLedgerType()
{
AccountLedgerTypeID = p.AccountLedgerTypeID,
AccountLedgerTypeName = p.AccountLedgerTypeName,
AccountLedgerTypeSystemName = p.SystemName
}).ToList());
}
}
public List <PharmaBusinessObjects.Common.PersonLedgerType> GetPersonLedgerTypes()
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
return(context.PersonLedgerType.Where(p => p.Status).Select(p => new PharmaBusinessObjects.Common.PersonLedgerType()
{
PersonTypeId = p.PersonTypeId,
PersonType = p.PersonType,
Status = p.Status
}).ToList());
}
}
public void ClearTempTransaction(PharmaBusinessObjects.Transaction.TransactionEntity entity)
{
try
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
var tempAdjustments = context.TempBillOutStandingsAudjustment.Where(x => x.ReceiptPaymentID == entity.ReceiptPaymentID).ToList();
///Rollback all amount deducted amount from OS amount from Bill outstanding
///
foreach (var tempAdj in tempAdjustments)
{
if (tempAdj.TempReceiptPayment.OldReceiptPaymentID != null)
{
var billOutStanding = context.BillOutStandings.Where(p => p.BillOutStandingsID == tempAdj.TempReceiptPayment.OldReceiptPaymentID).FirstOrDefault();
if (billOutStanding != null)
{
billOutStanding.OSAmount = tempAdj.BillOutStandings.BillAmount - tempAdj.Amount;
}
// tempAdj.BillOutStandings.OSAmount = tempAdj.BillOutStandings.BillAmount - tempAdj.Amount;
}
else
{
tempAdj.BillOutStandings.OSAmount += tempAdj.Amount;
}
}
context.TempBillOutStandingsAudjustment.RemoveRange(tempAdjustments);
var tempReceipt = context.TempReceiptPayment.Where(x => x.ReceiptPaymentID == entity.ReceiptPaymentID).ToList();
context.TempReceiptPayment.RemoveRange(tempReceipt);
context.SaveChanges();
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public List <PharmaBusinessObjects.Common.RecordType> GetRecordTypes()
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
return(context.RecordType.Where(p => p.Status).Select(p => new PharmaBusinessObjects.Common.RecordType()
{
RecordTypeId = p.RecordTypeId,
RecordTypeName = p.RecordType1,
SystemName = p.SystemName,
Status = p.Status
}).ToList());
}
}
public PharmaBusinessObjects.Master.PersonRouteMaster GetPersonRouteMasterByCode(string personRouteCode)
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
return(context.PersonRouteMaster.Where(p => p.PersonRouteCode == personRouteCode)
.Select(p => new PharmaBusinessObjects.Master.PersonRouteMaster()
{
PersonRouteCode = p.PersonRouteCode,
PersonRouteName = p.PersonRouteName,
PersonRouteID = p.PersonRouteID,
}).FirstOrDefault());
}
}
public List <PharmaBusinessObjects.Common.CustomerType> GetCustomerTypes()
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
return(context.CustomerType.Where(p => p.Status).Select(p => new PharmaBusinessObjects.Common.CustomerType()
{
CustomerTypeId = p.CustomerTypeId,
CustomerTypeName = p.CustomerType1,
CustomerTypeShortName = p.CustomerTypeShortName,
Status = p.Status
}).ToList());
}
}
public PharmaBusinessObjects.Master.ItemMaster GetItemByCodeAndCustomer(int itemCode, string customerCode)
{
PharmaBusinessObjects.Master.ItemMaster master = new PharmaBusinessObjects.Master.ItemMaster();
using (PharmaDBEntities context = new PharmaDBEntities())
{
SqlConnection connection = (SqlConnection)context.Database.Connection;
SqlCommand cmd = new SqlCommand("GetSaleLineItemByCode", connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ItemCode", itemCode));
cmd.Parameters.Add(new SqlParameter("@CustomerCode", customerCode));
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt != null && dt.Rows.Count > 0)
{
master.ItemID = Convert.IsDBNull(dt.Rows[0]["ItemId"]) ? 0 : Convert.ToInt32(dt.Rows[0]["ItemId"]);
master.ItemCode = Convert.ToString(dt.Rows[0]["ItemCode"]);
master.ItemName = Convert.ToString(dt.Rows[0]["ItemName"]);
master.CompanyID = Convert.IsDBNull(dt.Rows[0]["CompanyID"]) ? 0 : Convert.ToInt32(dt.Rows[0]["CompanyID"]);
master.SaleRate = Convert.IsDBNull(dt.Rows[0]["SaleRate"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["SaleRate"]);
master.SpecialRate = Convert.IsDBNull(dt.Rows[0]["SpecialRate"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["SpecialRate"]);
master.WholeSaleRate = Convert.IsDBNull(dt.Rows[0]["WholeSaleRate"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["WholeSaleRate"]);
master.SaleExcise = Convert.IsDBNull(dt.Rows[0]["SaleExcise"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["SaleExcise"]);
master.SurchargeOnSale = Convert.IsDBNull(dt.Rows[0]["SurchargeOnSale"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["SurchargeOnSale"]);
master.TaxOnSale = Convert.IsDBNull(dt.Rows[0]["TaxOnSale"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["TaxOnSale"]);
master.Scheme1 = Convert.IsDBNull(dt.Rows[0]["Scheme1"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["Scheme1"]);
master.Scheme2 = Convert.IsDBNull(dt.Rows[0]["Scheme2"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["Scheme2"]);
master.IsHalfScheme = Convert.IsDBNull(dt.Rows[0]["IsHalfScheme"]) ? false : Convert.ToBoolean(dt.Rows[0]["IsHalfScheme"]);
master.IsQTRScheme = Convert.IsDBNull(dt.Rows[0]["IsQTRScheme"]) ? false : Convert.ToBoolean(dt.Rows[0]["IsQTRScheme"]);
master.SpecialDiscount = Convert.IsDBNull(dt.Rows[0]["SpecialDiscount"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["SpecialDiscount"]);
master.SpecialDiscountOnQty = Convert.IsDBNull(dt.Rows[0]["SpecialDiscountOnQty"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["SpecialDiscountOnQty"]);
master.IsFixedDiscount = Convert.IsDBNull(dt.Rows[0]["IsFixedDiscount"]) ? false : Convert.ToBoolean(dt.Rows[0]["IsFixedDiscount"]);
master.FixedDiscountRate = Convert.IsDBNull(dt.Rows[0]["FixedDiscountRate"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["FixedDiscountRate"]);
master.QtyPerCase = Convert.IsDBNull(dt.Rows[0]["QtyPerCase"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["QtyPerCase"]);
master.Location = Convert.ToString(dt.Rows[0]["Location"]);
master.SaleTypeId = Convert.IsDBNull(dt.Rows[0]["SaleTypeId"]) ? 0 : Convert.ToInt32(dt.Rows[0]["SaleTypeId"]);
//master.Discount = Convert.IsDBNull(dt.Rows[0]["Discount"]) ? 0 : Convert.ToDouble(dt.Rows[0]["Discount"]);
// master.Batch = Convert.ToString(dt.Rows[0]["Batch"]);
master.Packing = Convert.ToString(dt.Rows[0]["Packing"]);
master.PurchaseRate = Convert.IsDBNull(dt.Rows[0]["PurchaseRate"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["PurchaseRate"]);
// master.FifoID = Convert.IsDBNull(dt.Rows[0]["FifoID"]) ? 0 : Convert.ToInt32(dt.Rows[0]["FifoID"]);
}
}
return(master);
}
public List <PharmaBusinessObjects.Master.ItemMaster> GetAllItems()
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
return(context.ItemMaster.Select(p => new PharmaBusinessObjects.Master.ItemMaster()
{
ItemID = p.ItemID,
ItemCode = p.ItemCode,
ItemName = p.ItemName,
CompanyID = p.CompanyID,
CompanyName = p.CompanyMaster.CompanyName,
ConversionRate = p.ConversionRate,
ShortName = p.ShortName,
Packing = p.Packing,
PurchaseRate = p.PurchaseRate,
MRP = p.MRP,
SaleRate = p.SaleRate,
SpecialRate = p.SpecialRate,
WholeSaleRate = p.WholeSaleRate,
SaleExcise = p.SaleExcise,
SurchargeOnSale = p.SurchargeOnSale,
TaxOnSale = p.TaxOnSale,
Scheme1 = p.Scheme1,
Scheme2 = p.Scheme2,
PurchaseExcise = p.PurchaseExcise,
UPC = p.UPC,
IsHalfScheme = p.IsHalfScheme,
IsQTRScheme = p.IsQTRScheme,
SpecialDiscount = p.SpecialDiscount,
SpecialDiscountOnQty = p.SpecialDiscountOnQty,
IsFixedDiscount = p.IsFixedDiscount,
FixedDiscountRate = p.FixedDiscountRate,
SurchargeOnPurchase = p.SurchargeOnPurchase,
TaxOnPurchase = p.TaxOnPurchase,
DiscountRecieved = p.DiscountRecieved,
SpecialDiscountRecieved = p.SpecialDiscountRecieved,
QtyPerCase = p.QtyPerCase,
Location = p.Location,
SaleTypeId = p.SaleTypeId,
PurchaseTypeId = p.PurchaseTypeId,
PurchaseTypeCode = p.AccountLedgerMaster1.AccountLedgerCode,
PurchaseTypeName = p.AccountLedgerMaster1.AccountLedgerName,
PurchaseTypeRate = p.AccountLedgerMaster1.SalePurchaseTaxType,
HSNCode = p.HSNCode,
Status = p.Status,
StatusText = p.Status ? string.Empty : Constants.Others.Inactive,
CompanyCode = p.CompanyMaster.CompanyCode
}).ToList());
}
}
public List <PharmaBusinessObjects.Master.HSNCodes> GetAllHSNCodes()
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
List <PharmaBusinessObjects.Master.HSNCodes> allHSNCode = new List <PharmaBusinessObjects.Master.HSNCodes>();
allHSNCode = context.HSNCode.Select(hsn => new PharmaBusinessObjects.Master.HSNCodes()
{
HSNID = hsn.HSNID,
HSNCode = hsn.HSNCode1,
HSNDescription = hsn.HSNDescription
}).ToList();
return(allHSNCode);
}
}
public List <CompanyCodeCounts> TotalItemsByCompany()
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
var companyCodeCounts = itemMasterDao.GetAllItems().GroupBy(p => p.CompanyCode)
.Select(q => new CompanyCodeCounts()
{
CompanyCode = q.Key,
CompanyCodeCount = q.Count()
}
).ToList();
return(companyCodeCounts);
}
}
public int DeletePersonalLedger(PharmaBusinessObjects.Master.PersonalLedgerMaster p)
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
var personalLedgerMaster = context.PersonalLedger.FirstOrDefault(q => q.PersonalLedgerCode == p.PersonalLedgerCode);
if (personalLedgerMaster != null)
{
personalLedgerMaster.Status = false;
personalLedgerMaster.ModifiedBy = this.LoggedInUser.Username;
personalLedgerMaster.ModifiedOn = System.DateTime.Now;
}
return(context.SaveChanges());
}
}
public int DeleteCompany(int companyId)
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
var companyMaster = context.CompanyMaster.FirstOrDefault(p => p.CompanyId == companyId);
if (companyMaster != null)
{
companyMaster.Status = false;
companyMaster.ModifiedBy = this.LoggedInUser.Username;
companyMaster.ModifiedOn = System.DateTime.Now;
}
return(context.SaveChanges());
}
}
public int DeleteCustomerLedger(int customerLedgerID)
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
var customerLedgerMaster = context.CustomerLedger.FirstOrDefault(p => p.CustomerLedgerId == customerLedgerID);
if (customerLedgerMaster != null)
{
customerLedgerMaster.Status = false;
customerLedgerMaster.ModifiedBy = this.LoggedInUser.Username;
customerLedgerMaster.ModifiedOn = System.DateTime.Now;
}
return(context.SaveChanges());
}
}
private void InsertData(string voucherTypeCode)
{
List <PharmaDAL.Entity.PurchaseSaleBookHeader> listPurchaseSaleBookHeader = new List <PharmaDAL.Entity.PurchaseSaleBookHeader>();
using (PharmaDBEntities context = new PharmaDBEntities())
{
SqlConnection connection = (SqlConnection)context.Database.Connection;
connection.Open();
SqlCommand cmd = new SqlCommand("SaveMigrationTRN", connection);
cmd.CommandTimeout = 0;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@VoucherTypeCode", voucherTypeCode));
cmd.ExecuteNonQuery();
connection.Close();
}
}
public List <PharmaBusinessObjects.Master.Privledge> GetPrivledges(string searchText)
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
return(context.Privledges.Where(p => string.IsNullOrEmpty(searchText) || p.PriviledgeName.ToLower().Contains(searchText.ToLower())).Select(p => new PharmaBusinessObjects.Master.Privledge()
{
PrivledgeId = p.PrivledgeId,
PrivledgeName = p.PriviledgeName,
Status = p.Status,
ControlName = p.ControlName,
CreatedBy = p.CreatedBy,
CreatedOn = p.CreatedOn,
ModifiedBy = p.ModifiedBy,
ModifiedOn = p.ModifiedOn,
}).ToList());
}
}
public PharmaBusinessObjects.Master.Privledge GetPrivledgeById(int privledgeId)
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
return(context.Privledges.Where(p => p.PrivledgeId == privledgeId).Select(p => new PharmaBusinessObjects.Master.Privledge()
{
PrivledgeId = p.PrivledgeId,
PrivledgeName = p.PriviledgeName,
Status = p.Status,
ControlName = p.ControlName,
CreatedBy = p.CreatedBy,
CreatedOn = p.CreatedOn,
ModifiedBy = p.ModifiedBy,
ModifiedOn = p.ModifiedOn,
}).FirstOrDefault());
}
}
public List <PharmaBusinessObjects.Master.PersonRouteMaster> GetPersonRoutes()
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
return(context.PersonRouteMaster.Select(p => new PharmaBusinessObjects.Master.PersonRouteMaster()
{
PersonRouteID = p.PersonRouteID,
PersonRouteCode = p.PersonRouteCode,
PersonRouteName = p.PersonRouteName,
RecordTypeId = p.RecordTypeId,
RecordTypeNme = p.RecordType.RecordType1,
Status = p.Status,
SystemName = p.RecordType.SystemName,
StatusText = p.Status ? string.Empty : Constants.Others.Inactive
}).ToList());
}
}
public PharmaBusinessObjects.Master.CompanyMaster GetCompanyById(int companyId)
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
return(context.CompanyMaster.Where(p => p.CompanyId == companyId).Select(p => new PharmaBusinessObjects.Master.CompanyMaster()
{
CompanyId = p.CompanyId,
CompanyName = p.CompanyName,
OrderPreferenceRating = p.OrderPreferenceRating,
BillingPreferenceRating = p.BillingPreferenceRating,
CompanyCode = p.CompanyCode,
Status = p.Status,
IsDirect = p.IsDirect,
StockSummaryRequired = p.StockSummaryRequired,
StatusText = p.Status ? string.Empty : Constants.Others.Inactive
}).FirstOrDefault());
}
}
public List <PharmaBusinessObjects.Master.ItemMaster> GetAllItemByCompanyID(int CompanyID)
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
List <PharmaBusinessObjects.Master.ItemMaster> allItemByCompanyID = new List <PharmaBusinessObjects.Master.ItemMaster>();
allItemByCompanyID = context.ItemMaster.Where(item => item.CompanyID == CompanyID && item.Status)
.Select(item => new PharmaBusinessObjects.Master.ItemMaster()
{
CompanyID = CompanyID,
CompanyName = item.CompanyMaster.CompanyName,
ItemID = item.ItemID,
ItemName = item.ItemName
}).ToList();
return(allItemByCompanyID);
}
}
public bool UpdateRole(PharmaBusinessObjects.Master.Role role)
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
try
{
int result = 0;
Roles roles = context.Roles.Where(p => p.RoleId == role.RoleId).FirstOrDefault();
if (roles != null)
{
roles.RoleName = role.RoleName;
roles.Status = role.Status;
roles.CreatedBy = this.LoggedInUser.Username;
roles.CreatedOn = DateTime.Now;
}
result = context.SaveChanges();
var privledges = context.RolePrivledges.Where(p => p.RoleId == role.RoleId).ToList();
privledges.ForEach(p => context.RolePrivledges.Remove(p));
foreach (var item in role.PrivledgeList)
{
RolePrivledges priv = new RolePrivledges()
{
RoleId = role.RoleId,
PrivledgeId = item.PrivledgeId
};
}
result = result + context.SaveChanges();
return(result > 0);
}
catch (System.Data.Entity.Validation.DbEntityValidationException ex)
{
throw ex;
}
}
}
public SaleLineItemInfo GetSaleLineItemInfo(string itemCode, long FifoID)
{
SaleLineItemInfo info = new SaleLineItemInfo();
using (PharmaDBEntities context = new PharmaDBEntities())
{
SqlConnection connection = (SqlConnection)context.Database.Connection;
SqlCommand cmd = new SqlCommand("GetSaleLineItemInfo", connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ItemCode", itemCode));
cmd.Parameters.Add(new SqlParameter("@FifoID", FifoID));
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt != null && dt.Rows.Count > 0)
{
info.TaxAmount = Convert.IsDBNull(dt.Rows[0]["TaxOnSale"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["TaxOnSale"]);
info.Scheme1 = Convert.IsDBNull(dt.Rows[0]["Scheme1"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["Scheme1"]);
info.Scheme2 = Convert.IsDBNull(dt.Rows[0]["Scheme2"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["Scheme2"]);
info.Balance = Convert.IsDBNull(dt.Rows[0]["Balance"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["Balance"]);
info.IsHalf = Convert.IsDBNull(dt.Rows[0]["IsHalfScheme"]) ? false : Convert.ToBoolean(dt.Rows[0]["IsHalfScheme"]);
info.CaseQty = Convert.IsDBNull(dt.Rows[0]["QtyPerCase"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["QtyPerCase"]);
info.SaleType = Convert.ToString(dt.Rows[0]["AccountLedgerName"]);
info.Packing = Convert.ToString(dt.Rows[0]["Packing"]);
info.Surcharge = Convert.IsDBNull(dt.Rows[0]["SurchargeOnSale"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["SurchargeOnSale"]);
info.LastBillInfo = new LastBillInfo();
info.LastBillInfo.Batch = Convert.ToString(dt.Rows[0]["Batch"]);
info.LastBillInfo.Rate = Convert.IsDBNull(dt.Rows[0]["SaleRate"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["SaleRate"]);
info.LastBillInfo.Discount = Convert.IsDBNull(dt.Rows[0]["Discount"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["Discount"]);
info.LastBillInfo.SpecialDiscount = Convert.IsDBNull(dt.Rows[0]["SpecialDiscount"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["SpecialDiscount"]);
info.LastBillInfo.Scheme1 = Convert.IsDBNull(dt.Rows[0]["Scheme1"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["Scheme1"]);
info.LastBillInfo.Scheme2 = Convert.IsDBNull(dt.Rows[0]["Scheme2"]) ? 0 : Convert.ToDecimal(dt.Rows[0]["Scheme2"]);
info.LastBillInfo.BillDate = Convert.IsDBNull(dt.Rows[0]["DueDate"]) ? DateTime.MinValue : Convert.ToDateTime(dt.Rows[0]["DueDate"]);
}
}
return(info);
}
public List <PharmaBusinessObjects.Master.CustomerCopanyDiscount> GetExistigCompanyDiscountMappingByCustomerID(int customerLedgerID)
{
try
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
List <PharmaBusinessObjects.Master.CustomerCopanyDiscount> existingDiscountMapping = new List <PharmaBusinessObjects.Master.CustomerCopanyDiscount>();
existingDiscountMapping = context.CustomerCompanyDiscountRef.Where(q => q.CustomerLedgerID == customerLedgerID && q.CompanyMaster.Status && q.ItemID == null)
.Select(x => new PharmaBusinessObjects.Master.CustomerCopanyDiscount()
{
CompanyID = x.CompanyMaster.CompanyId,
CompanyName = x.CompanyMaster.CompanyName,
Normal = x.Normal,
Breakage = x.Breakage,
Expired = x.Expired
}).ToList();
///Isssue in a single linq
foreach (var item in existingDiscountMapping)
{
item.CustomerItemDiscountMapping = context.CustomerCompanyDiscountRef.Where(y => y.CompanyID == item.CompanyID && y.ItemID != null)
.Select(o => new PharmaBusinessObjects.Master.CustomerCopanyDiscount()
{
CompanyID = o.CompanyID,
CompanyName = o.CompanyMaster.CompanyName,
ItemID = o.ItemID,
ItemName = o.ItemMaster.ItemName,
Normal = o.Normal,
Breakage = o.Breakage,
Expired = o.Expired
}).ToList();
}
return(existingDiscountMapping);
}
}
catch (Exception ex)
{
throw ex;
}
}
public List <PharmaBusinessObjects.Transaction.PurchaseSaleBookLineItem> GetLastNBatchNoForSupplierItem(string supplierCode, string itemCode)
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
return(context.PurchaseSaleBookLineItem.Where(p => p.PurchaseSaleBookHeader.LedgerTypeCode == supplierCode & p.ItemCode == itemCode)
.Select(p => new PharmaBusinessObjects.Transaction.PurchaseSaleBookLineItem()
{
PurchaseSaleBookLineItemID = p.PurchaseSaleBookLineItemID,
ItemCode = p.ItemCode,
PurchaseSaleRate = p.PurchaseSaleRate,
OldPurchaseSaleRate = p.PurchaseSaleRate,
Discount = p.Discount,
SpecialDiscount = p.SpecialDiscount,
VolumeDiscount = p.VolumeDiscount,
PurchaseSaleTax = p.SalePurchaseTax,
PurchaseBillDate = p.PurchaseSaleBookHeader.VoucherDate,
Batch = p.Batch,
ExpiryDate = p.ExpiryDate
}).OrderByDescending(p => p.PurchaseSaleBookLineItemID).Take(5).ToList());
}
}
public int UpdateCompany(PharmaBusinessObjects.Master.CompanyMaster company)
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
var companyMaster = context.CompanyMaster.FirstOrDefault(p => p.CompanyId == company.CompanyId);
if (companyMaster != null)
{
companyMaster.Status = company.Status;
companyMaster.StockSummaryRequired = company.StockSummaryRequired;
companyMaster.IsDirect = company.IsDirect;
companyMaster.OrderPreferenceRating = company.OrderPreferenceRating;
companyMaster.BillingPreferenceRating = company.BillingPreferenceRating;
companyMaster.CompanyName = company.CompanyName;
companyMaster.ModifiedBy = this.LoggedInUser.Username;
companyMaster.ModifiedOn = System.DateTime.Now;
}
return(context.SaveChanges());
}
}
public int AddUser(PharmaBusinessObjects.Master.UserMaster p)
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
Entity.Users table = new Entity.Users()
{
Username = p.Username,
Password = p.Password,
FirstName = p.FirstName,
LastName = p.LastName,
RoleID = p.RoleID,
IsSysAdmin = p.IsSystemAdmin,
CreatedBy = this.LoggedInUser.Username,
CreatedOn = System.DateTime.Now,
Status = p.Status
};
context.Users.Add(table);
return(context.SaveChanges());
}
}
public bool AddRole(PharmaBusinessObjects.Master.Role role)
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
try
{
int result = 0;
if (context.Roles.Any(p => p.RoleName.ToLower() == role.RoleName.ToLower()))
{
throw new Exception("Role already exists");
}
Roles roles = new Roles()
{
RoleName = role.RoleName,
Status = role.Status,
CreatedBy = this.LoggedInUser.Username,
CreatedOn = DateTime.Now
};
context.Roles.Add(roles);
result = context.SaveChanges();
foreach (Privledge item in role.PrivledgeList)
{
RolePrivledges priv = new RolePrivledges();
priv.RoleId = roles.RoleId;
priv.PrivledgeId = item.PrivledgeId;
context.RolePrivledges.Add(priv);
}
result = result + context.SaveChanges();
return(result > 0);
}
catch (System.Data.Entity.Validation.DbEntityValidationException ex)
{
throw ex;
}
}
}
public List <PharmaBusinessObjects.Transaction.PurchaseSaleBookLineItem> DeleteSaleLineItem(int saleBookHeaderID, int saleBookLineItemID)
{
List <PharmaBusinessObjects.Transaction.PurchaseSaleBookLineItem> itemList = new List <PharmaBusinessObjects.Transaction.PurchaseSaleBookLineItem>();
using (PharmaDBEntities context = new PharmaDBEntities())
{
SqlConnection connection = (SqlConnection)context.Database.Connection;
SqlCommand cmd = new SqlCommand("DeleteSaleLineItem", connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("PurchaseSaleBookHeaderID", saleBookHeaderID));
cmd.Parameters.Add(new SqlParameter("TempPurchaseSaleBookLineItemID", saleBookLineItemID));
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
PharmaBusinessObjects.Transaction.PurchaseSaleBookLineItem item = new PharmaBusinessObjects.Transaction.PurchaseSaleBookLineItem();
item.PurchaseSaleBookLineItemID = Convert.ToInt32(row["PurchaseSaleBookLineItemID"]);
item.PurchaseSaleBookHeaderID = Convert.ToInt32(row["PurchaseSaleBookHeaderID"]);
item.SchemeAmount = Convert.IsDBNull(row["SchemeAmount"]) ? 0 : Convert.ToDecimal(row["SchemeAmount"]);
item.GrossAmount = Convert.IsDBNull(row["GrossAmount"]) ? 0 : Convert.ToDecimal(row["GrossAmount"]);
item.TaxAmount = Convert.IsDBNull(row["TaxAmount"]) ? 0 : Convert.ToDecimal(row["TaxAmount"]);
item.TotalDiscountAmount = Convert.IsDBNull(row["DiscountAmount"]) ? 0 : Convert.ToDecimal(row["DiscountAmount"]);
item.CostAmount = Convert.IsDBNull(row["BillAmount"]) ? 0 : Convert.ToDecimal(row["BillAmount"]);
itemList.Add(item);
}
}
}
return(itemList);
}
public int AddPersonRoute(PharmaBusinessObjects.Master.PersonRouteMaster p)
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
try
{
var maxPersonRouteMasterID = context.PersonRouteMaster.Where(q => q.RecordTypeId == p.RecordTypeId).Count() + 1;
var systemName = context.RecordType.Where(q => q.RecordTypeId == p.RecordTypeId).FirstOrDefault().SystemName;
var personRouteCode = systemName + maxPersonRouteMasterID.ToString().PadLeft(3, '0');
Entity.PersonRouteMaster table = new Entity.PersonRouteMaster()
{
PersonRouteID = p.PersonRouteID,
PersonRouteCode = personRouteCode,
PersonRouteName = p.PersonRouteName,
RecordTypeId = p.RecordTypeId,
CreatedBy = this.LoggedInUser.LastName,
CreatedOn = System.DateTime.Now,
Status = p.Status
};
context.PersonRouteMaster.Add(table);
if (context.SaveChanges() > 0)
{
return(table.PersonRouteID);
}
else
{
return(0);
}
}
catch (System.Data.Entity.Validation.DbEntityValidationException ex)
{
throw ex;
}
}
}
public List <PharmaBusinessObjects.Master.PersonRouteMaster> GetPersonRoutesByRecordTypeIdAndSearch(int recordTypeID, string searchString = null)
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
var personRoutes = (from p in context.PersonRouteMaster
where (recordTypeID == 0 || p.RecordTypeId == recordTypeID) &&
(string.IsNullOrEmpty(searchString) || p.PersonRouteName.Contains(searchString))
select new PharmaBusinessObjects.Master.PersonRouteMaster()
{
PersonRouteID = p.PersonRouteID,
PersonRouteCode = p.PersonRouteCode,
PersonRouteName = p.PersonRouteName,
RecordTypeId = p.RecordTypeId,
RecordTypeNme = p.RecordType.RecordType1,
Status = p.Status,
SystemName = p.RecordType.SystemName,
StatusText = p.Status ? string.Empty : Constants.Others.Inactive
}).ToList();
return(personRoutes);
}
}
public PharmaBusinessObjects.Master.UserMaster GetUserByUserId(int userid)
{
using (PharmaDBEntities context = new PharmaDBEntities())
{
return(context.Users.Where(p => p.UserId == userid).Select(p => new PharmaBusinessObjects.Master.UserMaster()
{
UserId = p.UserId,
Username = p.Username,
Password = p.Password,
FirstName = p.FirstName,
LastName = p.LastName,
RoleID = p.RoleID ?? 0,
RoleName = p.Roles.RoleName,
IsSystemAdmin = p.IsSysAdmin,
CreatedBy = p.CreatedBy,
CreatedOn = p.CreatedOn,
ModifiedBy = p.ModifiedBy,
ModifiedOn = p.ModifiedOn,
Status = p.Status
}).FirstOrDefault());
}
}
