private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e) { //int SelectedCustomer = CustomersGridView.CurrentCell.RowIndex; //int ClientID = (int)CustomersGridView.Rows[SelectedCustomer].Cells[0].Value; var queryMeal = from Meal in db.Meals where Meal.id_customer == ClientID select Meal; foreach (var item in queryMeal.ToList()) { var queryProd_Meals = from prod_Meal in db.Meals_products where prod_Meal.id_meal == item.id_meal select prod_Meal; foreach (var item2 in queryProd_Meals.ToList()) { string insertStatement = "Delete From Meals_products where id_meal = " + item2.id_meal; db.ExecuteQuery <Meals_product>(insertStatement); } db.Meals.DeleteOnSubmit(item); } var queryClient = from Client in db.Customers where Client.id_customer == ClientID select Client; db.Customers.DeleteOnSubmit(queryClient.First()); db.SubmitChanges(); customersTableAdapter.Fill(this.dataSet.Customers); }
//获取题目可用数量 public int GetNumberOfTopicBySort(string sortId) { string sql = "select count(1) from Topic " + "where SecondPointId in " + "(select SecondPointId from SecondPoint where IsChose = 1)" + "and TopicSourceId in (select TopicSourceId from TopicSource where IsChose = 1)" + "and SortId = {0}"; return(db.ExecuteQuery <int>(sql, new object[] { int.Parse(sortId) }).First()); }
public static List <VIEW_案例信息_城市表_网站表> 获取当前要整理入库的案例(string 城市名称, string 网站名称, int 个数) { List <VIEW_案例信息_城市表_网站表> list = new List <VIEW_案例信息_城市表_网站表>(); try { if (string.IsNullOrEmpty(城市名称) || string.IsNullOrEmpty(网站名称) || 个数 < 1) { return(list); } using (DataClassesDataContext db = new DataClassesDataContext()) { var result = db.ExecuteQuery <VIEW_案例信息_城市表_网站表>("exec 获取当前要整理入库的案例_根据城市_网站 {0},{1},{2}", new object[] { 城市名称, 网站名称, 个数 }); list = result.ToList <VIEW_案例信息_城市表_网站表>(); } if (list != null && list.Count > 0) { list.ForEach(delegate(VIEW_案例信息_城市表_网站表 obj) { obj.来源 = 网站名称; obj.城市 = 城市名称; }); } } catch (Exception ex) { log.Error(string.Format("获取当前要整理入库的案例_异常,(城市名称:{0},网站名称{1},个数:{2})", 城市名称 == null ? "null" : 城市名称, 网站名称 == null ? "null" : 网站名称, 个数), ex); } return(list); }
public static List <VIEW_案例信息_城市表_网站表> 获取当前要整理入库的案例(int 个数) { List <VIEW_案例信息_城市表_网站表> list = new List <VIEW_案例信息_城市表_网站表>(); try { using (DataClassesDataContext db = new DataClassesDataContext()) { var result = db.ExecuteQuery <VIEW_案例信息_城市表_网站表>("exec 获取当前要整理入库的案例 {0}", new object[] { 个数 }); list = result.ToList <VIEW_案例信息_城市表_网站表>(); } if (list != null && list.Count > 0) { list.ForEach(delegate(VIEW_案例信息_城市表_网站表 obj) { obj.来源 = obj.网站; }); } } catch (Exception ex) { log.Error(string.Format("获取当前要整理入库的案例_异常,(个数:{0})", 个数), ex); } return(list); }
public static List <VIEW_案例信息_城市表_网站表> GetVIEW案例信息_根据城市网站案例日期的区间获取案例(int?cityId, int?webId, DateTime 开始日期, DateTime 结束日期, int start, int pageLength, bool isGetCount, out int count, out string message) { string startDate = 开始日期.ToString("yyyy-MM-dd HH:mm:ss"); string endDate = 结束日期.ToString("yyyy-MM-dd HH:mm:ss"); message = ""; count = 0; List <VIEW_案例信息_城市表_网站表> list = new List <VIEW_案例信息_城市表_网站表>(); using (DataClassesDataContext db = new DataClassesDataContext()) { StringBuilder sbSql = new StringBuilder("select * from VIEW_案例信息_城市表_网站表 with(nolock) where 1=1"); StringBuilder sbSql2 = new StringBuilder("select count(*) from VIEW_案例信息_城市表_网站表 with(nolock) where 1=1"); try { if (cityId != null) { sbSql.Append(" and 城市ID=").Append(cityId); sbSql2.Append(" and 城市ID=").Append(cityId); } if (webId != null) { sbSql.Append(" and 网站ID=").Append(webId); sbSql2.Append(" and 网站ID=").Append(webId); } sbSql.Append(" and 案例时间>='").Append(startDate).Append("'"); sbSql.Append(" and 案例时间<='").Append(endDate).Append("'"); sbSql2.Append(" and 案例时间>='").Append(startDate).Append("'"); sbSql2.Append(" and 案例时间<='").Append(endDate).Append("'"); if (isGetCount) { count = db.ExecuteQuery <int>(sbSql2.ToString()).FirstOrDefault(); } } catch (Exception ex) { message = "系统异常:sql(" + sbSql2.ToString() + "),异常信息:" + ex.Message;; return(new List <VIEW_案例信息_城市表_网站表>()); } list = db.ExecuteQuery <VIEW_案例信息_城市表_网站表>(sbSql.ToString().GetPageSql("ID desc", start, pageLength)).ToList(); } return(list); }
protected void Button1_Click(object sender, EventArgs e) { if (IsValid) { string regno = (string)Session["ID"]; regno = regno.Replace(" ", String.Empty).ToString(); Response.Write("Register Number:" + regno); string cid = DropDownList1.SelectedValue; cid = cid.Replace(" ", String.Empty).ToString();; // var flag = "f"; var ans = "no"; DataClassesDataContext cx3 = new DataClassesDataContext(); var result = cx3.ExecuteQuery <detailCompany>("Select * from detailCompany where companyid={0}", cid); foreach (var r in result) { //Response.Write("true"); using (DataClassesDataContext cx4 = new DataClassesDataContext()) { var query = cx4.ExecuteQuery <StudentsJobApplied>("Select * from StudentsJobApplied where studentid={0}", regno); foreach (var s in query) { // Response.Write("Company ID: "+s.companyid+" "+cid+" Student ID: "+s.studentid+" "+regno); if (s.companyid.Contains(cid)) { if (s.studentid.Contains(regno)) { lbstatus.Text = "You have Already Registered!"; ans = "yes"; } } } if (ans == "no") { StudentsJobApplied job_reg = new StudentsJobApplied { companyid = DropDownList1.SelectedValue, studentid = regno, }; cx4.StudentsJobApplieds.InsertOnSubmit(job_reg); cx4.SubmitChanges(); lbstatus.Text = "Successfully Applied"; //flag = "t"; } } } /* if(flag!="t") * { * //Response.Write("false"); * lbstatus.Text = "Invalid - Company ID Does not exists"; * }*/ } }
private void RemoveMealButton_Click(object sender, EventArgs e) { int SelectedMeal = CustomerMealsGridView.CurrentCell.RowIndex; int idOfMeal = (int)CustomerMealsGridView.Rows[SelectedMeal].Cells[0].Value; var queryProd_Meals = from prod_Meal in db.Meals_products where prod_Meal.id_meal == idOfMeal select prod_Meal; foreach (var item2 in queryProd_Meals.ToList()) { string insertStatement = "Delete From Meals_products where id_meal = " + item2.id_meal; db.ExecuteQuery <Meals_product>(insertStatement); } try { string insertStatement = "Delete From Meals where id_meal = " + idOfMeal; db.ExecuteQuery <Meals_product>(insertStatement); ClearProductsOfMeals(sender, e); } catch (Exception) { MessageBox.Show("First choose Product of Meal Then press to Removed"); } }
/// <summary> /// 初始化推荐书籍列表 /// </summary> protected void InitRecommendTable() { //通过数据库得到对应用户id关注的图书id DataClassesDataContext db = new DataClassesDataContext(); var books = db.ExecuteQuery <Book>("select * from BookWareHouse,Book where BookWareHouse.BookID = Book.BookID" + " and BookWareHouse.Type = 'recommend' and BookWareHouse.QuanZiID = '" + Session["QuanZiID"].ToString() + "'"); foreach (Book book in books) { TableRow row = new TableRow(); TableCell cell = new TableCell(); ImageButton img = new ImageButton(); img.ImageUrl = book.CoverImagePath.ToString(); img.Width = 120; img.PostBackUrl = "~/BookWareHouse/CheckBook.aspx?BookID=" + book.BookID.ToString(); cell.Controls.Add(img); cell.HorizontalAlign = HorizontalAlign.Center; row.Cells.Add(cell); TableRow row2 = new TableRow(); TableCell cell2 = new TableCell(); HyperLink link = new HyperLink(); link.NavigateUrl = "~/BookWareHouse/CheckBook.aspx?BookID=" + book.BookID.ToString(); link.Text = book.Title.ToString(); cell2.Controls.Add(link); cell2.HorizontalAlign = HorizontalAlign.Center; row2.Cells.Add(cell2); Table temptable = new Table(); temptable.Rows.Add(row); temptable.Rows.Add(row2); TableRow temprow = new TableRow(); TableCell tempcell = new TableCell(); tempcell.Controls.Add(temptable); tempcell.HorizontalAlign = HorizontalAlign.Center; temprow.Cells.Add(tempcell); temprow.CssClass = "s_item"; table1.Rows.Add(temprow); } }
//根据考试Id获取某个考生的考试记录 public StuExam GetStuExam(string sId, string examId) { try { string sql = "select * from StuExam where StudentId = {0} " + "and PaperId in (select PaperId from Paper where ExamId = {1} )"; List <StuExam> stuExams = db.ExecuteQuery <StuExam>(sql, new Object[] { sId, examId }).ToList(); if (stuExams.Count <= 0) { return(null); } else { return(stuExams.First()); } } catch (Exception e) { return(null); } }
protected void Page_Load(object sender, EventArgs e) { DataClassesDataContext cx2 = new DataClassesDataContext(); var result = cx2.ExecuteQuery <StudentsJobApplied>("Select * from StudentsJobApplied"); int count = 0; foreach (var r in result) { count = count + 1; } if (count == 0) { lbstudentno.Text = "No Students Applied Yet"; } else { lbstudentno.Text = "The Total Number of Students Applied are " + count; } }
protected void Button3_Click(object sender, EventArgs e) { //display string regno = string.Empty; regno = (string)Session["ID"]; DataClassesDataContext dc2 = new DataClassesDataContext(); var result = dc2.ExecuteQuery <StudentDetail>("Select * from StudentDetail where studentid={0}", regno); foreach (var r in result) { string usercheck = r.studentid; usercheck = usercheck.Replace(" ", String.Empty); if (usercheck.Equals(regno)) { txtname.Text = r.name; txtdob.Text = r.dob; txtmailid.Text = r.mailid; txtmobno.Text = r.mobno; txtaddress.Text = r.address; DropDownList1.SelectedValue = r.dept; if (r.degree == "UG") { rdegree.SelectedIndex = 0; txtug.Text = r.ug; } else { rdegree.SelectedIndex = 1; txtug.Text = r.ug; txtpg.Text = r.pg; } txt10.Text = r.ssc; txt12.Text = r.hsc; Button4.Visible = true; Button5.Visible = true; } else { lbstatus.Text = "You Have Not Registered Any Information...Please Register"; } } }
protected void btnlogin_Click(object sender, EventArgs e) { if (IsValid) { string uname = txtname.Text; string pwd = txtpwd.Text; if (uname == "admin" && pwd == "admin") { Response.Redirect("Admin.aspx"); } else { DataClassesDataContext cx2 = new DataClassesDataContext(); var result = cx2.ExecuteQuery <login>("Select * from login where username={0}", uname); var f = "f"; foreach (var r in result) { f = "t"; string str = r.password; str = str.Replace(" ", String.Empty); if (str.Equals(pwd)) { //lbcheck.Text = "valid"; Session["ID"] = txtname.Text; Response.Redirect("Student.aspx"); } else { lbcheck.Text = "Invalid Password"; } } if (f != "t") { lbcheck.Text = "Username does not exist - Please go and Register"; } } } }
protected void btnsubmit_Click(object sender, EventArgs e) { if (IsValid) { string ans = "no"; string uname = txtname.Text; uname = uname.Replace(" ", String.Empty); Response.Write("USERNAME" + uname); using (DataClassesDataContext cx1 = new DataClassesDataContext()) { var query = cx1.ExecuteQuery <login>("Select * from login where username={0}", uname); foreach (var s in query) { Response.Write("INSIDE USERNAME" + s.username); if (s.username.Equals(uname)) { lbcheck.Text = "You have Already Registered!"; ans = "yes"; } } if (ans == "no") { login student_reg = new login { username = txtname.Text, password = txtpwd1.Text }; cx1.logins.InsertOnSubmit(student_reg); cx1.SubmitChanges(); lbcheck.Text = "Successfully Registered"; } txtname.Text = ""; txtpwd1.Text = ""; txtpwd2.Text = ""; } } }
public Post ExecuteQuery() { Step(); return(Linq2SqlContext.ExecuteQuery <Post>("select * from Posts where Id = {0}", i).First()); }
public static void PrintFinCardByApplicationDateReport(DateTime fromDate, DateTime toDate, List <int> lstBranhces, List <int> lstInsuranceCompanies, List <int> lstInsuranceSubTypes, List <int> lstUsers, List <int> lstMarketingAgents) { string brokerName = BrokerHouseInformation.GetBrokerHouseName(); PDFCreators creator = new PDFCreators(true, 25, 25, 15, 15); creator.SetDocumentHeaderFooter(); creator.OpenPDF(); creator.GetContentByte(); creator.AddJDBLogoForFactures(10, 775); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitle("ФИНАНСОВА КАРТИЦА"); creator.SetTitle("по датум на издавање на полиси"); creator.SetTitle("за период " + fromDate.ToShortDateString() + " - " + toDate.ToShortDateString()); creator.SetTitleLeft10("Датум на печатење: " + DateTime.Today.ToShortDateString()); decimal overallSaldo = 0; decimal overallDebt = 0; decimal overallDemand = 0; DateTime dt1 = fromDate; DateTime dt2 = toDate; int d1 = dt1.Day; int m1 = dt1.Month; int y1 = dt1.Year; int d2 = dt2.Day; int m2 = dt2.Month; int y2 = dt2.Year; //List<Policy> lstPol = Policy.Table.Where(c => c.Discard == false && c.ApplicationDate.Date >= fromDate.Date && c.ApplicationDate.Date <= toDate.Date).ToList(); DataClassesDataContext dc = new DataClassesDataContext(); string query = @"SELECT p.* from policies p, policyitems pi where pi.policyid=p.id" + @" and p.branchid in (" + ReportFinPolicies.GetFromIDs(lstBranhces) + ") " + @" and p.userid in (" + ReportFinPolicies.GetFromIDs(lstUsers) + ") " + @" and p.insurancecompanyid in (" + ReportFinPolicies.GetFromIDs(lstInsuranceCompanies) + ") " + @" and pi.insurancesubtypeid in (" + ReportFinPolicies.GetFromIDs(lstInsuranceSubTypes) + ") " + @" and p.applicationdate>='" + y1 + - +m1 + - +d1 + "'" + @" and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'" + @" and p.discard = 0 "; List <Policy> lstPol = dc.ExecuteQuery <Policy>(query).ToList(); foreach (Policy p in lstPol) { creator.SetTitleLeft10("Број на полиса: " + p.PolicyItems[0].PolicyNumber); creator.SetTitleLeft10("Осигурителна компанија: " + p.InsuranceCompany.ShortName); creator.SetTitleLeft10("Подкласа на осигурување: " + p.PolicyItems[0].InsuranceSubType.ShortDescription); creator.SetTitleLeft10("Датум на полиса: " + p.ApplicationDate.ToShortDateString()); //decimal totalSaldo = 0; decimal totalDebtValue = 0; decimal totalDemandValue = 0; List <FinanceCardInfo> lst = new List <FinanceCardInfo>(); lst = FinanceCardController.GetByPolicy(p, 0); string[] headers = { "Дата", "Доспева", "Опис", "Должи", "Побарува", "Салдо" }; float[] widthPercentages = { 10, 10, 35, 15, 15, 15 }; TypeCode[] typeCodes = { TypeCode.String, TypeCode.String, TypeCode.String, TypeCode.Decimal, TypeCode.Decimal, TypeCode.Decimal }; creator.CreateTable_Facture(headers.Length, headers, widthPercentages); foreach (FinanceCardInfo fci in lst) { object[] values; values = new object[headers.Length]; values[0] = fci.DocumentDate.ToShortDateString(); values[1] = fci.PaidDate.ToShortDateString(); values[2] = fci.Description; values[3] = String.Format("{0:#,0.00}", fci.DebtValue); values[4] = String.Format("{0:#,0.00}", fci.DemandValue); values[5] = String.Format("{0:#,0.00}", fci.SaldoValue); totalDebtValue += fci.DebtValue; totalDemandValue += fci.DemandValue; //totalSaldo += fci.SaldoValue; creator.AddDataRowForFactures(values, headers.Length, typeCodes); } object[] valuesTot; valuesTot = new object[headers.Length]; valuesTot[0] = ""; valuesTot[1] = ""; valuesTot[2] = ""; valuesTot[3] = String.Format("{0:#,0.00}", totalDebtValue); valuesTot[4] = String.Format("{0:#,0.00}", totalDemandValue); valuesTot[5] = String.Format("{0:#,0.00}", totalDebtValue - totalDemandValue); overallSaldo += (totalDebtValue - totalDemandValue); overallDemand += totalDemandValue; overallDebt += totalDebtValue; creator.AddDataRowForFactures(valuesTot, headers.Length, typeCodes); creator.AddTable(); } creator.SetTitleRight("Должи: " + String.Format("{0:#,0.00}", overallDebt) + " Побарува: " + String.Format("{0:#,0.00}", overallDemand) + " Салдо: " + String.Format("{0:#,0.00}", overallSaldo)); creator.FinishPDF_FileName("FinCard"); }
/// <summary> /// 搜索按钮事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void searchbotton_Click(object sender, EventArgs e) { if (Page.IsValid) { int n = sel.SelectedIndex; string[] type = { "Title", "Author", "Publisher", "ISBN", "Category" }; DataClassesDataContext db = new DataClassesDataContext(); var books = db.ExecuteQuery <Book>("select * from Book where " + type[n] + " like \'%" + inputtextbox.Text + "%\'"); foreach (Book book in books) { TableRow row = new TableRow(); row.CssClass = "s_item"; TableCell cell = new TableCell(); cell.CssClass = "tdstyle"; ImageButton img = new ImageButton(); img.ImageUrl = book.CoverImagePath.ToString(); img.Width = 150; img.PostBackUrl = "~/BookWareHouse/CheckBook.aspx?BookID=" + book.BookID.ToString(); cell.Controls.Add(img); cell.HorizontalAlign = HorizontalAlign.Right; row.Cells.Add(cell); TableCell cell2 = new TableCell(); cell2.CssClass = "td_pad"; cell2.HorizontalAlign = HorizontalAlign.Left; Table celltable = new Table(); celltable.CssClass = "tbstyle"; celltable.HorizontalAlign = HorizontalAlign.Left; TableRow rightrow1 = new TableRow(); TableCell rightcell1 = new TableCell(); rightcell1.CssClass = "td_row"; rightcell1.HorizontalAlign = HorizontalAlign.Left; HyperLink link = new HyperLink(); link.NavigateUrl = "~/BookWareHouse/CheckBook.aspx?BookID=" + book.BookID.ToString(); link.Text = book.Title.ToString(); link.Font.Size = 18; link.CssClass = "h4"; rightcell1.Controls.Add(link); rightrow1.Cells.Add(rightcell1); celltable.Rows.Add(rightrow1); TableRow rightrow2 = new TableRow(); TableCell rightcell2 = new TableCell(); rightcell2.CssClass = "td_row"; rightcell2.Text = book.Author + "/" + book.Publisher + "/" + book.ISBN; rightrow2.Cells.Add(rightcell2); celltable.Rows.Add(rightrow2); TableRow rightrow3 = new TableRow(); TableCell rightcell3 = new TableCell(); rightcell3.CssClass = "td_row"; rightcell3.Text = "类型:" + book.Category; rightrow3.Cells.Add(rightcell3); celltable.Rows.Add(rightrow3); TableRow rightrow4 = new TableRow(); TableCell rightcell4 = new TableCell(); rightcell4.CssClass = "td_row"; rightcell4.Text = "评分:" + book.Score.ToString(); rightrow4.Cells.Add(rightcell4); celltable.Rows.Add(rightrow4); cell2.Controls.Add(celltable); row.Cells.Add(cell2); Table1.Rows.Add(row); } } }
/// <summary> /// 初始化热门图书列表 /// </summary> protected void InitHotBookTable() { //通过数据库得到热门图书的图书id DataClassesDataContext db = new DataClassesDataContext(); var books = db.ExecuteQuery <Book>("select * from Book order by Score DESC"); int n = 0; foreach (Book book in books) { TableRow row = new TableRow(); row.CssClass = "s_item"; TableCell cell = new TableCell(); cell.CssClass = "tdstyle"; ImageButton img = new ImageButton(); img.ImageUrl = book.CoverImagePath.ToString(); img.Width = 150; img.PostBackUrl = "~/BookWareHouse/CheckBook.aspx?BookID=" + book.BookID.ToString(); cell.Controls.Add(img); cell.HorizontalAlign = HorizontalAlign.Left; row.Cells.Add(cell); TableCell cell2 = new TableCell(); cell2.CssClass = "td_pad"; cell2.HorizontalAlign = HorizontalAlign.Left; Table celltable = new Table(); //celltable.CssClass = "tbstyle"; celltable.HorizontalAlign = HorizontalAlign.Left; TableRow rightrow1 = new TableRow(); TableCell rightcell1 = new TableCell(); //rightcell1.CssClass = "td_row"; rightcell1.HorizontalAlign = HorizontalAlign.Left; HyperLink link = new HyperLink(); link.NavigateUrl = "~/BookWareHouse/CheckBook.aspx?BookID=" + book.BookID.ToString(); link.Text = book.Title.ToString(); link.Font.Size = 12; link.CssClass = "h4"; rightcell1.Controls.Add(link); rightrow1.Cells.Add(rightcell1); celltable.Rows.Add(rightrow1); TableRow rightrow2 = new TableRow(); TableCell rightcell2 = new TableCell(); // rightcell2.CssClass = "td_row"; rightcell2.Text = book.Author + "/" + book.Publisher + "/" + book.ISBN; rightrow2.Cells.Add(rightcell2); celltable.Rows.Add(rightrow2); TableRow rightrow3 = new TableRow(); TableCell rightcell3 = new TableCell(); //rightcell3.CssClass = "td_row"; rightcell3.Text = "类型:" + book.Category; rightrow3.Cells.Add(rightcell3); celltable.Rows.Add(rightrow3); TableRow rightrow4 = new TableRow(); TableCell rightcell4 = new TableCell(); //rightcell4.CssClass = "td_row"; rightcell4.Text = "评分:" + book.Score.ToString(); rightrow4.Cells.Add(rightcell4); celltable.Rows.Add(rightrow4); cell2.Controls.Add(celltable); row.Cells.Add(cell2); TableCell cell3 = new TableCell(); cell3.Width = 200; row.Cells.Add(cell3); hotbooktable.Rows.Add(row); n++; if (n > 2) { break; } } }
public static void PrintReportForASO(int insuranceCompanyID, DateTime startDate, DateTime endDate) { DateTime dt1 = startDate; DateTime dt2 = endDate; int d1 = dt1.Day; int m1 = dt1.Month; int y1 = dt1.Year; int d2 = dt2.Day; int m2 = dt2.Month; int y2 = dt2.Year; DataClassesDataContext dcdc = new DataClassesDataContext(); PDFCreators pdf = new PDFCreators(false, 17, 17, 25, 25); pdf.OpenPDF(); pdf.SetTitle(" "); pdf.SetTitle(" "); pdf.AddJDBLogo(660, 480); pdf.SetTitle("Квартален извештај за Брокерските друштва"); pdf.SetTitle("за период " + startDate.ToShortDateString() + " - " + endDate.ToShortDateString()); if (insuranceCompanyID > 0) { InsuranceCompany ic = InsuranceCompany.Get(insuranceCompanyID); pdf.SetTitle("Осигурителна компанија: " + ic.ShortName); } pdf.SetTitleLeft(""); string[] headers = new string[6]; headers[0] = "Класа/подкласа на осигурување"; headers[1] = "Шифра"; headers[2] = "Број на договори"; headers[3] = "Премија"; headers[4] = "Реализирана провизија"; headers[5] = "Пресметана провизија"; TypeCode[] codes = new TypeCode[headers.Count()]; codes[0] = TypeCode.String; codes[1] = TypeCode.String; codes[2] = TypeCode.Int32; codes[3] = TypeCode.Decimal; codes[4] = TypeCode.Decimal; codes[5] = TypeCode.Decimal; float[] policyColumnsWidths = new float[headers.Count()]; policyColumnsWidths[0] = 55; policyColumnsWidths[1] = 5; policyColumnsWidths[2] = 10; policyColumnsWidths[3] = 10; policyColumnsWidths[4] = 10; policyColumnsWidths[5] = 10; pdf.CreateTableWithBorder(headers.Count(), false, headers, "", policyColumnsWidths); int totalNumberOfPolicies = 0; decimal totalPremiumValue = 0; decimal totalBrokValue = 0; decimal totalRealBrokValue = 0; List <InsuranceType> listInsuranceTypes = InsuranceType.Table.OrderBy(c => c.Code).ToList(); foreach (InsuranceType it in listInsuranceTypes) { object[] vals = new object[headers.Count()]; vals[0] = it.Name; vals[1] = it.Code; List <InsuranceSubType> listInsuranceSubTypes = InsuranceSubType.GetByInsuranceType(it.ID).OrderBy(c => c.Code).ToList(); int numberOfPolicies = 0; int premiumValue = 0; decimal brokValue = 0; decimal realBrokValue = 0; decimal tmpBrokValue = 0; decimal tmpBrokBaseValue = 0; decimal totFacturedValueInClass = 0; decimal totFacturedBrokerageValuePerClass = 0; //decimal tmpBrokValueInOtherClass = 0; decimal k = 0; foreach (InsuranceSubType ist in listInsuranceSubTypes) { string queryCountAndPremiumDef = string.Empty; string queryCountAndPremiumFullPremiumValue = string.Empty; string queryCountAndPremiumInOtherClasses = string.Empty; string getCalculatedBrokerages = string.Empty; string queryCalcBrokerageInOtherClases = string.Empty; string queryRealPaidValues = string.Empty; string queryGetFacturedBrokeragesPayments = string.Empty; decimal tmpBrokValueInOtherClass = 0; decimal tmpBrokBaseValueTot = 0; decimal tmpBrokValueTot = 0; if (insuranceCompanyID > 0) { queryCountAndPremiumFullPremiumValue = @"select count(pi1.id) as Count, sum(cast(pi1.premiumvalue as int)) as SumPremiumValue from policies p1 inner join policyitems pi1 on pi1.policyid=p1.id where p1.discard=0 and pi1.insurancesubtypeid=" + ist.ID + " and p1.insurancecompanyid = " + insuranceCompanyID + @" and p1.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p1.applicationdate<='" + y2 + - +m2 + - +d2 + "'"; queryCountAndPremiumDef = @"select 1, sum(cast(pei.Value as int)) as SumPremiumValue from policies p ,policyitems pi, policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist where p.discard=0 and caist.isactive=1 and pi.policyid=p.id and caist.controlid=pei.controlid and c.variabletypeid in (2,3,4) and c.specialfieldtypeid = 2 and c.insurancesubtypeid=" + ist.ID + @" and pei.policyitemid=pi.id and pei.controlid=c.id and pei.Value<>'0' and p.insurancecompanyid = " + insuranceCompanyID + @" and p.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'"; queryCountAndPremiumInOtherClasses = @"select count(pi.id) as Count, sum(cast(pei.Value as int)) as SumPremiumValue from policies p ,policyitems pi, policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist where p.discard=0 and caist.insurancesubtypeid=" + ist.ID + @" and pi.policyid=p.id and caist.controlid=pei.controlid and caist.isactive=1 and pei.policyitemid=pi.id and pei.controlid=c.id and pei.Value<>'0' and p.insurancecompanyid = " + insuranceCompanyID + @" and p.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'"; getCalculatedBrokerages = @"select pi1.id, pi1.insurancesubtypeid,pi1.premiumvalue, (select sum(convert(int,pei.Value)) from policyitems pi, policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist where caist.isactive=1 and caist.controlid=pei.controlid and c.variabletypeid in (2,3,4) and c.specialfieldtypeid = 2 and pei.policyitemid=pi.id and pei.controlid=c.id and pei.Value<>'0' and pi.id = pi1.id) as basicvalue, case when pi1.brokerageid is not null then (case when c1.islaw=1 then (select b.percentageforlaws from brokerages b where b.insurancesubtypeid=pi1.insurancesubtypeid and b.id=pi1.brokerageid) else (select b.percentageforprivates from brokerages b where b.insurancesubtypeid=pi1.insurancesubtypeid and b.id=pi1.brokerageid) end) else (case when pi1.packetbrokerageid is not null then (case when c1.islaw=1 then (select pist.BrokeragePecentageForPrivates from PacketsInsuranceSubTypes pist where pist.insurancesubtypeid=pi1.insurancesubtypeid and pist.id=pi1.packetbrokerageid) else (select pist.BrokeragePecentageForLaws from PacketsInsuranceSubTypes pist where pist.insurancesubtypeid=pi1.insurancesubtypeid and pist.id=pi1.packetbrokerageid) end) else 0 end) end as brokeragepercentage from policies p1 inner join policyitems pi1 on pi1.policyid=p1.id inner join clients c1 on p1.clientid=c1.id where p1.discard=0 and pi1.insurancesubtypeid=" + ist.ID + @" and p1.insurancecompanyid = " + insuranceCompanyID + @" and p1.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p1.applicationdate<='" + y2 + - +m2 + - +d2 + "'"; queryCalcBrokerageInOtherClases = @"select pi.id, pei.Value, case when pi.brokerageid is not null then (case when c1.islaw=1 then (select b.percentageforlaws from brokerages b where b.insurancesubtypeid=pi.insurancesubtypeid and b.id=pi.brokerageid) else (select b.percentageforprivates from brokerages b where b.insurancesubtypeid=pi.insurancesubtypeid and b.id=pi.brokerageid) end) else (case when pi.packetbrokerageid is not null then (case when c1.islaw=1 then (select pist.BrokeragePecentageForPrivates from PacketsInsuranceSubTypes pist where pist.insurancesubtypeid=pi.insurancesubtypeid and pist.id=pi.packetbrokerageid) else (select pist.BrokeragePecentageForLaws from PacketsInsuranceSubTypes pist where pist.insurancesubtypeid=pi.insurancesubtypeid and pist.id=pi.packetbrokerageid) end) else 0 end) end as brokeragepercentage from policies p ,policyitems pi, clients c1, policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist where p.discard=0 and p.clientid=c1.id and caist.insurancesubtypeid=" + ist.ID + @" and pi.policyid=p.id and caist.controlid=pei.controlid and caist.isactive=1 and pei.policyitemid=pi.id and pei.controlid=c.id and pei.Value<>'0' " + @" and p.insurancecompanyid = " + insuranceCompanyID + @" and p.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'"; queryRealPaidValues = @"select sum(fcpvpist.paidvalue) as sumpaidvalue from FacCollPaidValuesPerInsSubTypes fcpvpist,facturecollectedpaidvalues fcpv, factureitems fi, factures f where fcpvpist.facturecollectedpaidvalueid=fcpv.id and fcpv.factureitemid=fi.id and fi.factureid=f.id and f.discard=0 and fcpv.paiddate>='" + y1 + - +m1 + - +d1 + "' and fcpv.paiddate<='" + y2 + - +m2 + - +d2 + "'" + @" and fcpvpist.insurancesubtypeid=" + ist.ID + @" and f.insurancecompanyid=" + insuranceCompanyID; queryGetFacturedBrokeragesPayments = @"select sum(ppist.brokeragevalue) as tmpValueDecimal from PaymentsPerInsSubTypes ppist, payments pa, rates r, policyitems pi, policyitemfactureitems pifi, factureitems fi, factures f, policies p, facturepayments fp where ppist.paymentid=pa.id and pa.rateid=r.id and r.policyitemid=pi.id and pi.policyid=p.id and pifi.policyitemid=pi.id and pifi.factureitemid=fi.id and fi.factureid=f.id and f.discard=0 and fp.factureid=f.id and fp.paymentid=pa.id and p.discard=0 and p.applicationdate>='" + y1 + - +m1 + - +d1 + "'" + @" and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'" + @" and p.insurancecompanyid=" + insuranceCompanyID + @" and ppist.insurancesubtypeid=" + ist.ID; } else { queryCountAndPremiumFullPremiumValue = @"select count(pi1.id) as Count, sum(cast(pi1.premiumvalue as int)) as SumPremiumValue from policies p1 inner join policyitems pi1 on pi1.policyid=p1.id where p1.discard=0 and pi1.insurancesubtypeid=" + ist.ID + @" and p1.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p1.applicationdate<='" + y2 + - +m2 + - +d2 + "'"; queryCountAndPremiumDef = @"select 1, sum(cast(pei.Value as int)) as SumPremiumValue from policies p ,policyitems pi, policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist where p.discard=0 and caist.isactive=1 and pi.policyid=p.id and caist.controlid=pei.controlid and c.variabletypeid in (2,3,4) and c.specialfieldtypeid = 2 and c.insurancesubtypeid=" + ist.ID + @" and pei.policyitemid=pi.id and pei.controlid=c.id and pei.Value<>'0'" + @" and p.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'"; queryCountAndPremiumInOtherClasses = @"select count(pi.id) as Count, sum(cast(pei.Value as int)) as SumPremiumValue from policies p ,policyitems pi, policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist where p.discard=0 and caist.insurancesubtypeid=" + ist.ID + @" and pi.policyid=p.id and caist.controlid=pei.controlid and caist.isactive=1 and pei.policyitemid=pi.id and pei.controlid=c.id and pei.Value<>'0'" + @" and p.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'"; getCalculatedBrokerages = @"select pi1.id, pi1.insurancesubtypeid,pi1.premiumvalue, (select sum(convert(int,pei.Value)) from policyitems pi, policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist where caist.isactive=1 and caist.controlid=pei.controlid and c.variabletypeid in (2,3,4) and c.specialfieldtypeid = 2 and pei.policyitemid=pi.id and pei.controlid=c.id and pei.Value<>'0' and pi.id = pi1.id) as basicvalue, case when pi1.brokerageid is not null then (case when c1.islaw=1 then (select b.percentageforlaws from brokerages b where b.insurancesubtypeid=pi1.insurancesubtypeid and b.id=pi1.brokerageid) else (select b.percentageforprivates from brokerages b where b.insurancesubtypeid=pi1.insurancesubtypeid and b.id=pi1.brokerageid) end) else (case when pi1.packetbrokerageid is not null then (case when c1.islaw=1 then (select pist.BrokeragePecentageForPrivates from PacketsInsuranceSubTypes pist where pist.insurancesubtypeid=pi1.insurancesubtypeid and pist.id=pi1.packetbrokerageid) else (select pist.BrokeragePecentageForLaws from PacketsInsuranceSubTypes pist where pist.insurancesubtypeid=pi1.insurancesubtypeid and pist.id=pi1.packetbrokerageid) end) else 0 end) end as brokeragepercentage from policies p1 inner join policyitems pi1 on pi1.policyid=p1.id inner join clients c1 on p1.clientid=c1.id where p1.discard=0 and pi1.insurancesubtypeid=" + ist.ID + @" and p1.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p1.applicationdate<='" + y2 + - +m2 + - +d2 + "'"; queryCalcBrokerageInOtherClases = @"select pi.id, pei.Value, case when pi.brokerageid is not null then (case when c1.islaw=1 then (select b.percentageforlaws from brokerages b where b.insurancesubtypeid=pi.insurancesubtypeid and b.id=pi.brokerageid) else (select b.percentageforprivates from brokerages b where b.insurancesubtypeid=pi.insurancesubtypeid and b.id=pi.brokerageid) end) else (case when pi.packetbrokerageid is not null then (case when c1.islaw=1 then (select pist.BrokeragePecentageForPrivates from PacketsInsuranceSubTypes pist where pist.insurancesubtypeid=pi.insurancesubtypeid and pist.id=pi.packetbrokerageid) else (select pist.BrokeragePecentageForLaws from PacketsInsuranceSubTypes pist where pist.insurancesubtypeid=pi.insurancesubtypeid and pist.id=pi.packetbrokerageid) end) else 0 end) end as brokeragepercentage from policies p ,policyitems pi, clients c1, policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist where p.discard=0 and p.clientid=c1.id and caist.insurancesubtypeid=" + ist.ID + @" and pi.policyid=p.id and caist.controlid=pei.controlid and caist.isactive=1 and pei.policyitemid=pi.id and pei.controlid=c.id and pei.Value<>'0' " + @" and p.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'"; queryRealPaidValues = @"select sum(fcpvpist.paidvalue) as sumpaidvalue from FacCollPaidValuesPerInsSubTypes fcpvpist,facturecollectedpaidvalues fcpv, factureitems fi, factures f where fcpvpist.facturecollectedpaidvalueid=fcpv.id and fcpv.factureitemid=fi.id and fi.factureid=f.id and f.discard=0 and fcpv.paiddate>='" + y1 + - +m1 + - +d1 + "' and fcpv.paiddate<='" + y2 + - +m2 + - +d2 + "'" + @" and fcpvpist.insurancesubtypeid=" + ist.ID; queryGetFacturedBrokeragesPayments = @"select sum(ppist.brokeragevalue) as tmpValueDecimal from PaymentsPerInsSubTypes ppist, payments pa, rates r, policyitems pi, policyitemfactureitems pifi, factureitems fi, factures f, policies p, facturepayments fp where ppist.paymentid=pa.id and pa.rateid=r.id and r.policyitemid=pi.id and pi.policyid=p.id and pifi.policyitemid=pi.id and pifi.factureitemid=fi.id and fi.factureid=f.id and fp.factureid=f.id and fp.paymentid=pa.id and f.discard=0 and p.discard=0 and p.applicationdate>='" + y1 + - +m1 + - +d1 + "'" + @" and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'" + @" and ppist.insurancesubtypeid=" + ist.ID; } List <SummaryClass> summaryListFullPremValue = dcdc.ExecuteQuery <SummaryClass>(queryCountAndPremiumFullPremiumValue).ToList(); List <SummaryClass> summaryListDef = dcdc.ExecuteQuery <SummaryClass>(queryCountAndPremiumDef).ToList(); List <SummaryClass> summaryListInOtherClasses = dcdc.ExecuteQuery <SummaryClass>(queryCountAndPremiumInOtherClasses).ToList(); List <SummaryCalcBrokerageClass> summaryListBrok = dcdc.ExecuteQuery <SummaryCalcBrokerageClass>(getCalculatedBrokerages).ToList(); List <SummaryCalcBrokerageInOtherClass> summaryListCalcBrokerageInOtherClass = dcdc.ExecuteQuery <SummaryCalcBrokerageInOtherClass>(queryCalcBrokerageInOtherClases).ToList(); //List<SummaryCalcRealBrokerageClass> summaryListCalcRealBrokerageClass = dcdc.ExecuteQuery<SummaryCalcRealBrokerageClass>(queryRealPaidValues).ToList(); decimal facturedBrokValue = 0; List <SummaryForFacturedBrokerages> summaryForFacturedBrokeragesPayments = dcdc.ExecuteQuery <SummaryForFacturedBrokerages>(queryGetFacturedBrokeragesPayments).ToList(); if (summaryForFacturedBrokeragesPayments.Count > 0) { if (summaryForFacturedBrokeragesPayments[0].tmpValueDecimal != null) { facturedBrokValue += (decimal)summaryForFacturedBrokeragesPayments[0].tmpValueDecimal; } } totFacturedBrokerageValuePerClass += facturedBrokValue; foreach (SummaryCalcBrokerageClass scbc in summaryListBrok) { if (scbc.basicvalue != null) { //tmpBrokBaseValue = RateController.RateController.Scale5((scbc.brokeragepercentage / 100) * (scbc.premiumvalue - Convert.ToInt32(scbc.basicvalue))); tmpBrokBaseValue = (scbc.brokeragepercentage / 100) * (scbc.premiumvalue - Convert.ToInt32(scbc.basicvalue)); brokValue += tmpBrokBaseValue; tmpBrokBaseValueTot += tmpBrokBaseValue; } else { //tmpBrokBaseValue = RateController.RateController.Scale5((scbc.brokeragepercentage / 100) * scbc.premiumvalue); tmpBrokBaseValue = (scbc.brokeragepercentage / 100) * scbc.premiumvalue; brokValue += tmpBrokBaseValue; tmpBrokBaseValueTot += tmpBrokBaseValue; } //tmpBrokValue = RateController.RateController.Scale5((scbc.brokeragepercentage / 100) * scbc.premiumvalue); tmpBrokValue = (scbc.brokeragepercentage / 100) * scbc.premiumvalue; tmpBrokValueTot += tmpBrokValue; } foreach (SummaryCalcBrokerageInOtherClass scbioc in summaryListCalcBrokerageInOtherClass) { //tmpBrokValueInOtherClass += RateController.RateController.Scale5((scbioc.brokeragepercentage / 100) * Convert.ToDecimal(scbioc.value)); tmpBrokValueInOtherClass += (scbioc.brokeragepercentage / 100) * Convert.ToDecimal(scbioc.value); //brokValue += RateController.RateController.Scale5((scbioc.brokeragepercentage / 100) * Convert.ToDecimal(scbioc.value)); brokValue += (scbioc.brokeragepercentage / 100) * Convert.ToDecimal(scbioc.value); } numberOfPolicies += summaryListFullPremValue[0].Count + summaryListInOtherClasses[0].Count; totalNumberOfPolicies += summaryListFullPremValue[0].Count; int inCiclusPremValue = 0; if (summaryListFullPremValue[0].SumPremiumValue != null) { inCiclusPremValue += (int)summaryListFullPremValue[0].SumPremiumValue; } if (summaryListDef[0].SumPremiumValue != null) { inCiclusPremValue -= (int)summaryListDef[0].SumPremiumValue; } if (summaryListInOtherClasses[0].SumPremiumValue != null) { inCiclusPremValue += (int)summaryListInOtherClasses[0].SumPremiumValue; } premiumValue += inCiclusPremValue; //if (summaryListCalcRealBrokerageClass[0].sumpaidvalue != null) { // realBrokValue += (decimal)summaryListCalcRealBrokerageClass[0].sumpaidvalue; //} } vals[2] = numberOfPolicies; vals[3] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(premiumValue)); totalPremiumValue += premiumValue; vals[5] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(brokValue)); totalBrokValue += brokValue; //vals[4] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(realBrokValue)); //vals[4] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(totFacturedValueInClass)); vals[4] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(totFacturedBrokerageValuePerClass)); //totalRealBrokValue += realBrokValue; //totalRealBrokValue += totFacturedValueInClass; totalRealBrokValue += totFacturedBrokerageValuePerClass; pdf.AddDataRowWithBorder(vals, headers.Count(), codes); } object[] valsTotal = new object[headers.Count()]; valsTotal[0] = "ВКУПНО"; valsTotal[1] = ""; valsTotal[2] = totalNumberOfPolicies; valsTotal[3] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(totalPremiumValue)); valsTotal[5] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(totalBrokValue)); valsTotal[4] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(totalRealBrokValue)); pdf.AddDataRowWithBorder(valsTotal, headers.Count(), codes); pdf.AddTable(); pdf.FinishPDF(); }
public static void AllPolicies(DateTime fromDate, DateTime toDate, int insuranceTypeID, int insuranceSubTypeID, int userID, int branchID, int companyID) { DataClassesDataContext dcdc = new DataClassesDataContext(); DateTime dt1 = fromDate; DateTime dt2 = toDate; int d1 = dt1.Day; int m1 = dt1.Month; int y1 = dt1.Year; int d2 = dt2.Day; int m2 = dt2.Month; int y2 = dt2.Year; string query = @"select clients.name ,clients.embg, policyItems.policynumber,policies.startdate, policies.enddate, users.username, policyitems.premiumvalue, policyitems.realpremiumvalue, case when policyItems.brokerageid is not null then (case when clients.islaw=1 then (select b.percentageforlaws from brokerages b where b.insurancesubtypeid=policyItems.insurancesubtypeid and b.id=policyItems.brokerageid) else (select b.percentageforprivates from brokerages b where b.insurancesubtypeid=policyItems.insurancesubtypeid and b.id=policyItems.brokerageid) end) else (case when policyItems.packetbrokerageid is not null then (case when clients.islaw=1 then (select pist.BrokeragePecentageForPrivates from PacketsInsuranceSubTypes pist where pist.insurancesubtypeid=policyItems.insurancesubtypeid and pist.id=policyItems.packetbrokerageid) else (select pist.BrokeragePecentageForLaws from PacketsInsuranceSubTypes pist where pist.insurancesubtypeid=policyItems.insurancesubtypeid and pist.id=policyItems.packetbrokerageid) end) else 0 end) end as brokeragepercentage, insurancesubtypes.shortdescription as insurancetypename from clients INNER JOIN policies ON policies.clientid = clients.id INNER JOIN policyItems ON policyItems.policyid = policies.id INNER JOIN users ON users.id = policies.userID INNER JOIN insuranceSubTypes ON policyItems.InsuranceSubTypeID = insuranceSubTypes.ID INNER JOIN insuranceTypes ON insuranceSubTypes.InsuranceTypeID = insuranceTypes.id where policies.applicationdate >='" + y1 + - +m1 + - +d1 + "' and policies.applicationdate<='" + y2 + - +m2 + - +d2 + "' and policies.Discard = 0"; if (insuranceTypeID > 0) { query += @" and insuranceTypes.ID = " + insuranceTypeID; } if (insuranceSubTypeID > 0) { query += @" and insuranceSubTypes.ID = " + insuranceSubTypeID; } if (userID > 0) { query += @" and users.ID = " + userID; } if (branchID > 0) { query += @" and policies.BranchID = " + branchID; } if (companyID > 0) { query += @" and policies.InsuranceCompanyID=" + companyID; } IEnumerable <Summary> summaryList = dcdc.ExecuteQuery <Summary>(query); PDFCreators pdf = new PDFCreators(false, 15, 15, 15, 15); pdf.OpenPDF(); pdf.SetTitle("Продадени полиси за даден период"); string[] headersP = { "РБ", "Број на полиса", "Осигуреник", "Почеток", "Истек", "Премија за наплата", "Брокеража(%)", "Брокеража", "Агент", "Тип" }; float[] policyColumnsWidths = { 6, 9, 22, 9, 9, 9, 9, 9, 9, 9 }; pdf.CreateTable(headersP.Length, false, headersP, "", policyColumnsWidths); decimal PremValue = 0; decimal BrokerageValue = 0; object[] vals; int i = 0; foreach (Summary s in summaryList) { i++; vals = new object[headersP.Length]; vals[0] = i.ToString(); vals[1] = s.policynumber; vals[2] = s.name; vals[3] = s.startdate.ToShortDateString(); vals[4] = s.enddate.ToShortDateString(); vals[5] = String.Format("{0:#,0.00}", s.premiumvalue); vals[6] = s.brokeragepercentage; decimal brokValue = (s.realpremiumvalue * s.brokeragepercentage / 100); vals[7] = String.Format("{0:#,0.00}", brokValue); vals[8] = s.username; vals[9] = s.insurancetypename; PremValue += s.premiumvalue; BrokerageValue += brokValue; pdf.AddDataRow1(vals, headersP.Length); } vals = new object[headersP.Length]; vals[0] = ""; //i.ToString(); vals[1] = ""; vals[2] = ""; vals[3] = ""; vals[4] = "Вкупно"; vals[5] = String.Format("{0:#,0.00}", PremValue); vals[6] = ""; vals[7] = String.Format("{0:#,0.00}", BrokerageValue); vals[8] = ""; vals[9] = ""; pdf.AddDataRow1(vals, headersP.Length); pdf.AddTable(); pdf.FinishPDF(); }
public static void GetNumberStatisticsForTimePeriod(DateTime startDate, DateTime endDate) { DataClassesDataContext dcdc = new DataClassesDataContext(); DateTime dt1 = startDate; DateTime dt2 = endDate; int d1 = dt1.Day; int m1 = dt1.Month; int y1 = dt1.Year; int d2 = dt2.Day; int m2 = dt2.Month; int y2 = dt2.Year; string s = @"select it.ID as TypeID,ic.ID as CompanyID,sum(pitems.premiumvalue) as TotalSum,count(pitems.id) as TotalCount, it.IsForLive as IsForLive from policyitems pitems INNER JOIN policies p ON pitems.policyid =p.id INNER JOIN insurancesubtypes ist ON pitems.InsuranceSubTypeID = ist.ID INNER JOIN insurancetypes it ON it.ID = ist.insurancetypeID INNER JOIN insuranceCompanies ic ON ic.ID = p.insuranceCompanyID where p.applicationdate>='" + y1 + - +m1 + - +d1 + "' and p.applicationdate<='" + y2 + - +m2 + - +d2 + @"'and p.discard = 0 group by it.ID, ic.ID,it.IsForLive order by it.ID"; List <ReportClass> summaryList = dcdc.ExecuteQuery <ReportClass>(s).ToList(); List <InsuranceCompany> icList = new List <InsuranceCompany>(); foreach (ReportClass rc in summaryList) { InsuranceCompany ic = icList.Where(company => company.ID == rc.CompanyID).SingleOrDefault(); if (ic == null) { icList.Add(InsuranceCompany.Get(rc.CompanyID)); } } icList = icList.OrderBy(c => c.ID).ToList(); PDFCreators pdf = new PDFCreators(false, 25, 25, 25, 25); pdf.OpenPDF(); pdf.SetTitle(" "); pdf.SetTitle(" "); pdf.AddJDBLogo(660, 480); pdf.SetTitle("ЗБИРНА СТАТИСТКА ЗА БРОЈ НА ПОЛИСИ ПО КЛАСИ НА ОСИГУРУВАЊЕ"); pdf.SetTitle("ЗА ПЕРИОД " + startDate.ToShortDateString() + " - " + endDate.ToShortDateString()); pdf.SetTitleLeft(""); string[] headers = new string[icList.Count + 3]; headers[0] = ""; headers[1] = "Класи на осигурување"; for (int i = 0; i < icList.Count; i++) { headers[2 + i] = icList[i].ShortName; } headers[icList.Count + 2] = "Вкупно"; TypeCode[] codes = new TypeCode[headers.Count()]; codes[0] = TypeCode.Int32; codes[1] = TypeCode.String; for (int i = 0; i <= icList.Count; i++) { codes[2 + i] = TypeCode.Int32; } float[] policyColumnsWidths = new float[headers.Count()]; policyColumnsWidths[0] = 2; policyColumnsWidths[1] = 18; for (int i = 0; i <= icList.Count; i++) { policyColumnsWidths[2 + i] = 80 / (icList.Count + 1); } int count = 0; List <InsuranceType> itList = InsuranceType.Select().OrderBy(c => c.ID).ToList(); pdf.CreateTableWithBorder(headers.Count(), false, headers, "", policyColumnsWidths); foreach (InsuranceType it in itList) { decimal totalTypeCount = 0; List <ReportClass> subList = summaryList.Where(sl => sl.TypeID == it.ID).ToList(); object[] vals = new object[headers.Count()]; vals[0] = ++count; vals[1] = it.ShortName; for (int i = 0; i < icList.Count; i++) { ReportClass rcItem = subList.Where(cl => cl.CompanyID == icList[i].ID).SingleOrDefault(); if (rcItem == null) { vals[i + 2] = 0; } else { vals[i + 2] = String.Format("{0:#,0}", rcItem.TotalCount); totalTypeCount += rcItem.TotalCount; } } vals[headers.Count() - 1] = String.Format("{0:#,0}", totalTypeCount); pdf.AddDataRowWithBorder(vals, headers.Count(), codes); } // za kreiranje na vkupno object[] totalVals = new object[headers.Count()]; totalVals[0] = ""; totalVals[1] = "Вкупно неживот"; decimal total = 0; for (int i = 0; i < icList.Count; i++) { List <ReportClass> rcSubList = summaryList.Where(c => c.CompanyID == icList[i].ID && c.IsForLive == false).ToList(); decimal totalForCompany = 0; foreach (ReportClass rc in rcSubList) { totalForCompany += rc.TotalCount; } totalVals[i + 2] = String.Format("{0:#,0}", totalForCompany); total += totalForCompany; } totalVals[headers.Count() - 1] = String.Format("{0:#,0}", total); pdf.AddDataRowWithBorder(totalVals, headers.Count(), codes); object[] totalValsLive = new object[headers.Count()]; totalValsLive[0] = ""; totalValsLive[1] = "Вкупно живот"; decimal totalLive = 0; for (int i = 0; i < icList.Count; i++) { List <ReportClass> rcSubList = summaryList.Where(c => c.CompanyID == icList[i].ID && c.IsForLive == true).ToList(); decimal totalForCompany = 0; foreach (ReportClass rc in rcSubList) { totalForCompany += rc.TotalCount; } totalValsLive[i + 2] = String.Format("{0:#,0}", totalForCompany); totalLive += totalForCompany; } totalValsLive[headers.Count() - 1] = String.Format("{0:#,0}", totalLive); pdf.AddDataRowWithBorder(totalValsLive, headers.Count(), codes); pdf.AddTable(); pdf.FinishPDF(); }
public static void PrintSkadencar(DateTime fromDate, DateTime toDate, List <int> lstBranches, List <int> lstInsuranceCompanies, List <int> lstInsuranceSubTypes) { DataClassesDataContext dcdc = new DataClassesDataContext(); DateTime dt1 = fromDate; DateTime dt2 = toDate; int d1 = dt1.Day; int m1 = dt1.Month; int y1 = dt1.Year; int d2 = dt2.Day; int m2 = dt2.Month; int y2 = dt2.Year; PDFCreators pdf = new PDFCreators(false, 10, 10, 10, 10); pdf.OpenPDF(); pdf.SetTitle("Скаденцар"); pdf.SetTitle("Истек на полиса од " + fromDate.ToShortDateString() + " до " + toDate.ToShortDateString()); pdf.SetTitleLeft10("Датум и време на печатење: " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString()); string query = @"select p.id, pi.id as policyItemID, pi.policynumber, p.applicationdate, ist.shortdescription as insurancesubtypename, ic.shortname as insurancecompanyname, c.name as clientname, o.name as ownername, it.shortname as insurancetypename, p.startdate, p.enddate, ist.id as insuranceSubTypeID from policies p inner join policyitems pi on pi.policyid = p.id inner join clients c on p.clientid = c.id inner join clients o on p.ownerid = o.id inner join insurancecompanies ic on p.insurancecompanyid=ic.id inner join insurancesubtypes ist on pi.insurancesubtypeid=ist.id inner join insurancetypes it on ist.insurancetypeid=it.id where p.branchid in (" + ReportFinPolicies.GetFromIDs(lstBranches) + ") " + @" and p.insurancecompanyid in (" + ReportFinPolicies.GetFromIDs(lstInsuranceCompanies) + ") " + @" and pi.insurancesubtypeid in (" + ReportFinPolicies.GetFromIDs(lstInsuranceSubTypes) + ") " + @" and p.enddate>='" + y1 + - +m1 + - +d1 + "'" + @" and p.enddate<='" + y2 + - +m2 + - +d2 + "'" + @" and p.discard = 0 " + @" order by p.enddate"; //@" --query so marketing agent"; List <SummaryForSkadencar> lst = dcdc.ExecuteQuery <SummaryForSkadencar>(query).ToList(); int counter = 1; foreach (SummaryForSkadencar ss in lst) { string[] headers = new string[5]; headers[0] = "Р.бр.: " + counter.ToString(); headers[1] = "Полиса: " + ss.policynumber; headers[2] = "Класа: " + ss.insurancetypename; headers[3] = "Подкласа: " + ss.insurancesubtypename; headers[4] = "О.компанија: " + ss.insurancecompanyname; TypeCode[] codes = new TypeCode[headers.Length]; codes[0] = TypeCode.String; codes[1] = TypeCode.String; codes[2] = TypeCode.String; codes[3] = TypeCode.String; codes[4] = TypeCode.String; float[] policyColumnsWidths = new float[headers.Length]; policyColumnsWidths[0] = 16; policyColumnsWidths[1] = 20; policyColumnsWidths[2] = 24; policyColumnsWidths[3] = 24; policyColumnsWidths[4] = 16; pdf.CreateTableForSkadencar(headers.Count(), false, headers, "", policyColumnsWidths); object[] vals = new object[headers.Count()]; vals[0] = "Издадена на: " + ss.applicationdate.ToShortDateString(); vals[1] = "Скаденца: " + ss.startdate.ToShortDateString() + " - " + ss.enddate.ToShortDateString(); vals[2] = "Договорувач: " + ss.clientname; vals[3] = "Осигуреник: " + ss.ownername; string regNumber = ""; Broker.DataAccess.Control con = Broker.DataAccess.Control.GetByInsuranceSubTypeAndTexbBoxID(ss.insuranceSubTypeID, Broker.DataAccess.Control.REGISTRATION_NUMBER); if (con != null) { PolicyExtendInformation regNumberPEI = PolicyExtendInformation.GetByPolicyItemAndControl(ss.policyItemID, con.ID); if (regNumberPEI != null) { regNumber = regNumberPEI.Value; } } vals[4] = "Регистрација: " + regNumber; pdf.AddDataRowWithBorder(vals, headers.Count(), codes); pdf.AddTable(); counter++; } pdf.FinishPDF_FileName("Skadencar"); }