public static void printIncExpReceipt(string id_value) { var dt = common.getDataSet(@"SELECT TR.*,FORMAT(TR.TRANS_DATE,'dd-MMM-yyyy') TR_DATE,EX.NAME,EX.TYPE FROM INC_EXP_TRANS TR,INC_EXP_HEADS EX WHERE TR.INC_EXP_HEAD=EX.ID AND TR.ID = " + id_value).Tables[0]; if (dt.Rows.Count <= 0) { MessageBox.Show("No Records Found"); return; } DataRow dr = dt.Rows[0]; string desc = dr["description"].ToString(); string amount = dr["amount"].ToString(); string period = dr["tr_date"].ToString(); string pay_method = dr["pay_method"].ToString(); string type = dr["type"].ToString(); string inc_exp_name = dr["name"].ToString(); string trans_details = ""; string rcpt_no = id_value.PadLeft(6, '0'); if (type == "Income") { period = "Received On " + period; desc = "Received From " + desc; trans_details = "Received "; rcpt_no = "Receipt No. : " + rcpt_no; } else { period = "Paid On " + period; desc = "Paid To " + desc; trans_details = "Paid "; rcpt_no = "Voucher No. : " + rcpt_no; } Dictionary <string, object> frmParams = new Dictionary <string, object>(); Dictionary <string, string> rptParams = new Dictionary <string, string>(); trans_details += string.Format("an amount of {0} as {1} by {2}", amount, inc_exp_name, pay_method); rptParams.Add("Period", period); rptParams.Add("Name", desc); rptParams.Add("trans_details", trans_details); rptParams.Add("Report_Heading", rptHeading); rptParams.Add("Report_Sub_Heading", rptSubHeading); rptParams.Add("rcpt_no", rcpt_no); frmParams.Add("reportParams", rptParams); frmParams.Add("reportName", "inc_exp_voucher.rdlc"); Form objForm = new Report_Viewer(frmParams); common.showForm(objForm); }
private void btn_show_Click(object sender, EventArgs e) { string from_date = dp_from.Value.ToString("dd-MMM-yyyy"); string to_date = dp_to.Value.ToString("dd-MMM-yyyy"); string selCmd = string.Format(@"select REPLACE(STR(id, 6), SPACE(1), '0') Rcpt_No,Name,Amount,[Pay Method] 'Pay_Method',Comments,Type,format(Date,'dd-MMM-yyyy') Date from(select fd.id,Name,fd.total_paid 'Amount',fd.payment_method 'Pay Method',fd.Comments,ie.Type,fd.date_created 'Date' from fees_details fd,inc_exp_heads ie where fd.inc_exp_head = ie.id union select eit.id,Name,eit.Amount,eit.pay_method 'Pay Method',eit.Comments,ie.Type,eit.date_created 'Date' from inc_exp_trans eit,inc_exp_heads ie where eit.inc_exp_head = ie.id union select rd.id,Name,rd.Amount,rd.pay_method 'Pay Method',rd.Comments,ie.Type,rd.date_created 'Date' from rent_details rd,inc_exp_heads ie where rd.inc_exp_head = ie.id) a where format(date,'dd-MMM-yyyy') between '{0}' and '{1}' order by a.type desc", from_date, to_date); var ds = common.getDataSet(selCmd); if (ds.Tables[0].Rows.Count <= 0) { MessageBox.Show("No Records Found"); return; } Dictionary <string, object> frmParams = new Dictionary <string, object>(); Dictionary <string, string> rptParams = new Dictionary <string, string>(); string period = "Income Expense Status From " + from_date + " To " + to_date; string rptHeading = ConfigurationManager.AppSettings["Institute_Name"].ToString(); string rptSubHeading = ConfigurationManager.AppSettings["Jamaat_Name"].ToString(); rptParams.Add("Period", period); rptParams.Add("Report_Heading", rptHeading); rptParams.Add("Report_Sub_Heading", rptSubHeading); frmParams.Add("reportDataSrc", ds.Tables[0]); frmParams.Add("reportParams", rptParams); frmParams.Add("reportName", "inc_exp_status.rdlc"); Form objForm = new Report_Viewer(frmParams); common.showForm(objForm); }
public static void printRentReceipt(string id_value) { var dt = common.getDataSet(@"SELECT PR.NAME 'stu_name',TN.NAME 'Tenant',RD.PAY_METHOD, FORMAT(RD.RECVD_ON,'dd-MMM-yyyy') 'Recvd On',RD.AMOUNT, FORMAT(RD.RENT_FROM,'dd-MMM-yyyy') 'Rent From',FORMAT(RD.RENT_TO,'dd-MMM-yyyy') 'Rent To' FROM LEASE_DETAILS LD INNER JOIN PROPERTIES PR ON LD.PROPERTY = PR.ID INNER JOIN tenants TN ON TN.ID = LD.TENANT INNER JOIN RENT_DETAILS RD ON RD.lease_id = LD.ID AND RD.ID =" + id_value).Tables[0]; if (dt.Rows.Count <= 0) { MessageBox.Show("No Records Found"); return; } DataRow dr = dt.Rows[0]; string property = dr["stu_name"].ToString(); string tenant = dr["Tenant"].ToString(); string period = string.Format("Rent For {0} To {1}", dr["Rent From"].ToString(), dr["Rent To"].ToString()); string pay_method = dr["pay_method"].ToString(); string trans_details = ""; string todays_date = "Received On: " + dr["Recvd On"].ToString(); string amount = dr["Amount"].ToString(); string rcpt_no = "Receipt No. : " + id_value.PadLeft(6, '0'); Dictionary <string, object> frmParams = new Dictionary <string, object>(); Dictionary <string, string> rptParams = new Dictionary <string, string>(); trans_details += string.Format("Received an amount of {0} as Monthly Rent by {1} from {2} for Flat/Shop {3}", amount, pay_method, tenant, property); rptParams.Add("Period", period); rptParams.Add("todays_date", todays_date); rptParams.Add("trans_details", trans_details); rptParams.Add("Report_Heading", rptHeading); rptParams.Add("Report_Sub_Heading", rptSubHeading); rptParams.Add("rcpt_no", rcpt_no); frmParams.Add("reportParams", rptParams); frmParams.Add("reportName", "Rent_Receipt.rdlc"); Form objForm = new Report_Viewer(frmParams); common.showForm(objForm); }
public static void printFeesReceipt(string id_value) { string selCmd = string.Format(@"SELECT ST.NAME,ST.MHR_NO,STD.NAME 'STANDARD',FORMAT(FEES_FROM,'{0}') 'FEES_FROM', FORMAT(FEES_TO,'{0}') 'FEES_TO',FORMAT(RECVD_ON,'dd-MMM-yyyy') 'RECVD_ON',TOTAL_PAID,PAYMENT_METHOD FROM FEES_DETAILS FD,STUDENTS ST,STANDARDS STD WHERE FD.ID ={1} AND FD.STUDENT = ST.ID AND STD.ID=ST.STANDARD", "MMM-yyyy", id_value); var dt = common.getDataSet(selCmd).Tables[0]; if (dt.Rows.Count <= 0) { MessageBox.Show("No Records Found"); return; } var dr = dt.Rows[0]; Dictionary <string, object> frmParams = new Dictionary <string, object>(); Dictionary <string, string> rptParams = new Dictionary <string, string>(); string period = "Paid For " + dr["FEES_FROM"] + " To " + dr["FEES_TO"]; string mhr_no = dr["mhr_no"].ToString(); string name = dr["name"].ToString(); string standard = dr["standard"].ToString(); string fees_amount = dr["total_paid"].ToString(); string pay_method = dr["payment_method"].ToString(); string recvd_on = dr["recvd_on"].ToString(); string fees_details = string.Format("Received an amount of {0} on {1} by {2}", fees_amount, recvd_on, pay_method); string rcpt_no = "Receipt No. : " + id_value.PadLeft(6, '0'); rptParams.Add("Period", period); rptParams.Add("MHR_No", mhr_no); rptParams.Add("Name", name); rptParams.Add("Fees_details", fees_details); rptParams.Add("Rcpt_No", rcpt_no); rptParams.Add("Report_Heading", rptHeading); rptParams.Add("Report_Sub_Heading", rptSubHeading); rptParams.Add("Standard", standard); frmParams.Add("reportParams", rptParams); frmParams.Add("reportName", "fee_receipt.rdlc"); Form objForm = new Report_Viewer(frmParams); common.showForm(objForm); }
private void btn_show_Click(object sender, EventArgs e) { string from_month = "01-JUNE-" + cmb_year.SelectedValue.ToString(); string year = cmb_year.SelectedValue.ToString(); string next_year = (int.Parse(cmb_year.SelectedValue.ToString()) + 1).ToString(); string to_month = "31-MAY-" + (next_year).ToString(); string standard = cmb_standard.SelectedValue.ToString() == "0" ? "ST.STANDARD" : cmb_standard.SelectedValue.ToString(); // string selCmd = string.Format(@"SELECT MAX(ST.NAME) STU_NAME,MAX(ST.MHR_NO) MHR_NO,MAX(ST.ITS_ID) ITS_ID, // FORMAT(MIN(FD.FEES_FROM),'dd-MMM-yyyy') 'FROM',MAX(SD.NAME) STANDARD ,FORMAT(MAX(FD.FEES_TO),'dd-MMM-yyyy') 'TO', // MAX(FD.MONTHLY_FEES) MONTHLY_FEES,MAX(SEQUENCE) SEQUENCE FROM STUDENTS ST // LEFT OUTER JOIN FEES_DETAILS FD ON ST.ID = FD.STUDENT // INNER JOIN STANDARDS SD ON SD.ID = ST.STANDARD // WHERE FD.FEES_FROM >= '{0}' AND FD.FEES_TO <= '{1}' AND {2} // GROUP BY FD.STUDENT ORDER BY SEQUENCE", from_month,to_month,standard); string selCmd = string.Format(@"SELECT ID,MAX(STU_NAME) STU_NAME,MAX(MHR_NO) MHR_NO,MAX(ITS_ID) ITS_ID, MAX(FROM_DT) 'FROM',MAX(STANDARD) STANDARD ,MAX(TO_DT) 'TO', MAX(MONTHLY_FEES) MONTHLY_FEES,MAX(SEQUENCE) SEQUENCE FROM ( SELECT ST.ID 'ID',ST.NAME STU_NAME,ST.MHR_NO,ST.ITS_ID, FORMAT(FD.FEES_FROM,'dd-MMM-yyyy') 'FROM_DT',SD.NAME STANDARD , FORMAT(FD.FEES_TO,'dd-MMM-yyyy') 'TO_DT', FD.MONTHLY_FEES,SEQUENCE FROM STUDENTS ST INNER JOIN FEES_DETAILS FD ON ST.ID = FD.STUDENT INNER JOIN STANDARDS SD ON SD.ID = ST.STANDARD WHERE FD.FEES_FROM >= '{0}' AND FD.FEES_TO <= '{1}' AND ST.STANDARD = {2} UNION SELECT ST.ID 'ID',ST.NAME STU_NAME,ST.MHR_NO,ST.ITS_ID ITS_ID, NULL 'FROM_DT',SD.NAME STANDARD ,NULL 'TO_DT', 0 MONTHLY_FEES,SEQUENCE FROM STUDENTS ST INNER JOIN STANDARDS SD ON ST.STANDARD = {2} WHERE SD.ID = ST.STANDARD) A GROUP BY A.ID,A.STU_NAME ORDER BY SEQUENCE ASC", from_month, to_month, standard); Dictionary <int, string> months = new Dictionary <int, string>(); var dt_fees = common.getDataSet(selCmd).Tables[0]; if (dt_fees.Rows.Count <= 0) { MessageBox.Show("No Records Found"); return; } addMonthCols(dt_fees, months); foreach (DataRow dr in dt_fees.Rows) { if (dr["from"].ToString() == "" || dr["to"].ToString() == "") { dr["isDefaulter"] = "1"; } else { DateTime dt_from = common.parseDate(dr["from"].ToString()); DateTime dt_to = common.parseDate(dr["to"].ToString()); while (dt_from <= dt_to) { int mnth = dt_from.Month; string mnt_name = months[mnth]; dr[mnt_name] = dr["monthly_fees"]; dt_from = dt_from.AddMonths(1); } if (chk_defaulters.Checked) { DateTime till_dt = DateTime.Now.AddMonths(-1); DateTime to_dt = common.parseDate("31-May-" + next_year); DateTime from_dt = common.parseDate("01-Jun-" + year); if (till_dt > to_dt) { // if current date is greater than academic year till_dt = to_dt; } while (from_dt <= till_dt) { int mnth = from_dt.Month; string mnt_name = months[mnth]; if (dr[mnt_name].ToString() == "") { dr["isDefaulter"] = "1"; } from_dt = from_dt.AddMonths(1); } } } } if (chk_defaulters.Checked) { //string year = cmb_year.SelectedValue.ToString(); //DateTime from_dt = common.parseDate("01-Jun-" + year); dt_fees.DefaultView.RowFilter = "isDefaulter = '1'"; dt_fees = dt_fees.DefaultView.ToTable(); } Dictionary <string, object> frmParams = new Dictionary <string, object>(); Dictionary <string, string> rptParams = new Dictionary <string, string>(); string period = "For Academic Year " + year + " - " + next_year; string rptHeading = ConfigurationManager.AppSettings["Institute_Name"].ToString(); string rptSubHeading = ConfigurationManager.AppSettings["Jamaat_Name"].ToString(); rptParams.Add("Period", period); rptParams.Add("Report_Heading", rptHeading); rptParams.Add("Report_Sub_Heading", rptSubHeading); frmParams.Add("reportDataSrc", dt_fees); frmParams.Add("reportParams", rptParams); frmParams.Add("reportName", "fees_status.rdlc"); Form objForm = new Report_Viewer(frmParams); common.showForm(objForm); }