public void SendWeeklyGwpFile()
{
List <GrossWrittenPremiumReportModels> ListGrossWrittenPremiumReport = new List <GrossWrittenPremiumReportModels>();
ListGrossWrittenPremiumReportModels _ListGrossWrittenPremiumReport = new ListGrossWrittenPremiumReportModels();
_ListGrossWrittenPremiumReport.ListGrossWrittenPremiumReportdata = new List <GrossWrittenPremiumReportModels>();
var yesterdayDate = DateTime.Now.AddDays(-1);
var query = " select PolicyDetail.PolicyNumber as Policy_Number, Customer.ALMId, case when Customer.ALMId is null then [dbo].fn_GetUserCallCenterAgent(SummaryDetail.CreatedBy) else [dbo].fn_GetUserALM(Customer.BranchId) end as PolicyCreatedBy, Customer.FirstName + ' ' + Customer.LastName as Customer_Name,VehicleDetail.TransactionDate as Transaction_date, ";
query += " case when Customer.id=SummaryDetail.CreatedBy then [dbo].fn_GetUserBranch(Customer.id) else [dbo].fn_GetUserBranch(SummaryDetail.CreatedBy) end as BranchName, ";
query += " VehicleDetail.CoverNote as CoverNoteNum, PaymentMethod.Name as Payment_Mode, PaymentTerm.Name as Payment_Term,CoverType.Name as CoverType, Currency.Name as Currency, ";
query += " VehicleDetail.Premium + VehicleDetail.StampDuty + VehicleDetail.ZTSCLevy as Premium_due, VehicleDetail.StampDuty as Stamp_duty, VehicleDetail.ZTSCLevy as ZTSC_Levy, ";
query += " cast(VehicleDetail.Premium * 30 / 100 as decimal(10, 2)) as Comission_Amount, VehicleDetail.IncludeRadioLicenseCost, ";
query += " CASE WHEN IncludeRadioLicenseCost = 1 THEN VehicleDetail.RadioLicenseCost else 0 end as RadioLicenseCost, VehicleDetail.VehicleLicenceFee as Zinara_License_Fee, ";
query += " VehicleDetail.RenewalDate as PolicyRenewalDate, VehicleDetail.IsActive, VehicleDetail.RenewPolicyNumber as RenewPolicyNumber, ";
query += " VehicleDetail.BusinessSourceDetailId, SummaryDetail.id as SummaryDetailId, BusinessSource.Source as BusinessSourceName, SourceDetail.FirstName + ' ' + SourceDetail.LastName as SourceDetailName from PolicyDetail ";
query += " join Customer on PolicyDetail.CustomerId = Customer.Id ";
query += " join VehicleDetail on PolicyDetail.Id = VehicleDetail.PolicyId ";
query += "join SummaryVehicleDetail on VehicleDetail.id = SummaryVehicleDetail.VehicleDetailsId ";
query += " join SummaryDetail on SummaryDetail.id = SummaryVehicleDetail.SummaryDetailId ";
//query += " join PaymentInformation on SummaryDetail.Id=PaymentInformation.SummaryDetailId ";
query += " join PaymentMethod on SummaryDetail.PaymentMethodId = PaymentMethod.Id ";
query += "join PaymentTerm on VehicleDetail.PaymentTermId = PaymentTerm.Id ";
query += " left join CoverType on VehicleDetail.CoverTypeId = CoverType.Id ";
query += " left join Currency on VehicleDetail.CurrencyId = Currency.Id ";
query += " left join BusinessSource on BusinessSource.Id = VehicleDetail.BusinessSourceDetailId ";
query += " left join SourceDetail on VehicleDetail.BusinessSourceDetailId = SourceDetail.Id join AspNetUsers on AspNetUsers.id=customer.UserID join AspNetUserRoles on AspNetUserRoles.UserId=AspNetUsers.Id ";
query += " where (VehicleDetail.IsActive = 1 or VehicleDetail.IsActive = null) and SummaryDetail.isQuotation=0 and SummaryDetail.PaymentMethodId <>" + (int)paymentMethod.PayLater + " and CONVERT(date, VehicleDetail.TransactionDate) = convert(date, '" + yesterdayDate.ToString("MM/dd/yyyy") + "', 101) order by VehicleDetail.Id desc ";
Debug.WriteLine("***********************");
Debug.WriteLine(yesterdayDate.ToString("MM/dd/yyyy"));
Debug.WriteLine("***********************");
try {
ListGrossWrittenPremiumReport = InsuranceContext.Query(query).
Select(x => new GrossWrittenPremiumReportModels()
{
Policy_Number = x.Policy_Number,
BranchName = x.BranchName,
PolicyCreatedBy = x.PolicyCreatedBy,
Customer_Name = x.Customer_Name,
Transaction_date = x.Transaction_date.ToShortDateString(),
CoverNoteNum = x.CoverNoteNum,
Payment_Mode = x.Payment_Mode,
Payment_Term = x.Payment_Term,
CoverType = x.CoverType,
Currency = x.Currency,
Premium_due = x.Premium_due,
Stamp_duty = x.Stamp_duty,
ZTSC_Levy = x.ZTSC_Levy,
ALMId = x.ALMId,
Comission_Amount = x.Comission_Amount,
RadioLicenseCost = x.RadioLicenseCost,
Zinara_License_Fee = x.Zinara_License_Fee,
PolicyRenewalDate = x.PolicyRenewalDate,
IsActive = x.IsActive,
RenewPolicyNumber = x.RenewPolicyNumber,
}).ToList();
List <BranchModel> obj = InsuranceContext.Query("select * from Branch where id != 6").Select(x => new BranchModel
{
Id = x.Id,
BranchName = x.BranchName
}).ToList();
List <ZinaraReportModel> reportModelsList = new List <ZinaraReportModel>();
obj.ForEach(x =>
{
ZinaraReportModel model = new ZinaraReportModel();
var count = ListGrossWrittenPremiumReport.FindAll(p => p.BranchName == x.BranchName).Count();
var total = ListGrossWrittenPremiumReport.Where(p => p.BranchName == x.BranchName).Sum(item => item.Zinara_License_Fee);
// var zinaraAmount = total.Sum(item => item.Zinara_License_Fee);
model.BranchName = x.BranchName;
model.count = count;
model.ZinaraAmount = total;
reportModelsList.Add(model);
});
GenerateExcel(reportModelsList);
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
}
public void SendWeeklyReport()
{
var dtOneMonthBack = DateTime.Now;
int year = dtOneMonthBack.Year;
int month = dtOneMonthBack.Month;
string firstWeekStart = dtOneMonthBack.ToString("MM") + "/01/" + DateTime.Now.Year.ToString();
string firstWeekEnd = dtOneMonthBack.ToString("MM") + "/07/" + DateTime.Now.Year.ToString();
string secondWeekStart = dtOneMonthBack.ToString("MM") + "/08/" + DateTime.Now.Year.ToString();
string secondWeekEnd = dtOneMonthBack.ToString("MM") + "/14/" + DateTime.Now.Year.ToString();
string thirdWeekStart = dtOneMonthBack.ToString("MM") + "/15/" + DateTime.Now.Year.ToString();
string thirdWeekEnd = dtOneMonthBack.ToString("MM") + "/21/" + DateTime.Now.Year.ToString();
string fourthWeekStart = dtOneMonthBack.ToString("MM") + "/22/" + DateTime.Now.Year.ToString();
DateTime lastDate = new DateTime(year, month,
DateTime.DaysInMonth(year, month));
string fourthWeekEnd = lastDate.ToString("MM/dd/yyyy");
//string firstWeekStart = dtOneMonthBack.ToString("MM/dd/yyyy");
//string firstWeekEnd = dtOneMonthBack.AddDays(7).ToString("MM/dd/yyyy");
//string secondWeekStart = dtOneMonthBack.AddDays(8).ToString("MM/dd/yyyy");
//string secondWeekEnd = dtOneMonthBack.AddDays(14).ToString("MM/dd/yyyy");
//string thirdWeekStart = dtOneMonthBack.AddDays(15).ToString("MM/dd/yyyy");
//string thirdWeekEnd = dtOneMonthBack.AddDays(21).ToString("MM/dd/yyyy");
//string fourthWeekStart = dtOneMonthBack.AddDays(22).ToString("MM/dd/yyyy");
////DateTime lastDate = new DateTime(year, month,
//// DateTime.DaysInMonth(year, month));
//string fourthWeekEnd = DateTime.Now.ToString("MM/dd/yyyy");
List <GrossWrittenPremiumReportModels> ListGrossWrittenPremiumReport = new List <GrossWrittenPremiumReportModels>();
ListGrossWrittenPremiumReportModels _ListGrossWrittenPremiumReport = new ListGrossWrittenPremiumReportModels();
_ListGrossWrittenPremiumReport.ListGrossWrittenPremiumReportdata = new List <GrossWrittenPremiumReportModels>();
try
{
ListGrossWrittenPremiumReport = getGWPData(firstWeekStart, firstWeekEnd);
Debug.WriteLine("**************hdfhd***************");
Debug.WriteLine(ListGrossWrittenPremiumReport.Count());
Debug.WriteLine("**************hdfhd***************");
var report2 = getGWPData(secondWeekStart, secondWeekEnd);
var report3 = getGWPData(thirdWeekStart, thirdWeekEnd);
var report4 = getGWPData(fourthWeekStart, fourthWeekEnd);
var report5 = getGWPData(firstWeekStart, fourthWeekEnd);
List <BranchModel> branches = InsuranceContext.Query("select * from Branch").Select(x => new BranchModel
{
Id = x.Id,
BranchName = x.BranchName
}).ToList();
List <WeeklyGWPModel> weeklyGWPModels = new List <WeeklyGWPModel>();
branches.ForEach(x =>
{
WeeklyGWPModel model = new WeeklyGWPModel();
if (x.BranchName == "Online")
{
var count = ListGrossWrittenPremiumReport.FindAll(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Count();
var total = ListGrossWrittenPremiumReport.Where(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Sum(item => item.Premium_due);
var count2 = report2.FindAll(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Count();
var total2 = report2.Where(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Sum(item => item.Premium_due);
var count3 = report3.FindAll(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Count();
var total3 = report3.Where(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Sum(item => item.Premium_due);
var count4 = report4.FindAll(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Count();
var total4 = report4.Where(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Sum(item => item.Premium_due);
var count5 = report5.FindAll(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Count();
var total5 = report5.Where(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Sum(item => item.Premium_due);
model.BranchName = x.BranchName;
model.FirstWeekCount = count;
model.FirstWeekValue = total;
model.SecondWeekCount = count2;
model.SecondWeekValue = total2;
model.ThirdWeekCount = count3;
model.ThirdWeekValue = total3;
model.FourWeekCount = count4;
model.FourWeekValue = total4;
model.TotalMonthCount = count5;
model.TotalMonthValue = total5;
weeklyGWPModels.Add(model);
}
else
{
var count = ListGrossWrittenPremiumReport.FindAll(p => p.BranchName == x.BranchName).Count();
var total = ListGrossWrittenPremiumReport.Where(p => p.BranchName == x.BranchName).Sum(item => item.Premium_due);
var count2 = report2.FindAll(p => p.BranchName == x.BranchName).Count();
var total2 = report2.Where(p => p.BranchName == x.BranchName).Sum(item => item.Premium_due);
var count3 = report3.FindAll(p => p.BranchName == x.BranchName).Count();
var total3 = report3.Where(p => p.BranchName == x.BranchName).Sum(item => item.Premium_due);
var count4 = report4.FindAll(p => p.BranchName == x.BranchName).Count();
var total4 = report4.Where(p => p.BranchName == x.BranchName).Sum(item => item.Premium_due);
var count5 = report5.FindAll(p => p.BranchName == x.BranchName).Count();
var total5 = report5.Where(p => p.BranchName == x.BranchName).Sum(item => item.Premium_due);
model.BranchName = x.BranchName;
model.FirstWeekCount = count;
model.FirstWeekValue = total;
model.SecondWeekCount = count2;
model.SecondWeekValue = total2;
model.ThirdWeekCount = count3;
model.ThirdWeekValue = total3;
model.FourWeekCount = count4;
model.FourWeekValue = total4;
model.TotalMonthCount = count5;
model.TotalMonthValue = total5;
weeklyGWPModels.Add(model);
}
});
GenerateExcel2(weeklyGWPModels);
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
}
public void SendWeeklyReport()
{
Library.WriteErrorLog("Start Summary GWP Report");
var dtOneMonthBack = DateTime.Now;
int year = dtOneMonthBack.Year;
int month = dtOneMonthBack.Month;
string firstWeekStart = dtOneMonthBack.ToString("MM") + "/01/" + DateTime.Now.Year.ToString();
string firstWeekEnd = dtOneMonthBack.ToString("MM") + "/07/" + DateTime.Now.Year.ToString();
string secondWeekStart = dtOneMonthBack.ToString("MM") + "/08/" + DateTime.Now.Year.ToString();
string secondWeekEnd = dtOneMonthBack.ToString("MM") + "/14/" + DateTime.Now.Year.ToString();
string thirdWeekStart = dtOneMonthBack.ToString("MM") + "/15/" + DateTime.Now.Year.ToString();
string thirdWeekEnd = dtOneMonthBack.ToString("MM") + "/21/" + DateTime.Now.Year.ToString();
string fourthWeekStart = dtOneMonthBack.ToString("MM") + "/22/" + DateTime.Now.Year.ToString();
DateTime lastDate = new DateTime(year, month,
DateTime.DaysInMonth(year, month));
string fourthWeekEnd = lastDate.ToString("MM/dd/yyyy");
//string firstWeekStart = dtOneMonthBack.ToString("MM/dd/yyyy");
//string firstWeekEnd = dtOneMonthBack.AddDays(7).ToString("MM/dd/yyyy");
//string secondWeekStart = dtOneMonthBack.AddDays(8).ToString("MM/dd/yyyy");
//string secondWeekEnd = dtOneMonthBack.AddDays(14).ToString("MM/dd/yyyy");
//string thirdWeekStart = dtOneMonthBack.AddDays(15).ToString("MM/dd/yyyy");
//string thirdWeekEnd = dtOneMonthBack.AddDays(21).ToString("MM/dd/yyyy");
//string fourthWeekStart = dtOneMonthBack.AddDays(22).ToString("MM/dd/yyyy");
////DateTime lastDate = new DateTime(year, month,
//// DateTime.DaysInMonth(year, month));
//string fourthWeekEnd = DateTime.Now.ToString("MM/dd/yyyy");
List <GrossWrittenPremiumReportModels> ListGrossWrittenPremiumReport = new List <GrossWrittenPremiumReportModels>();
ListGrossWrittenPremiumReportModels _ListGrossWrittenPremiumReport = new ListGrossWrittenPremiumReportModels();
_ListGrossWrittenPremiumReport.ListGrossWrittenPremiumReportdata = new List <GrossWrittenPremiumReportModels>();
try
{
ListGrossWrittenPremiumReport = getGWPData(firstWeekStart, firstWeekEnd);
Library.WriteErrorLog("Summary GWP count: " + ListGrossWrittenPremiumReport.Count());
var report2 = getGWPData(secondWeekStart, secondWeekEnd);
Library.WriteErrorLog("Summary GWP count: " + report2.Count());
var report3 = getGWPData(thirdWeekStart, thirdWeekEnd);
Library.WriteErrorLog("Summary GWP count: " + report3.Count());
var report4 = getGWPData(fourthWeekStart, fourthWeekEnd);
Library.WriteErrorLog("Summary GWP count: " + report4.Count());
var report5 = getGWPData(firstWeekStart, fourthWeekEnd);
Library.WriteErrorLog("Summary GWP count: " + report5.Count());
DataTable dt = GetAllBranch();
List <BranchModel> branches = ConvertDataTable <BranchModel>(dt);
List <WeeklyGWPModel> weeklyGWPModels = new List <WeeklyGWPModel>();
branches.ForEach(x =>
{
WeeklyGWPModel model = new WeeklyGWPModel();
if (x.BranchName == "Online")
{
var count = ListGrossWrittenPremiumReport.FindAll(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Count();
var total = ListGrossWrittenPremiumReport.Where(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Sum(item => item.Premium_due);
var count2 = report2.FindAll(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Count();
var total2 = report2.Where(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Sum(item => item.Premium_due);
var count3 = report3.FindAll(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Count();
var total3 = report3.Where(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Sum(item => item.Premium_due);
var count4 = report4.FindAll(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Count();
var total4 = report4.Where(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Sum(item => item.Premium_due);
var count5 = report5.FindAll(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Count();
var total5 = report5.Where(p => p.BranchName == x.BranchName || p.BranchName == "" || p.BranchName == null).Sum(item => item.Premium_due);
model.BranchName = x.BranchName;
model.FirstWeekCount = count;
model.FirstWeekValue = total;
model.SecondWeekCount = count2;
model.SecondWeekValue = total2;
model.ThirdWeekCount = count3;
model.ThirdWeekValue = total3;
model.FourWeekCount = count4;
model.FourWeekValue = total4;
model.TotalMonthCount = count5;
model.TotalMonthValue = total5;
weeklyGWPModels.Add(model);
}
else
{
var count = ListGrossWrittenPremiumReport.FindAll(p => p.BranchName == x.BranchName).Count();
var total = ListGrossWrittenPremiumReport.Where(p => p.BranchName == x.BranchName).Sum(item => item.Premium_due);
var count2 = report2.FindAll(p => p.BranchName == x.BranchName).Count();
var total2 = report2.Where(p => p.BranchName == x.BranchName).Sum(item => item.Premium_due);
var count3 = report3.FindAll(p => p.BranchName == x.BranchName).Count();
var total3 = report3.Where(p => p.BranchName == x.BranchName).Sum(item => item.Premium_due);
var count4 = report4.FindAll(p => p.BranchName == x.BranchName).Count();
var total4 = report4.Where(p => p.BranchName == x.BranchName).Sum(item => item.Premium_due);
var count5 = report5.FindAll(p => p.BranchName == x.BranchName).Count();
var total5 = report5.Where(p => p.BranchName == x.BranchName).Sum(item => item.Premium_due);
model.BranchName = x.BranchName;
model.FirstWeekCount = count;
model.FirstWeekValue = total;
model.SecondWeekCount = count2;
model.SecondWeekValue = total2;
model.ThirdWeekCount = count3;
model.ThirdWeekValue = total3;
model.FourWeekCount = count4;
model.FourWeekValue = total4;
model.TotalMonthCount = count5;
model.TotalMonthValue = total5;
weeklyGWPModels.Add(model);
}
});
GenerateExcel2(weeklyGWPModels);
}
catch (Exception ex)
{
Library.WriteErrorLog("Summary GWP Report Count - " + ex.Message);
}
}
