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);
}