public string Save(Models.SaleTax _s, int ResellerId = 0)
{
try
{
var db = new Context.ConnectionStringsContext();
if (_s.Id != 0 && db.SaleTax.Any(m => m.Id == _s.Id)) //Update
{
var saletax = db.SaleTax.Where(m => m.Id == _s.Id).SingleOrDefault();
saletax.Amount = _s.Amount;
saletax.ResellerId = ResellerId;
saletax.Country = _s.Country;
saletax.State = _s.State;
}
else //Save
{
Models.SaleTax saletax = new Models.SaleTax
{
Amount = _s.Amount,
Status = true,
ResellerId = ResellerId,
Country = _s.Country,
State = _s.State,
};
db.SaleTax.Add(saletax);
}
db.SaveChanges();
return("Successully sale tax has been saved.");
}
catch (Exception ex)
{
return("Unknown error occur, Please try again.");
}
}
public void UpdateResellerPrice(int Id, GridPriceList priceList)
{
try
{
var db = new Context.ConnectionStringsContext();
//if (db.ResellerPrice.Any(m => m.PriceId == priceList.Id && m.ResellerId == Id && m.Status == true))
//{
// var rcp = db.ResellerPrice.Where(m => m.PriceId == priceList.Id && m.ResellerId == Id).SingleOrDefault();
// rcp.Price = priceList.ResellerPrice;
//}
//else
//{
Models.ResellerPrice rcp = new Models.ResellerPrice()
{
PriceId = priceList.Id,
Price = priceList.ResellerPrice,
ResellerId = Id,
Status = true,
};
db.ResellerPrice.Add(rcp);
//}
db.SaveChanges();
}
catch
{
}
}
public IEnumerable <Models.SaleTaxRepo> Get(int ResellerId = 0)
{
try
{
var db = new Context.ConnectionStringsContext();
var data = (from s in db.SaleTax
join c in db.Countries
on s.Country equals c.Code
where s.Status == true && c.Status == true
select new
{
s.Amount,
s.State,
Country = c.CountryName,
CountryCode = c.Code,
s.Id
}).OrderBy(m => m.Country).OrderBy(m => m.State);
IEnumerable <Models.SaleTaxRepo> list = data.ToList().Select(r => new Models.SaleTaxRepo
{
Amount = r.Amount,
Country = r.Country,
CountryCode = r.CountryCode,
State = r.State,
SaleTaxId = r.Id
});
return(list);
}
catch
{
return(new List <Models.SaleTaxRepo>());
}
}
public Models.SaleTax Get(string Country, string state, int ResellerId = 0)
{
try
{
var db = new Context.ConnectionStringsContext();
var data = (from s in db.SaleTax
join c in db.Countries
on s.Country equals c.Code
where s.Status == true && c.Status == true &&
s.Country == Country && s.State.ToLower() == state.ToLower()
select new
{
s.Amount,
s.State,
Country = c.CountryName,
CountryCode = c.Code,
s.Id
}).SingleOrDefault();
Models.SaleTax list = new Models.SaleTax
{
Amount = data.Amount,
Country = data.Country,
State = data.State,
Id = data.Id
};
return(list);
}
catch
{
return(new Models.SaleTax());
}
}
public void AddOffersSetup(Models.OffersSetup OffersSetup)
{
try
{
var db = new Context.ConnectionStringsContext();
if (!db.OffersSetup.Any(m => m.MicrosoftOfferId == OffersSetup.MicrosoftOfferId))
{
OffersSetup.CreatedDate = DateTime.Now;
OffersSetup.AppliedDate = DateTime.Now;
OffersSetup.Status = true;
db.OffersSetup.Add(OffersSetup);
}
else
{
var _o = db.OffersSetup.Where(m => m.MicrosoftOfferId == OffersSetup.MicrosoftOfferId).SingleOrDefault();
_o.Features = OffersSetup.Features;
_o.Price = OffersSetup.Price;
_o.SubTitle = OffersSetup.SubTitle;
_o.Summary = OffersSetup.Summary;
_o.Title = OffersSetup.Title;
}
db.SaveChanges();
}
catch
{
}
}
//List<Models.MicrosoftPriceList>
public List <Models.ExcelPriceList> GetPriceList()
{
try
{
var db = new Context.ConnectionStringsContext();
//var obj = (from n in db.MicrosoftPriceList
// group n by n.MicrosoftId into g
// select new { MicrosoftId = g.Key, StartDate = g.Max(t => t.StartDate) }).ToList() ;
//return obj;
//return db.MicrosoftPriceList.OrderByDescending(T => T.StartDate).Take(1).ToList();
var query = (from p in db.MicrosoftPriceList
where p.Status == true
group p by p.MicrosoftId into op
select new
{
MicrosoftId = op.Key,
Name = op.Max(x => x.Name),
StartDate = op.Max(x => x.StartDate),
EndDate = op.Max(x => x.EndDate),
Price = op.Max(x => x.Price),
//ResellerPrice = op.Max(x => x.ResellerPrice),
ResellerPrice = op.Max(x => x.ResellerPrice) == 0 ? op.Max(x => x.CustomerPrice) - (op.Max(x => x.CustomerPrice) * (db.DefaultMargin.Where(m => m.ResellerId == 0 && m.Role == (int)Roles.Resellers).FirstOrDefault().DefaultPercentage)) / 100 : op.Max(x => x.ResellerPrice),
CustomerPrice = op.Max(x => x.CustomerPrice),
AgreementType = op.Max(x => x.AgreementType),
CustomerType = op.Max(x => x.CustomerType),
LicenseType = op.Max(x => x.LicenseType),
PurchaseUnit = op.Max(x => x.PurchaseUnit),
PurchaseUnitNumber = op.Max(x => x.PurchaseUnitNumber),
Id = op.Max(x => x.Id),
}).ToList();
List <Models.ExcelPriceList> mic = query.ToList().Select(r => new Models.ExcelPriceList
{
EndDate = r.EndDate,
Id = r.Id,
MicrosoftId = r.MicrosoftId,
Name = r.Name,
StartDate = r.StartDate,
Price = r.Price,
AgreementType = r.AgreementType,
CustomerType = r.CustomerType,
LicenseType = r.LicenseType,
ResellerPrice = r.ResellerPrice,
CustomerPrice = r.CustomerPrice,
PurchaseUnit = r.PurchaseUnit,
PurchaseUnitNumber = r.PurchaseUnitNumber,
}).ToList();
return(mic);
}
catch (Exception ex)
{
return(new List <Models.ExcelPriceList>());
}
}
public List <Models.Customers> Get(int ResellerId)
{
try
{
var db = new Context.ConnectionStringsContext();
return(db.Customers.Where(m => m.Status == true && m.ResellerId == ResellerId).ToList());
}
catch
{
return(new List <Models.Customers>());
}
}
//Get customers from db
public IEnumerable <Models.Customers> GetCustomers()
{
try
{
var db = new Context.ConnectionStringsContext();
return(db.Customers.Where(m => m.Status == true).ToList());
}
catch
{
return(new List <Models.Customers>());
}
}
//Get customerby id from db
public Models.Customers GetCustomerById(string Id)
{
try
{
var db = new Context.ConnectionStringsContext();
return(db.Customers.Where(m => m.Status == true && m.MicrosoftId == Id).SingleOrDefault());
}
catch
{
return(new Models.Customers());
}
}
public string Save(Models.InvoiceSetting _i, int ResellerId)
{
try
{
var db = new Context.ConnectionStringsContext();
if (_i.Id != 0 && db.InvoiceSetting.Any(m => m.Id == _i.Id)) //Update
{
var invset = db.InvoiceSetting.Where(m => m.Id == _i.Id).FirstOrDefault();
invset.ExpiryDateOfInvoice = _i.ExpiryDateOfInvoice;
invset.InvoiceNotes = _i.InvoiceNotes;
invset.IsShowCurrency = _i.IsShowCurrency;
invset.IsShowExpiryDate = _i.IsShowExpiryDate;
invset.IsShowIssueDate = _i.IsShowIssueDate;
invset.IsShowLogo = _i.IsShowLogo;
invset.IsShowNotes = _i.IsShowNotes;
invset.IsShowPOBox = _i.IsShowPOBox;
invset.AccNo = _i.AccNo;
invset.IBAN = _i.IBAN;
invset.SWIFT = _i.SWIFT;
invset.IsShowAccNo = _i.IsShowAccNo;
invset.IsShowIBAN = _i.IsShowIBAN;
invset.IsShowSWIFT = _i.IsShowSWIFT;
}
else //save
{
Models.InvoiceSetting invset = new Models.InvoiceSetting
{
ExpiryDateOfInvoice = _i.ExpiryDateOfInvoice,
InvoiceNotes = _i.InvoiceNotes,
IsShowCurrency = _i.IsShowCurrency,
IsShowExpiryDate = _i.IsShowExpiryDate,
IsShowIssueDate = _i.IsShowIssueDate,
IsShowLogo = _i.IsShowLogo,
IsShowNotes = _i.IsShowNotes,
IsShowPOBox = _i.IsShowPOBox,
AccNo = _i.AccNo,
IBAN = _i.IBAN,
SWIFT = _i.SWIFT,
IsShowAccNo = _i.IsShowAccNo,
IsShowIBAN = _i.IsShowIBAN,
IsShowSWIFT = _i.IsShowSWIFT,
};
db.InvoiceSetting.Add(invset);
}
db.SaveChanges();
return("Successfully Invoice setting has been saved.");
}
catch (Exception ex)
{
return("Unknown error occur, Please try again.");
}
}
public Models.PromotionCodes GetPromoCodeById(int?id)
{
try
{
var db = new Context.ConnectionStringsContext();
return(db.PromotionCodes.Where(m => m.Id == id).SingleOrDefault());
}
catch (Exception ex)
{
return(new Models.PromotionCodes());
}
}
public void DeletePromoCode(int id)
{
try
{
var db = new Context.ConnectionStringsContext();
db.PromotionCodes.Where(m => m.Id == id).SingleOrDefault().Status = false;
db.SaveChanges();
}
catch
{
}
}
public Models.Reseller GetById(int?id)
{
try
{
var db = new Context.ConnectionStringsContext();
Models.Reseller reseller = db.Resellers.Where(m => m.Id == id).SingleOrDefault();
return(reseller);
}
catch (Exception ex)
{
return(new Models.Reseller());
}
}
public virtual string AddDefaultMargin(Models.DefaultMargin d)
{
try
{
var db = new Context.ConnectionStringsContext();
db.DefaultMargin.Add(d);
db.SaveChanges();
return("Successfully Saved");
}
catch (Exception ex)
{
return("Unknown error occur, Please try again.");
}
}
public virtual string AddTermAndConditions(Models.TermsConditions tc)
{
try
{
var db = new Context.ConnectionStringsContext();
db.TermsConditions.Add(tc);
db.SaveChanges();
return("Successfully Saved");
}
catch (Exception ex)
{
return("Unknown error occur, Please try again.");
}
}
public virtual string CheckIfExist(Models.TermsConditions tc, int ResellerId = 0)
{
tc.CreatedDate = DateTime.Now;
var db = new Context.ConnectionStringsContext();
if (db.TermsConditions.Any(m => m.Role == (int)tc.Role && m.ResellerId == ResellerId)) //Update
{
return(EditTermsAndConditions(tc));
}
else //Add
{
return(AddTermAndConditions(tc));
}
}
public virtual string CheckIfExist(Models.DefaultMargin d, int ResellerId = 0)
{
d.CreatedDate = DateTime.Now;
var db = new Context.ConnectionStringsContext();
if (db.DefaultMargin.Any(m => m.Role == (int)d.Role && m.ResellerId == ResellerId)) //Update
{
return(EditDefaultMargin(d));
}
else //Add
{
return(AddDefaultMargin(d));
}
}
public string Reset(int Id)
{
try
{
var db = new Context.ConnectionStringsContext();
db.ResellerPrice.Where(m => m.Status == true && m.ResellerId == Id).ToList().ForEach(c => c.Status = false);
db.SaveChanges();
return("Reset");
}
catch
{
return("Error");
}
}
//Find the buying price, ERP Price, Reseller Price
public List <Models.MicrosoftPriceList> Get()
{
try
{
var db = new Context.ConnectionStringsContext();
var query = (from p in db.MicrosoftPriceList
where p.Status == true
group p by p.MicrosoftId into op
select new
{
MicrosoftId = op.Key,
Price = op.Max(x => x.Price),
ResellerPrice = (db.ResellerCustomersPrice.Any(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == 0 && m.ResellerPrice != null)) ?
db.ResellerCustomersPrice.Where(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == 0).FirstOrDefault().ResellerPrice : 0,
CustomerPrice = (db.ResellerCustomersPrice.Any(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == 0 && m.CustomerPrice != null)) ?
db.ResellerCustomersPrice.Where(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == 0).FirstOrDefault().CustomerPrice
: op.Max(x => x.CustomerPrice),
PurchaseUnit = op.Max(x => x.PurchaseUnit),
PurchaseUnitNumber = op.Max(x => x.PurchaseUnitNumber),
Id = op.Max(x => x.Id),
DefaultMarginReseller = db.DefaultMargin.Where(m => m.ResellerId == 0 && m.Role == (int)Roles.Resellers).FirstOrDefault().DefaultPercentage
//Price = op.Max(x => x.Price),
//ResellerPrice = op.Max(x => x.ResellerPrice) == 0 ? op.Max(x => x.CustomerPrice) - (op.Max(x => x.CustomerPrice) * (db.DefaultMargin.Where(m => m.ResellerId == 0 && m.Role == (int)Roles.Resellers).FirstOrDefault().DefaultPercentage)) / 100 : op.Max(x => x.ResellerPrice),
//CustomerPrice = op.Max(x => x.CustomerPrice),
//PurchaseUnit = op.Max(x => x.PurchaseUnit),
//PurchaseUnitNumber = op.Max(x => x.PurchaseUnitNumber),
//Id = op.Max(x => x.Id),
}).ToList();
List <Models.MicrosoftPriceList> mic = query.ToList().Select(r => new Models.MicrosoftPriceList
{
Id = r.Id,
MicrosoftId = r.MicrosoftId,
Price = r.Price,
ResellerPrice = (double)r.ResellerPrice != 0 ? (double)r.ResellerPrice : (r.Price + ((r.CustomerPrice - r.Price) * r.DefaultMarginReseller) / 100),
CustomerPrice = r.CustomerPrice,
PurchaseUnit = r.PurchaseUnit,
PurchaseUnitNumber = r.PurchaseUnitNumber,
}).ToList();
return(mic);
}
catch
{
return(new List <Models.MicrosoftPriceList>());
}
}
public virtual string EditDefaultMargin(Models.DefaultMargin d)
{
try
{
var db = new Context.ConnectionStringsContext();
Models.DefaultMargin defaultMargin = db.DefaultMargin.Where(m => m.Id == d.Id).SingleOrDefault();
defaultMargin.DefaultPercentage = d.DefaultPercentage;
db.SaveChanges();
return("Successfully Updated");
}
catch (Exception ex)
{
return("Unknown error occur, Please try again.");
}
}
public object GetCountries()
{
try
{
var db = new Context.ConnectionStringsContext();
return(db.Countries.Where(m => m.Status == true).ToList());
//return (from c in db.Countries
// where c.Status == true
// select new { Text = c.CountryName, Value = c.Code }).ToList();
}
catch
{
throw new Exception("Unknown error occur, Please try again.");
}
}
public virtual string EditTermsAndConditions(Models.TermsConditions tc)
{
try
{
var db = new Context.ConnectionStringsContext();
Models.TermsConditions termsConditions = db.TermsConditions.Where(m => m.Id == tc.Id).SingleOrDefault();
termsConditions.Info = tc.Info;
db.SaveChanges();
return("Successfully Updated");
}
catch (Exception ex)
{
return("Unknown error occur, Please try again.");
}
}
//Delete the request reseller (Soft deletion)
public bool DeleteRequest(int id)
{
var db = new Context.ConnectionStringsContext();
try
{
db.Customers.Where(m => m.Id == id).SingleOrDefault().Status = false;
db.SaveChanges();
return(true);
}
catch
{
return(false);
}
}
public Models.PromotionCodes GetPromoCodeByCoode(string code)
{
try
{
var db = new Context.ConnectionStringsContext();
return(db.PromotionCodes.Where(m => m.Code == code && m.IsApplied == false && m.Status == true && DbFunctions.TruncateTime(m.ExpiryDate).Value >= DateTime.Now).SingleOrDefault());
//return (from p in db.PromotionCodes
// where p.Code == code && p.IsApplied ==false
// && p.Status == true
// && DbFunctions.TruncateTime(o.Date).Value >= PreviusDate.Date)
}
catch (Exception ex)
{
return(new Models.PromotionCodes());
}
}
public Models.InvoiceSetting Get(int ResellerId)
{
try
{
var db = new Context.ConnectionStringsContext();
if (!db.InvoiceSetting.Any(m => m.ResellerId == ResellerId))
{
return(EmptyInvoiceSetting());
}
return(db.InvoiceSetting.Where(m => m.ResellerId == ResellerId).FirstOrDefault());
}
catch
{
return(EmptyInvoiceSetting());
}
}
public Models.Company Get(int ResellerId)
{
try
{
var db = new Context.ConnectionStringsContext();
if (!db.Company.Any(m => m.ResellerId == ResellerId))
{
return(EmptyCompany());
}
return(db.Company.Where(m => m.ResellerId == ResellerId).FirstOrDefault());
}
catch
{
return(EmptyCompany());
}
}
public string Delete(int id, bool IsDelete)
{
try
{
var db = new Context.ConnectionStringsContext();
if (db.Resellers.Any(m => m.Id == id))
{
db.Resellers.Where(m => m.Id == id).SingleOrDefault().Status = IsDelete ? false : true;
db.SaveChanges();
}
return(IsDelete ? "Deleted" : "Activate");
}
catch (Exception ex)
{
return("Unknown error occur, Please try again.");
}
}
public List <Models.MicrosoftPriceList> GetPriceList(int ResellerId, string Id)
{
try
{
var db = new Context.ConnectionStringsContext();
var query = (from p in db.MicrosoftPriceList
where p.Status == true && p.MicrosoftId == Id
group p by p.MicrosoftId into op
select new
{
MicrosoftId = op.Key,
Price = op.Max(x => x.Price),
ResellerPrice = db.ResellerPrice.Any(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == ResellerId && m.Status == true) ?
db.ResellerPrice.Where(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == ResellerId && m.Status == true).OrderByDescending(m => m.Id).FirstOrDefault().Price :
(db.ResellerCustomersPrice.Any(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == 0 && m.ResellerPrice != null))
? db.ResellerCustomersPrice.Where(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == 0).OrderByDescending(m => m.Id).FirstOrDefault().ResellerPrice : 0,
CustomerPrice = (db.CustomerPrice.Any(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == ResellerId))
? (db.CustomerPrice.Where(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == ResellerId)).OrderByDescending(m => m.Id).FirstOrDefault().Price
: (db.ResellerCustomersPrice.Any(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == ResellerId && m.CustomerPrice != null)) ?
db.ResellerCustomersPrice.Where(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == ResellerId).OrderByDescending(m => m.Id).FirstOrDefault().CustomerPrice
: op.Max(x => x.CustomerPrice),
PurchaseUnit = op.Max(x => x.PurchaseUnit),
PurchaseUnitNumber = op.Max(x => x.PurchaseUnitNumber),
Id = op.Max(x => x.Id),
DefaultMarginReseller = db.Resellers.Where(m => m.Id == ResellerId).FirstOrDefault().Margin //get the margin from reseller table
}).ToList();
List <Models.MicrosoftPriceList> mic = query.ToList().Select(r => new Models.MicrosoftPriceList
{
Id = r.Id,
MicrosoftId = r.MicrosoftId,
Price = r.Price,
ResellerPrice = (double)r.ResellerPrice != 0 ? (double)r.ResellerPrice : (r.Price + ((r.CustomerPrice - r.Price) * r.DefaultMarginReseller) / 100),
CustomerPrice = r.CustomerPrice,
PurchaseUnit = r.PurchaseUnit,
PurchaseUnitNumber = r.PurchaseUnitNumber,
}).ToList();
return(mic);
}
catch
{
return(new List <Models.MicrosoftPriceList>());
}
}
//Get Price List by Id
public Models.MicrosoftPriceList GetPriceById(string id)
{
try
{
var db = new Context.ConnectionStringsContext();
var query = (from p in db.MicrosoftPriceList
where p.Status == true && p.MicrosoftId == id
group p by p.MicrosoftId into op
select new
{
MicrosoftId = op.Key,
Price = op.Max(x => x.Price),
//ResellerPrice = op.Max(x => x.ResellerPrice),
//ResellerPrice = op.Max(x => x.ResellerPrice) == 0 ? op.Max(x => x.CustomerPrice) - (op.Max(x => x.CustomerPrice) * (db.DefaultMargin.Where(m => m.ResellerId == 0 && m.Role == (int)Roles.Resellers).FirstOrDefault().DefaultPercentage)) / 100 : op.Max(x => x.ResellerPrice),
ResellerPrice = (db.ResellerCustomersPrice.Any(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == 0 && m.ResellerPrice != null)) ?
db.ResellerCustomersPrice.Where(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == 0).FirstOrDefault().ResellerPrice : 0,
CustomerPrice = (db.ResellerCustomersPrice.Any(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == 0 && m.CustomerPrice != null)) ?
db.ResellerCustomersPrice.Where(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == 0).FirstOrDefault().CustomerPrice
: op.Max(x => x.CustomerPrice), //op.Max(x => x.CustomerPrice),
PurchaseUnit = op.Max(x => x.PurchaseUnit),
PurchaseUnitNumber = op.Max(x => x.PurchaseUnitNumber),
Id = op.Max(x => x.Id),
DefaultMarginReseller = db.DefaultMargin.Where(m => m.ResellerId == 0 && m.Role == (int)Roles.Resellers).FirstOrDefault().DefaultPercentage
}).SingleOrDefault();
Models.MicrosoftPriceList mic = new Models.MicrosoftPriceList
{
Id = query.Id,
MicrosoftId = query.MicrosoftId,
Price = query.Price,
ResellerPrice = (double)query.ResellerPrice != 0 ? (double)query.ResellerPrice : (query.CustomerPrice - (query.CustomerPrice * query.DefaultMarginReseller) / 100),
CustomerPrice = query.CustomerPrice,
PurchaseUnit = query.PurchaseUnit,
PurchaseUnitNumber = query.PurchaseUnitNumber,
};
return(mic);
}
catch
{
return(new Models.MicrosoftPriceList());
}
}
public Models.OffersSetup GetOfferByMicrosoftOfferId(string Id)
{
try
{
var db = new Context.ConnectionStringsContext();
if (db.OffersSetup.Any(m => m.MicrosoftOfferId == Id))
{
return(db.OffersSetup.Where(m => m.MicrosoftOfferId == Id).SingleOrDefault());
}
else
{
return(new Models.OffersSetup());
}
}
catch
{
return(new Models.OffersSetup());
}
}
