private List <SupplierPaymentRequestDetail> LoadSuppliersForPayment(int ProvinceID, string ZoneID, int year, string Month, int PaymentReqNo) { // Here you are free to do whatever data access code you like // You can invoke direct SQL queries, stored procedures, whatever string strConnection = ConfigurationManager.ConnectionStrings["UsedConnection"].ConnectionString; using (var conn = new SqlConnection(strConnection)) using (var cmd = conn.CreateCommand()) { string UserName = ""; conn.Open(); cmd.CommandText = "EXEC LoadSuppliers " + ProvinceID + ", '" + ZoneID + "', " + year + ", '" + Month + "', " + PaymentReqNo; SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); // m_BanksBranch.BranchCode, Maximum_PayableAmount_For_Scool.MAXAmount List <SupplierPaymentRequestDetail> MyList = new List <SupplierPaymentRequestDetail>(); foreach (DataRow mydataRow in dt.Rows) { SupplierPaymentRequestDetail supInfo = new SupplierPaymentRequestDetail(); supInfo.CensorsID = mydataRow["CensorsID"].ToString().Trim(); supInfo.Supplier.ID = mydataRow["ID"].ToString().Trim(); supInfo.PaymentHeaderStatus = Convert.ToString(mydataRow["Status"]); supInfo.Supplier.SupplierName = mydataRow["SupplierName"].ToString().Trim(); supInfo.Supplier.BankAccountNo = mydataRow["BankAccountNo"].ToString().Trim(); supInfo.Supplier.BankName = mydataRow["BankName"].ToString().Trim(); supInfo.BranchName = mydataRow["BranchName"].ToString().Trim(); if (mydataRow["BankID"] != DBNull.Value) { supInfo.Supplier.BankID = Convert.ToInt16(mydataRow["BankID"]); } if (mydataRow["BankBranchID"] != DBNull.Value) { supInfo.Supplier.BankBranchID = Convert.ToInt16(mydataRow["BankBranchID"]); } //supInfo.Supplier.BranchCode = mydataRow["BranchCode"].ToString().Trim(); supInfo.MAXAmount = Convert.ToDecimal(mydataRow["MAXAmount"]); supInfo.Amount = Convert.ToDecimal(mydataRow["Amount"]); MyList.Add(supInfo); } return(MyList); } }
public ActionResult GetPaymentDetailMoe(string model) { try { var filters = JsonConvert.DeserializeObject <List <FilterModel> >(model); List <SupplierPaymentRequestDetail> lstPayment = new List <SupplierPaymentRequestDetail>(); var zonedataDic = new Dictionary <string, SupplierDetailZoneLevel>(); var year = filters.Select(x => x.Year).FirstOrDefault(); var month = filters.Select(x => x.Month).FirstOrDefault(); using (var conn = new SqlConnection(_strConnection)) using (var cmd = conn.CreateCommand()) { conn.Open(); foreach (var filter in filters) { cmd.CommandText = $"EXEC GET_SupplerPaymentDetails {filter.Year},'{filter.Month}',{filter.ProvinceId},{false}"; SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); foreach (DataRow dr in dt.Rows) { SupplierPaymentRequestDetail row = new SupplierPaymentRequestDetail(); row.CensorsID = Convert.ToString(dr["CensusID"]); row.SupplierId = Convert.ToInt32(dr["SupplierId"]); row.SupplierName = Convert.ToString(dr["SupplierName"]); row.Amount = Convert.ToDecimal(dr["Amount"]); row.SupplierPaymentReqHeaderId = Convert.ToDecimal(dr["supplierPaymentReq_HeaderId"]); row.BankAccountNo = Convert.ToString(dr["BankAccountNo"]); row.BankName = Convert.ToString(dr["BankName"]); row.BranchName = Convert.ToString(dr["BranchName"]); row.BankCode = Convert.ToString(dr["BankCode"]); row.BranchCode = Convert.ToString(dr["BranchCode"]); row.ZoneName = Convert.ToString(dr["ZoneName"]); row.ProvinceName = Convert.ToString(dr["ProvinceName"]); row.ProvinceId = Convert.ToInt32(dr["ProvinceId"]); row.SupplierPaymentReqDetailId = Convert.ToInt32(dr["SupplierPaymentReq_DetailId"]); lstPayment.Add(row); } } var provinceGroup = lstPayment.GroupBy(x => x.ProvinceName); var returnModel = new SupplierDetailPayModel(); returnModel.FullTotal = lstPayment.Sum(x => x.Amount); foreach (var prov in provinceGroup) { var provinceLevel = new SupplierDetailProvinceLevel(); provinceLevel.ProvincialTotal = prov.Sum(x => x.Amount); provinceLevel.ProvinceName = prov.Key; var zoneGroup = prov.GroupBy(x => x.ZoneName); foreach (var zone in zoneGroup) { var zoneLevel = new SupplierDetailZoneLevel(); zoneLevel.ZoneTotal = zone.Sum(x => x.Amount); zoneLevel.ZoneName = zone.Key; var details = zone.ToList().Select(c => new SupplierPaymentRequestDetailMoe() { Amount = c.Amount, BankName = c.BankName, BankCode = c.BankCode, BranchCode = c.BranchCode, BranchName = c.BranchName, SupplierName = c.SupplierName, BankAccountNo = c.BankAccountNo, CensorsId = c.CensorsID, Year = year, Month = month, ProvinceId = c.ProvinceId, SupplierId = c.SupplierId, SupplierPaymentReqHeaderId = c.SupplierPaymentReqHeaderId, SupplierPaymentReqDetailId = c.SupplierPaymentReqDetailId }).ToList(); zoneLevel.SupplierPaymentRequestDetails = details; provinceLevel.SupplierDetailZoneLevel.Add(zoneLevel); zonedataDic.Add(zone.Key, zoneLevel); } returnModel.Detail.Add(provinceLevel); } ViewBag.TableData = zonedataDic; return(PartialView("_SupplierPaymentMOEGroupLevel", returnModel)); } } catch (Exception e) { return(Json(new { status = false }, JsonRequestBehavior.AllowGet)); } }
public ActionResult SupplierPaymentMoePdfGenerate(string model) { var filters = JsonConvert.DeserializeObject <List <FilterModel> >(model); List <SupplierPaymentRequestDetail> lstPayment = new List <SupplierPaymentRequestDetail>(); var year = filters.Select(x => x.Year).FirstOrDefault(); var month = filters.Select(x => x.Month).FirstOrDefault(); using (var conn = new SqlConnection(_strConnection)) using (var cmd = conn.CreateCommand()) { conn.Open(); foreach (var filter in filters) { cmd.CommandText = $"EXEC GET_SupplerPaymentDetails {filter.Year},'{filter.Month}',{filter.ProvinceId},{true}"; SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); foreach (DataRow dr in dt.Rows) { SupplierPaymentRequestDetail row = new SupplierPaymentRequestDetail(); row.CensorsID = Convert.ToString(dr["CensusID"]); row.SupplierId = Convert.ToInt32(dr["SupplierId"]); row.SupplierName = Convert.ToString(dr["SupplierName"]); row.Amount = Convert.ToDecimal(dr["Amount"]); row.SupplierPaymentReqHeaderId = Convert.ToDecimal(dr["supplierPaymentReq_HeaderId"]); row.BankAccountNo = Convert.ToString(dr["BankAccountNo"]); row.BankName = Convert.ToString(dr["BankName"]); row.BranchName = Convert.ToString(dr["BranchName"]); row.BankCode = Convert.ToString(dr["BankCode"]); row.ZoneName = Convert.ToString(dr["ZoneName"]); row.ProvinceName = Convert.ToString(dr["ProvinceName"]); lstPayment.Add(row); } } var provinceGroup = lstPayment.GroupBy(x => x.ProvinceName); var returnModel = new SupplierDetailPayModel(); returnModel.FullTotal = lstPayment.Sum(x => x.Amount); foreach (var prov in provinceGroup) { var provinceLevel = new SupplierDetailProvinceLevel(); provinceLevel.ProvincialTotal = prov.Sum(x => x.Amount); provinceLevel.ProvinceName = prov.Key; var zoneGroup = prov.GroupBy(x => x.ZoneName); foreach (var zone in zoneGroup) { var zoneLevel = new SupplierDetailZoneLevel(); zoneLevel.ZoneTotal = zone.Sum(x => x.Amount); zoneLevel.ZoneName = zone.Key; var details = zone.ToList().Select(c => new SupplierPaymentRequestDetailMoe() { Amount = c.Amount, BankName = c.BankName, BankCode = c.BankCode, BranchName = c.BranchName, SupplierName = c.SupplierName, BankAccountNo = c.BankAccountNo, CensorsId = c.CensorsID, Year = year, Month = month, Paid = "Paid" }).ToList(); zoneLevel.SupplierPaymentRequestDetails = details; provinceLevel.SupplierDetailZoneLevel.Add(zoneLevel); } returnModel.Detail.Add(provinceLevel); } return(new Rotativa.PartialViewAsPdf("_SupplierPaymentMOEPdfView", returnModel) { FileName = $"SupplierPayment_DetailsMOE_{year}_{month}.pdf" }); } }