private void button2_Click(object sender, EventArgs e)
{
this.Cursor = Cursors.WaitCursor;
string choice = comMalyTogary.SelectedIndex.ToString() + comMadynDaan.SelectedIndex.ToString();
switch (choice)
{
case "11":
{
string qry = @"SELECT docno, dbo.tblMovment.stmpcheckcred AS [check],
dbo.tblMovment.stmpdmndcred AS stmpdmnd, dbo.tblMovment.stmpkndcred AS stmpknd, dbo.tblMovment.stmpnduscred AS stmpndus, dbo.tblMovment.stmpconscred AS stmpcons, dbo.tblMovment.stmpsupcred AS stmpsup,
dbo.tblMovment.suppcomcred AS suppcom, dbo.tblMovment.stmpcontcred AS contract, dbo.tblMovment.credsum,
dbo.tblMovment.stmpcred + dbo.tblMovment.earncred + dbo.tblMovment.prftcred + dbo.tblMovment.freejbscred + dbo.tblMovment.fincombcred + dbo.tblMovment.stmpdmndcred + dbo.tblMovment.stmpkndcred + dbo.tblMovment.stmpnduscred
+ dbo.tblMovment.stmpcheckcred + dbo.tblMovment.stmpconscred + dbo.tblMovment.stmpsupcred + dbo.tblMovment.stmpcontcred + dbo.tblMovment.suppcomcred + dbo.tblMovment.internalTaxcred AS tswya
FROM dbo.tblMovment
WHERE (cast( [move_date] as datetime) BETWEEN cast ( '" + txtdateFrm.Text.Substring(6, 4) +
@"/" + txtdateFrm.Text.Substring(3, 2) + @"/" + txtdateFrm.Text.Substring(0, 2)
+ @"' as datetime) AND cast ( '" + txtdatTo.Text.Substring(6, 4) + @"/" + txtdatTo.Text.Substring(3, 2) + @"/" +
txtdatTo.Text.Substring(0, 2) + @"' as datetime) ) AND
(dbo.tblMovment.doccd = N'" + (comMostndTswya.SelectedIndex + 1).ToString() +
@"') and dbo.tblMovment.credsum <>0 ORDER BY CAST(dbo.tblMovment.docno AS int)";
SqlDataAdapter adreport = new SqlDataAdapter(qry, Static_class.con);
DataTable dt = new DataTable();
adreport.Fill(dt);
FrmReportViewer frm = new FrmReportViewer(dt, @"\report\taxclcRpt_Com.rpt",
txtdateFrm.Text,
txtdatTo.Text,
comMadynDaan.Text,
comMostndTswya.Text);
this.Cursor = Cursors.Arrow;
frm.ShowDialog();
} break;
case "10":
{
string qry = @"SELECT docno, dbo.tblMovment.stmpcheckdep AS [check],
dbo.tblMovment.stmpdmnddep AS stmpdmnd, dbo.tblMovment.stmpknddep AS stmpknd, dbo.tblMovment.stmpndusdep AS stmpndus, dbo.tblMovment.stmpconsdep AS stmpcons, dbo.tblMovment.stmpsupdep AS stmpsup,
dbo.tblMovment.suppcomdep AS suppcom, dbo.tblMovment.stmpcontdep AS contract, dbo.tblMovment.depsum,
dbo.tblMovment.stmpdep + dbo.tblMovment.earndep + dbo.tblMovment.prftdep + dbo.tblMovment.freejbsdep + dbo.tblMovment.fincombdep + dbo.tblMovment.stmpdmnddep + dbo.tblMovment.stmpknddep + dbo.tblMovment.stmpndusdep
+ dbo.tblMovment.stmpcheckdep + dbo.tblMovment.stmpconsdep + dbo.tblMovment.stmpsupdep + dbo.tblMovment.stmpcontdep + dbo.tblMovment.suppcomdep + dbo.tblMovment.internalTaxdep AS tswya
FROM dbo.tblMovment
WHERE (cast( [move_date] as datetime) BETWEEN cast ( '" + txtdateFrm.Text.Substring(6, 4) +
@"/" + txtdateFrm.Text.Substring(3, 2) + @"/" + txtdateFrm.Text.Substring(0, 2)
+ @"' as datetime) AND cast ( '" + txtdatTo.Text.Substring(6, 4) + @"/" + txtdatTo.Text.Substring(3, 2) + @"/" +
txtdatTo.Text.Substring(0, 2) + @"' as datetime) ) AND
(dbo.tblMovment.doccd = N'" + (comMostndTswya.SelectedIndex + 1).ToString() +
@"') and dbo.tblMovment.depsum <>0 ORDER BY CAST(dbo.tblMovment.docno AS int)";
SqlDataAdapter adreport = new SqlDataAdapter(qry, Static_class.con);
DataTable dt = new DataTable();
adreport.Fill(dt);
FrmReportViewer frm = new FrmReportViewer(dt, @"\report\taxclcRpt_Com.rpt",
txtdateFrm.Text,
txtdatTo.Text,
comMadynDaan.Text,
comMostndTswya.Text);
this.Cursor = Cursors.Arrow;
frm.ShowDialog();
} break;
case "00":
{
string qry = @"
SELECT docno, stmpdep AS stmp, earndep AS earn, prftdep AS prft, freejbsdep AS freejbs, fincombdep AS finComb, internalTaxdep AS internaltax,
stmpdep + earndep + prftdep + freejbsdep + fincombdep + stmpdmnddep + stmpknddep + stmpndusdep + stmpcheckdep + stmpconsdep + stmpsupdep + stmpcontdep + suppcomdep + internalTaxdep AS tswya
FROM dbo.tblMovment
WHERE (cast( [move_date] as datetime) BETWEEN cast ( '" + txtdateFrm.Text.Substring(6, 4) +
@"/" + txtdateFrm.Text.Substring(3, 2) + @"/" + txtdateFrm.Text.Substring(0, 2)
+ @"' as datetime) AND cast ( '" + txtdatTo.Text.Substring(6, 4) + @"/" + txtdatTo.Text.Substring(3, 2) + @"/" +
txtdatTo.Text.Substring(0, 2) + @"' as datetime) ) AND
(dbo.tblMovment.doccd = N'" + (comMostndTswya.SelectedIndex + 1).ToString() +
@"') and dbo.tblMovment.depsum <>0 ORDER BY CAST(dbo.tblMovment.docno AS int)";
SqlDataAdapter adreport = new SqlDataAdapter(qry, Static_class.con);
DataTable dt = new DataTable();
adreport.Fill(dt);
FrmReportViewer frm = new FrmReportViewer(dt, @"\report\taxclcRpt_Fin.rpt",
txtdateFrm.Text,
txtdatTo.Text,
comMadynDaan.Text,
comMostndTswya.Text);
this.Cursor = Cursors.Arrow;
frm.ShowDialog();
} break;
case "01":
{
string qry = @"
SELECT docno, stmpcred AS stmp, earncred AS earn, prftcred AS prft, freejbscred AS freejbs, fincombcred AS finComb, internalTaxcred AS internaltax,
stmpcred + earncred + prftcred + freejbscred + fincombcred + stmpdmndcred + stmpkndcred + stmpnduscred + stmpcheckcred + stmpconscred + stmpsupcred + stmpcontcred + suppcomcred + internalTaxcred AS tswya
FROM dbo.tblMovment
WHERE (cast( [move_date] as datetime) BETWEEN cast ( '" + txtdateFrm.Text.Substring(6, 4) +
@"/" + txtdateFrm.Text.Substring(3, 2) + @"/" + txtdateFrm.Text.Substring(0, 2)
+ @"' as datetime) AND cast ( '" + txtdatTo.Text.Substring(6, 4) + @"/" + txtdatTo.Text.Substring(3, 2) + @"/" +
txtdatTo.Text.Substring(0, 2) + @"' as datetime) ) AND
(dbo.tblMovment.doccd = N'" + (comMostndTswya.SelectedIndex + 1).ToString() +
@"') and dbo.tblMovment.credsum <>0 ORDER BY CAST(dbo.tblMovment.docno AS int)";
SqlDataAdapter adreport = new SqlDataAdapter(qry, Static_class.con);
DataTable dt = new DataTable();
adreport.Fill(dt);
FrmReportViewer frm = new FrmReportViewer(dt, @"\report\taxclcRpt_Fin.rpt",
txtdateFrm.Text,
txtdatTo.Text,
comMadynDaan.Text,
comMostndTswya.Text);
this.Cursor = Cursors.Arrow;
frm.ShowDialog();
} break;
}
}
private void btn_paid_needed_Click(object sender, EventArgs e)
{
this.Cursor = Cursors.WaitCursor;
#region create_dt_master
DataTable dt_master = new DataTable();
dt_master.Columns.Add("yr", typeof(int));
dt_master.Columns.Add("mn", typeof(int));
dt_master.Columns.Add("paid", typeof(decimal));
dt_master.Columns.Add("needed", typeof(decimal));
dt_master.Columns.Add("checkNo", typeof(string));
dt_master.Columns.Add("bank", typeof(string));
for (int i = 1; i < 13; i++)
{
dt_master.Rows.Add(new Object[] {
yyyy.Value.ToString(),
i,
0, 0, "", ""
});
}
dt_master.Rows.Add(new Object[] {
(yyyy.Value.IfNullThenZero() + 1),
1,
0, 0, "", ""
});
dt_master.PrimaryKey = new DataColumn[2] {
dt_master.Columns[0], dt_master.Columns[1]
};
#endregion
string qry = "";
string yr = yyyy.Value.IfNullThenZero().ToString();
string yr_1 = (yyyy.Value.IfNullThenZero() + 1).ToString();
#region المسدد
qry = @"SELECT YEAR([move_date]),month ([move_date]), SUM(dbo.tblMovment.earndep) AS sum
FROM dbo.tblMovment
WHERE doccd=2 and (cast( [move_date] as datetime)
BETWEEN cast ( '" + yr + @"/01/01' as datetime) AND cast ( '" + yr_1 + @"/01/01' as datetime) )
group by YEAR([move_date]),month ([move_date])
order by YEAR([move_date]),month ([move_date]) ";
SqlDataAdapter da_paid = new SqlDataAdapter(qry, Static_class.con);
DataTable dt_paid = new DataTable();
da_paid.Fill(dt_paid);
dt_paid.PrimaryKey = new DataColumn[2] {
dt_paid.Columns[0], dt_paid.Columns[1]
};
Static_class.EditMasterDataTable(dt_master, dt_paid, 2, 2, 2);
#endregion
#region المستحق
qry = @" SELECT year ([move_date]),month([move_date]), SUM(dbo.tblMovment.earncred) AS sum
FROM dbo.tblMovment
WHERE (cast( [move_date] as datetime)
BETWEEN cast ( '" + yr + @"/01/01' as datetime) AND cast ( '" + yr_1 + @"/01/01' as datetime) )
group by YEAR([move_date]),month ([move_date])
order by YEAR([move_date]),month ([move_date]) ";
SqlDataAdapter da_needed = new SqlDataAdapter(qry, Static_class.con);
DataTable dt_needed = new DataTable();
da_needed.Fill(dt_needed);
dt_needed.PrimaryKey = new DataColumn[2] {
dt_needed.Columns[0], dt_needed.Columns[1]
};
Static_class.EditMasterDataTable(dt_master, dt_needed, 2, 3, 2);
#endregion
#region رقم الشيك
qry = @"SELECT [yr],[mn],[checkNo],[bank]
FROM [TAXDB].[dbo].[earnCheckNo]
where yr=" + yr + " OR (yr=" + yr_1 + " and mn =1 )";
SqlDataAdapter da_check = new SqlDataAdapter(qry, Static_class.con);
DataTable dt_check = new DataTable();
da_check.Fill(dt_check);
dt_check.PrimaryKey = new DataColumn[2] {
dt_check.Columns[0], dt_check.Columns[1]
};
Static_class.EditMasterDataTable(dt_master, dt_check, 2, 4, 2);
Static_class.EditMasterDataTable(dt_master, dt_check, 2, 5, 3);
#endregion
dt_master.Rows[0]["paid"] = "0";
dt_master.Rows[dt_master.Rows.Count - 1]["needed"] = "0";
FrmReportViewer frm = new FrmReportViewer(dt_master, @"\report\earnCheck.rpt", yr.ToString());
this.Cursor = Cursors.Arrow;
frm.ShowDialog();
}