private void button1_Click(object sender, EventArgs e)
{
if (checkEmptyComp(panel3) == 0)
{
Static_class.reportdb = 2;
Static_class.rptlbl5 = "الكــل";
string tblsql = @" SELECT dbo.tblMovment.docno, dbo.tblMovment.earncred AS cred, TBLlabel.lblnm As lbl , dbo.tblMovment.earndep AS dep, '" + datfrmearn.Text
+ @"' AS datefrm, '" + dattoearn.Text + @"' AS dateto,
dbo.TBLdoctyp.docnm AS typ
FROM TBLlabel RIGHT OUTER JOIN
tblMovment ON TBLlabel.lblcd = tblMovment.lblcd LEFT OUTER JOIN
TBLdoctyp ON tblMovment.doccd = TBLdoctyp.doccd
WHERE (CONVERT(datetime, dbo.tblMovment.mov_date, 103) BETWEEN CONVERT(datetime, '" + datfrmearn.Text + @"', 103) AND CONVERT(datetime, '" + dattoearn.Text + @"', 103)) AND
(dbo.tblMovment.doccd = N'" + doccd.SelectedValue.ToString() + "') and (dbo.tblMovment.earndep+dbo.tblMovment.earncred)>0 order by dbo.tblMovment.docno ";
Static_class.fillTbl(tblsql);
Static_class.rptlbl1 = "";
Static_class.rptlbl2 = "";
Static_class.rptlbl3 = "";
Static_class.rptlbl4 = "";
Static_class.reportname = "workEarnrpt";
Static_class.sysprint();
}
}
private void rprt_Click(object sender, EventArgs e)
{
Static_class.reportdb = 2;
Static_class.rptlbl5 = "الكــل";
string tblsql = "";
tblsql = @" SELECT dbo.tblMovment.docno, dbo.tblMovment.docsubno, dbo.TBLsuppliers.supnm, dbo.tblMovment.mov_date as [date] , dbo.tblMovment.prftcred,
dbo.tblMovment.freejbscred, dbo.taxkd.taxnm, dbo.tblMovment.supcd,taxfileNo,taxrecNo
FROM dbo.TBLsuppliers INNER JOIN
dbo.taxkd ON dbo.TBLsuppliers.taxcd = dbo.taxkd.taxcd RIGHT OUTER JOIN
dbo.tblMovment ON dbo.TBLsuppliers.supcd = dbo.tblMovment.supcd where dbo.tblMovment.supcd ='" + supcd.Text + "' order by dbo.tblMovment.mov_date ";
Static_class.fillTbl(tblsql);
Static_class.rptlbl1 = "";
Static_class.rptlbl2 = "";
Static_class.rptlbl3 = "";
Static_class.rptlbl4 = "";
Static_class.reportname = "rptbscsupdt";
Static_class.sysprint();
}
private void button7_Click(object sender, EventArgs e)
{
if (checkEmptyComp(panel4) == 0)
{
Static_class.reportdb = 2;
Static_class.rptlbl5 = "الكــل";
string fieldnm = (comboBox2.Text == "مدين" ? "earndep" : "earncred");
string tblsql = @" SELECT '" + button7.Text + @" / " + comboBox2.Text + @"' as taxtyp, '" + paieddatFrm.Text + @"' as datfrm,'" + paieddatto.Text + @"' as datto, SUM(dbo.tblMovment." + fieldnm + @") AS sum, dbo.TBLlabel.lblnm,dbo.TBLdoctyp.docnm
FROM dbo.tblMovment LEFT OUTER JOIN
dbo.TBLdoctyp ON dbo.tblMovment.doccd = dbo.TBLdoctyp.doccd LEFT OUTER JOIN
dbo.TBLlabel ON dbo.tblMovment.lblcd = dbo.TBLlabel.lblcd
WHERE (cast( [move_date] as datetime) BETWEEN cast ( '" + paieddatFrm.Text.Substring(6, 4) + @"/" + paieddatFrm.Text.Substring(3, 2) + @"/" + paieddatFrm.Text.Substring(0, 2) + @"' as datetime) AND cast ( '" + paieddatto.Text.Substring(6, 4) + @"/" + paieddatto.Text.Substring(3, 2) + @"/" + paieddatto.Text.Substring(0, 2) + @"' as datetime) ) GROUP BY dbo.TBLlabel.lblnm, dbo.TBLdoctyp.docnm ";
Static_class.fillTbl(tblsql);
Static_class.rptlbl1 = "";
Static_class.rptlbl2 = "";
Static_class.rptlbl3 = "";
Static_class.rptlbl4 = "";
Static_class.reportname = "taxPaiedRpt";
Static_class.sysprint();
}
}
private void button6_Click(object sender, EventArgs e)
{
string fieldnm = comboBox2.Text; //== "مدين"
string tblsql;
if (checkEmptyComp(panel4) == 0)
{
Static_class.reportdb = 2;
Static_class.rptlbl5 = "الكــل";
if (fieldnm == "مدين")
{
tblsql = @" SELECT '" + button6.Text + @" / " + comboBox2.Text + @"' as taxtyp, '" + paieddatFrm.Text + @"' as datfrm,'" + paieddatto.Text + @"' as datto,sum( (dbo.tblMovment.depsum - dbo.tblMovment.suppcomdep)) AS sum, dbo.TBLlabel.lblnm,dbo.TBLdoctyp.docnm
FROM dbo.tblMovment LEFT OUTER JOIN
dbo.TBLdoctyp ON dbo.tblMovment.doccd = dbo.TBLdoctyp.doccd LEFT OUTER JOIN
dbo.TBLlabel ON dbo.tblMovment.lblcd = dbo.TBLlabel.lblcd
WHERE (cast( [move_date] as datetime) BETWEEN cast ( '" + paieddatFrm.Text.Substring(6, 4) + @"/" + paieddatFrm.Text.Substring(3, 2) + @"/" + paieddatFrm.Text.Substring(0, 2) + @"' as datetime) AND cast ( '" + paieddatto.Text.Substring(6, 4) + @"/" + paieddatto.Text.Substring(3, 2) + @"/" + paieddatto.Text.Substring(0, 2) + @"' as datetime) ) AND (dbo.tblMovment.movcd ='2')GROUP BY dbo.TBLlabel.lblnm, dbo.TBLdoctyp.docnm ";
}
else
{
tblsql = @" SELECT '" + button6.Text + @" / " + comboBox2.Text + @"' as taxtyp, '" + paieddatFrm.Text + @"' as datfrm,'" + paieddatto.Text + @"' as datto,sum( (dbo.tblMovment.credsum - dbo.tblMovment.suppcomcred)) AS sum, dbo.TBLlabel.lblnm,dbo.TBLdoctyp.docnm
FROM dbo.tblMovment LEFT OUTER JOIN
dbo.TBLdoctyp ON dbo.tblMovment.doccd = dbo.TBLdoctyp.doccd LEFT OUTER JOIN
dbo.TBLlabel ON dbo.tblMovment.lblcd = dbo.TBLlabel.lblcd
WHERE (cast( [move_date] as datetime) BETWEEN cast ( '" + paieddatFrm.Text.Substring(6, 4) + @"/" + paieddatFrm.Text.Substring(3, 2) + @"/" + paieddatFrm.Text.Substring(0, 2) + @"' as datetime) AND cast ( '" + paieddatto.Text.Substring(6, 4) + @"/" + paieddatto.Text.Substring(3, 2) + @"/" + paieddatto.Text.Substring(0, 2) + @"' as datetime) ) AND (dbo.tblMovment.movcd ='2')GROUP BY dbo.TBLlabel.lblnm, dbo.TBLdoctyp.docnm ";
}
// Static_class.reportdb = 2;
// Static_class.rptlbl5 = "الكــل";
// string fieldnm = (comboBox2.Text == "مدين" ? "stmpdep" : "stmpcred");
// string tblsql = @" SELECT '" + button6.Text + @" / " + comboBox2.Text + @"' as taxtyp, '" + paieddatFrm.Text + @"' as datfrm,'" + paieddatto.Text + @"' as datto, SUM(dbo.tblMovment." + fieldnm + @") AS sum, dbo.TBLlabel.lblnm,dbo.TBLdoctyp.docnm
//FROM dbo.tblMovment LEFT OUTER JOIN
// dbo.TBLdoctyp ON dbo.tblMovment.doccd = dbo.TBLdoctyp.doccd LEFT OUTER JOIN
// dbo.TBLlabel ON dbo.tblMovment.lblcd = dbo.TBLlabel.lblcd
//WHERE (CONVERT(datetime, dbo.tblMovment.date, 103) BETWEEN CONVERT(datetime, '" + paieddatFrm.Text + @"', 103) AND CONVERT(datetime, '" + paieddatto.Text + @"', 103))GROUP BY dbo.TBLlabel.lblnm, dbo.TBLdoctyp.docnm ";
Static_class.fillTbl(tblsql);
Static_class.rptlbl1 = "";
Static_class.rptlbl2 = "";
Static_class.rptlbl3 = "";
Static_class.rptlbl4 = "";
Static_class.reportname = "taxPaiedRpt";
Static_class.sysprint();
}
}
private void button1_Click(object sender, EventArgs e)
{
if (checkEmptyComp(panel2) == 0)
{
Static_class.reportdb = 2;
Static_class.rptlbl5 = "الكــل";
string tblsql;
string mov = movcd.Text;
if (mov == "مالي")
{
tblsql = @"SELECT dbo.tblMovment.docno, dbo.tblMovment.fincombcred AS cred, dbo.tblMovment.fincombdep AS dep, dbo.TBLlabel.lblnm AS lbl, dbo.TBLmovtyp.movnm as 'typ',
'" + datfrmsh.Text + @"' AS datefrm, '" + datetosh.Text + @"' AS dateto
FROM dbo.tblMovment LEFT OUTER JOIN
dbo.TBLmovtyp ON dbo.tblMovment.movcd = dbo.TBLmovtyp.movcd LEFT OUTER JOIN
dbo.TBLlabel ON dbo.tblMovment.lblcd = dbo.TBLlabel.lblcd
WHERE (cast( [move_date] as datetime) BETWEEN cast ( '" + datfrmsh.Text.Substring(6, 4) + @"/" + datfrmsh.Text.Substring(3, 2) + @"/" + datfrmsh.Text.Substring(0, 2) + @"' as datetime) AND cast ( '" + datetosh.Text.Substring(6, 4) + @"/" + datetosh.Text.Substring(3, 2) + @"/" + datetosh.Text.Substring(0, 2) + @"' as datetime) ) AND
(dbo.tblMovment.movcd = N'" + movcd.SelectedValue.ToString() + "') order by dbo.tblMovment.docno ";
}
else
{
tblsql = @"SELECT dbo.tblMovment.docno, dbo.tblMovment.suppcomcred AS cred, dbo.tblMovment.suppcomdep AS dep, dbo.TBLlabel.lblnm AS lbl, dbo.TBLmovtyp.movnm as 'typ',
'" + datfrmsh.Text + @"' AS datefrm, '" + datetosh.Text + @"' AS dateto
FROM dbo.tblMovment LEFT OUTER JOIN
dbo.TBLmovtyp ON dbo.tblMovment.movcd = dbo.TBLmovtyp.movcd LEFT OUTER JOIN
dbo.TBLlabel ON dbo.tblMovment.lblcd = dbo.TBLlabel.lblcd
WHERE (cast( [move_date] as datetime) BETWEEN cast ( '" + datfrmsh.Text.Substring(6, 4) + @"/" + datfrmsh.Text.Substring(3, 2) + @"/" + datfrmsh.Text.Substring(0, 2) + @"' as datetime) AND cast ( '" + datetosh.Text.Substring(6, 4) + @"/" + datetosh.Text.Substring(3, 2) + @"/" + datetosh.Text.Substring(0, 2) + @"' as datetime) ) AND
(dbo.tblMovment.movcd = N'" + movcd.SelectedValue.ToString() + "') order by dbo.tblMovment.docno ";
}
Static_class.fillTbl(tblsql);
Static_class.rptlbl1 = "";
Static_class.rptlbl2 = "";
Static_class.rptlbl3 = "";
Static_class.rptlbl4 = "";
Static_class.reportname = "taxsharingrpt";
Static_class.sysprint();
}
}
private void button1_Click(object sender, EventArgs e)
{
Static_class.reportdb = 2;
Static_class.rptlbl5 = "الكــل";
string tblsql = "";
tblsql = @" SELECT dbo.TBLsuppliers.supcd, dbo.TBLsuppliers.supnm, dbo.taxkd.taxnm, dbo.taxkd.taxprc
FROM dbo.taxkd RIGHT OUTER JOIN
dbo.TBLsuppliers ON dbo.taxkd.taxcd = dbo.TBLsuppliers.taxcd ";
Static_class.fillTbl(tblsql);
Static_class.rptlbl1 = "";
Static_class.rptlbl2 = "";
Static_class.rptlbl3 = "";
Static_class.rptlbl4 = "";
Static_class.reportname = "RPTSUP";
Static_class.sysprint();
}
private void view_Click(object sender, EventArgs e)
{
if (checkEmptyComp(panbscsupdt) == 0)
{
Static_class.reportdb = 2;
Static_class.rptlbl5 = "الكــل";
string tblsql = "";
//if (cmbmonth.SelectedIndex == 0)
tblsql = @" SELECT dbo.tblMovment.docno, dbo.tblMovment.docsubno, dbo.TBLsuppliers.supnm, dbo.tblMovment.mov_date, dbo.tblMovment.prftcred, dbo.tblMovment.prftdep,
dbo.tblMovment.freejbscred, dbo.tblMovment.freejbsdep,dbo.taxkd.taxnm, dbo.tblMovment.supcd ,taxfileNo,taxrecNo , dbo.taxkd.taxprc as prc
FROM dbo.TBLsuppliers INNER JOIN
dbo.taxkd ON dbo.TBLsuppliers.taxcd = dbo.taxkd.taxcd RIGHT OUTER JOIN
dbo.tblMovment ON dbo.TBLsuppliers.supcd = dbo.tblMovment.supcd where dbo.tblMovment.supcd ='" + supcd.Text + "' and yr=" + yr.Text + " order by dbo.tblMovment.docno ";
// else
// tblsql = @" SELECT dbo.tblMovment.docno, dbo.tblMovment.docsubno, dbo.TBLsuppliers.supnm, dbo.tblMovment.mov_date, dbo.tblMovment.prftcred, dbo.tblMovment.prftdep,
// dbo.tblMovment.freejbscred,dbo.tblMovment.freejbsdep, dbo.taxkd.taxnm, dbo.tblMovment.supcd,taxfileNo,taxrecNo
// FROM dbo.TBLsuppliers INNER JOIN
// dbo.taxkd ON dbo.TBLsuppliers.taxcd = dbo.taxkd.taxcd RIGHT OUTER JOIN
// dbo.tblMovment ON dbo.TBLsuppliers.supcd = dbo.tblMovment.supcd where dbo.tblMovment.supcd ='" + supcd.Text + "' and yr=" + yr.Text + " and month(convert(datetime ,mov_date,103))=" + cmbmonth.SelectedValue.ToString() + " order by dbo.tblMovment.docno ";
Static_class.fillTbl(tblsql);
Static_class.rptlbl1 = "";
Static_class.rptlbl2 = "";
Static_class.rptlbl3 = "";
Static_class.rptlbl4 = "";
Static_class.reportname = "rptbscsupdt";
Static_class.sysprint();
}
}
private void btncstm_Click(object sender, EventArgs e)
{
Static_class.reportdb = 2;
Static_class.rptlbl5 = "الكــل";
string frm = "", tom = "";
switch (combTAX1.SelectedIndex)
{
case 0:
{
frm = "1"; tom = "12";
} break;
case 1:
{
frm = "1"; tom = "3";
} break;
case 2:
{
frm = "4"; tom = "6";
} break;
case 3:
{
frm = "7"; tom = "9";
} break;
case 4:
{
frm = "10"; tom = "12";
} break;
}
string tblsql = "";
// if (cmbmonth.SelectedIndex == 0)
// tblsql = @" SELECT dbo.tblMovment.docno, dbo.tblMovment.docsubno, dbo.TBLsuppliers.supnm, CASE WHEN MONTH(dbo.tblMovment.date) < 4 THEN 'الأولــــى' WHEN MONTH(dbo.tblMovment.date) < 7 THEN 'الثانية' WHEN MONTH(dbo.tblMovment.date)
// < 10 THEN 'الثالثة' ELSE 'الرابعة' END AS date, dbo.tblMovment.prftcred,
// dbo.tblMovment.freejbscred, dbo.taxkd.taxnm, dbo.tblMovment.supcd ,taxfileNo,taxrecNo
// FROM dbo.TBLsuppliers INNER JOIN
// dbo.taxkd ON dbo.TBLsuppliers.taxcd = dbo.taxkd.taxcd RIGHT OUTER JOIN
// dbo.tblMovment ON dbo.TBLsuppliers.supcd = dbo.tblMovment.supcd
// where dbo.TBLsuppliers.supnm is not null and yr=" + yr.Text + " order by dbo.tblMovment.docno ";
// else
tblsql = @" SELECT dbo.tblMovment.docno, dbo.tblMovment.docsubno,TBLsuppliers.taxcd,
substring( [mov_date],4,7) as dt, dbo.TBLsuppliers.supnm,
CASE WHEN MONTH(cast( [move_date] as datetime)) < 4 THEN 'الأولــــى'
WHEN MONTH(cast( [move_date] as datetime))
< 7 THEN 'الثانية' WHEN MONTH(cast( [move_date] as datetime)) < 10 THEN 'الثالثة' ELSE 'الرابعة' END AS date, dbo.tblMovment.prftcred,
dbo.tblMovment.prftdep, dbo.taxkd.taxprc AS prc, dbo.tblMovment.freejbscred, dbo.tblMovment.freejbsdep, dbo.taxkd.taxnm, dbo.tblMovment.supcd,
dbo.TBLsuppliers.taxfileNo, dbo.TBLsuppliers.taxrecNo, dbo.tblTxDep.txDep_Nm
FROM dbo.TBLsuppliers INNER JOIN
dbo.taxkd ON dbo.TBLsuppliers.taxcd = dbo.taxkd.taxcd LEFT OUTER JOIN
dbo.tblMovment ON dbo.TBLsuppliers.supcd = dbo.tblMovment.supcd LEFT OUTER JOIN
dbo.tblTxDep ON dbo.TBLsuppliers.txdep_cd = dbo.tblTxDep.txDep_Cd
where yr=" + yr.Text
;
if (numericUpDown1.Value > 0)
{
//tblsql += " and cast( dbo.tblMovment.docno as int) between "+numericUpDown1.Value.ToString()+" and "+numericUpDown2.Value.ToString();
tblsql += " and MONTH(cast( [move_date] as datetime)) >= " + numericUpDown1.Value.ToString() + " and MONTH(cast( [move_date] as datetime))<= " + numericUpDown2.Value.ToString();
}
else
{
tblsql += " and MONTH(cast( [move_date] as datetime)) >= " + frm + " and MONTH(cast( [move_date] as datetime))<= " + tom;
}
tblsql += " order by cast( [move_date] as datetime) ";
//dbo.TBLsuppliers.supnm is not null and
Static_class.fillTbl(tblsql);
Static_class.rptlbl1 = "";
Static_class.rptlbl2 = "";
Static_class.rptlbl3 = "";
Static_class.rptlbl4 = "";
Static_class.reportname = "rptbscsupdt_tot";
Static_class.sysprint();
}
private void button1_Click(object sender, EventArgs e)
{
if (checkEmptyComp(panel1) == 0)
{
Static_class.reportdb = 2;
Static_class.rptlbl5 = "الكــل";
string tblsql = "";
if (radioButton1.Checked)
{
if (docno.Text != "")
{
if (docno.Text != "")
{
tblsql = @" SELECT 'مدين' AS knd
, dbo.tblMovment.typcd, cast( dbo.tblMovment.docno as int )as docno, '" + datfrmclc.Text + "' AS datfrm, '" + dattocalc.Text + @"' AS datto, dbo.TBLdoctyp.docnm AS doctyp,
dbo.tblMovment.[internalTaxdep] as internaltax ,
dbo.tblMovment.stmpcheckdep ,
dbo.tblMovment.stmpdmnddep ,
dbo.tblMovment.earndep ,
dbo.tblMovment.stmpknddep ,
dbo.tblMovment.stmpndusdep,
dbo.tblMovment.stmpconsdep + dbo.tblMovment.stmpconscred AS stmpcons,
(dbo.tblMovment.stmpsupdep )+ (dbo.tblMovment.stmpcontdep ) AS stmpsup, dbo.tblMovment.suppcomdep AS suppcom,
dbo.tblMovment.fincombdep AS fincomb, dbo.tblMovment.freejbsdep AS freejbs,
dbo.tblMovment.prftdep AS prft, dbo.tblMovment.stmpdep AS stmp, dbo.tblMovment.depsum ,0 AS credsum
FROM dbo.tblMovment LEFT OUTER JOIN
dbo.TBLdoctyp ON dbo.tblMovment.doccd = dbo.TBLdoctyp.doccd
WHERE (cast( [move_date] as datetime) BETWEEN cast ( '" + datfrmclc.Text.Substring(6, 4) + @"/" + datfrmclc.Text.Substring(3, 2) + @"/" + datfrmclc.Text.Substring(0, 2) + @"' as datetime) AND cast ( '" + dattocalc.Text.Substring(6, 4) + @"/" + dattocalc.Text.Substring(3, 2) + @"/" + dattocalc.Text.Substring(0, 2) + @"' as datetime) ) AND
(dbo.tblMovment.doccd = N'" + comboBox1.SelectedValue.ToString() + "') and dbo.tblMovment.depsum <>0 and ( cast (docno as int ) between " + docno.Text + " and " + docno2.Text + ") order by cast( dbo.tblMovment.docno as int ) ";
}
else
{
tblsql = @" SELECT 'مدين' AS knd , dbo.tblMovment.typcd, cast( dbo.tblMovment.docno as int )as docno, '" + datfrmclc.Text + "' AS datfrm, '" + dattocalc.Text + @"' AS datto, dbo.TBLdoctyp.docnm AS doctyp,
dbo.tblMovment.[internalTaxdep] as internaltax ,
dbo.tblMovment.stmpcheckdep ,
dbo.tblMovment.stmpdmnddep ,
dbo.tblMovment.earndep ,
dbo.tblMovment.stmpknddep ,
dbo.tblMovment.stmpndusdep,
dbo.tblMovment.stmpconsdep + dbo.tblMovment.stmpconscred AS stmpcons,
(dbo.tblMovment.stmpsupdep )+ (dbo.tblMovment.stmpcontdep ) AS stmpsup, dbo.tblMovment.suppcomdep AS suppcom,
dbo.tblMovment.fincombdep AS fincomb, dbo.tblMovment.freejbsdep AS freejbs,
dbo.tblMovment.prftdep AS prft, dbo.tblMovment.stmpdep AS stmp, dbo.tblMovment.depsum ,0 AS credsum
FROM dbo.tblMovment LEFT OUTER JOIN
dbo.TBLdoctyp ON dbo.tblMovment.doccd = dbo.TBLdoctyp.doccd
WHERE (cast( [move_date] as datetime) BETWEEN cast ( '" + datfrmclc.Text.Substring(6, 4) + @"/" + datfrmclc.Text.Substring(3, 2) + @"/" + datfrmclc.Text.Substring(0, 2) + @"' as datetime) AND cast ( '" + dattocalc.Text.Substring(6, 4) + @"/" + dattocalc.Text.Substring(3, 2) + @"/" + dattocalc.Text.Substring(0, 2) + @"' as datetime) ) AND
(dbo.tblMovment.doccd = N'" + comboBox1.SelectedValue.ToString() + "') and dbo.tblMovment.depsum <>0 and docno=" + docno.Text + " order by cast( dbo.tblMovment.docno as int ) ";
}
}
else
{
tblsql = @" SELECT 'مدين' AS knd , dbo.tblMovment.typcd ,cast( dbo.tblMovment.docno as int )as docno, '" + datfrmclc.Text + "' AS datfrm, '" + dattocalc.Text + @"' AS datto, dbo.TBLdoctyp.docnm AS doctyp,
dbo.tblMovment.[internalTaxdep] as internaltax ,
dbo.tblMovment.stmpcheckdep ,
dbo.tblMovment.stmpdmnddep AS stmpdmnd,
dbo.tblMovment.earndep AS earn,
dbo.tblMovment.stmpknddep AS stmpknd,
dbo.tblMovment.stmpndusdep AS stmpndus,
dbo.tblMovment.stmpconsdep AS stmpcons,
(dbo.tblMovment.stmpsupdep )+ (dbo.tblMovment.stmpcontdep ) AS stmpsup, dbo.tblMovment.suppcomdep AS suppcom,
dbo.tblMovment.fincombdep AS fincomb, dbo.tblMovment.freejbsdep AS freejbs,
dbo.tblMovment.prftdep AS prft, dbo.tblMovment.stmpdep AS stmp, dbo.tblMovment.depsum,0 AS credsum
FROM dbo.tblMovment LEFT OUTER JOIN
dbo.TBLdoctyp ON dbo.tblMovment.doccd = dbo.TBLdoctyp.doccd
WHERE (cast( [move_date] as datetime) BETWEEN cast ( '" + datfrmclc.Text.Substring(6, 4) + @"/" + datfrmclc.Text.Substring(3, 2) + @"/" + datfrmclc.Text.Substring(0, 2) + @"' as datetime) AND cast ( '" + dattocalc.Text.Substring(6, 4) + @"/" + dattocalc.Text.Substring(3, 2) + @"/" + dattocalc.Text.Substring(0, 2) + @"' as datetime) ) AND dbo.tblMovment.depsum <>0 AND
(dbo.tblMovment.doccd = N'" + comboBox1.SelectedValue.ToString() + "') order by cast( dbo.tblMovment.docno as int ) ";
}
}
else
{
if (docno.Text != "")
{
if (docno2.Text != "")
{
tblsql = @" SELECT 'دائن' AS knd , dbo.tblMovment.typcd, dbo.tblMovment.docno, '" + datfrmclc.Text + "' AS datfrm, '" + dattocalc.Text + @"' AS datto, dbo.TBLdoctyp.docnm AS doctyp,
dbo.tblMovment.[internalTaxcred] as internaltax ,
dbo.tblMovment.stmpcheckcred ,
dbo.tblMovment.stmpdmndcred AS stmpdmnd,
dbo.tblMovment.earncred AS earn,
dbo.tblMovment.stmpkndcred AS stmpknd,
dbo.tblMovment.stmpnduscred AS stmpndus,
dbo.tblMovment.stmpconscred AS stmpcons,
( dbo.tblMovment.stmpsupcred)+ ( dbo.tblMovment.stmpcontcred) AS stmpsup, dbo.tblMovment.suppcomcred AS suppcom,
dbo.tblMovment.fincombcred AS fincomb, dbo.tblMovment.freejbscred AS freejbs,
dbo.tblMovment.prftcred AS prft, dbo.tblMovment.stmpcred AS stmp, 0 AS depsum,
dbo.tblMovment.credsum
FROM dbo.tblMovment LEFT OUTER JOIN
dbo.TBLdoctyp ON dbo.tblMovment.doccd = dbo.TBLdoctyp.doccd
WHERE (cast( [move_date] as datetime) BETWEEN cast ( '" + datfrmclc.Text.Substring(6, 4) + @"/" + datfrmclc.Text.Substring(3, 2) + @"/" + datfrmclc.Text.Substring(0, 2) + @"' as datetime) AND cast ( '" + dattocalc.Text.Substring(6, 4) + @"/" + dattocalc.Text.Substring(3, 2) + @"/" + dattocalc.Text.Substring(0, 2) + @"' as datetime) ) AND
(dbo.tblMovment.doccd = N'" + comboBox1.SelectedValue.ToString() + "') AND dbo.tblMovment.credsum <>0 and (cast (docno as int ) between " + docno.Text + " and " + docno2.Text + ") order by cast( dbo.tblMovment.docno as int ) ";
}
else
{
tblsql = @" SELECT 'دائن' AS knd , dbo.tblMovment.typcd, dbo.tblMovment.docno, '" + datfrmclc.Text + "' AS datfrm, '" + dattocalc.Text + @"' AS datto, dbo.TBLdoctyp.docnm AS doctyp,
dbo.tblMovment.[internalTaxcred] as internaltax ,
dbo.tblMovment.stmpcheckcred ,
dbo.tblMovment.stmpdmndcred AS stmpdmnd,
dbo.tblMovment.earncred AS earn,
dbo.tblMovment.stmpkndcred AS stmpknd,
dbo.tblMovment.stmpnduscred AS stmpndus,
dbo.tblMovment.stmpconscred AS stmpcons,
( dbo.tblMovment.stmpsupcred)+ ( dbo.tblMovment.stmpcontcred) AS stmpsup, dbo.tblMovment.suppcomcred AS suppcom,
dbo.tblMovment.fincombcred AS fincomb, dbo.tblMovment.freejbscred AS freejbs,
dbo.tblMovment.prftcred AS prft, dbo.tblMovment.stmpcred AS stmp, 0 AS depsum,
dbo.tblMovment.credsum
FROM dbo.tblMovment LEFT OUTER JOIN
dbo.TBLdoctyp ON dbo.tblMovment.doccd = dbo.TBLdoctyp.doccd
WHERE (cast( [move_date] as datetime) BETWEEN cast ( '" + datfrmclc.Text.Substring(6, 4) + @"/" + datfrmclc.Text.Substring(3, 2) + @"/" + datfrmclc.Text.Substring(0, 2) + @"' as datetime) AND cast ( '" + dattocalc.Text.Substring(6, 4) + @"/" + dattocalc.Text.Substring(3, 2) + @"/" + dattocalc.Text.Substring(0, 2) + @"' as datetime) ) AND
(dbo.tblMovment.doccd = N'" + comboBox1.SelectedValue.ToString() + "') AND dbo.tblMovment.credsum <>0 and docno=" + docno.Text + " order by cast( dbo.tblMovment.docno as int ) ";
}
}
else
{
tblsql = @" SELECT 'دائن' AS knd , dbo.tblMovment.typcd, dbo.tblMovment.docno, '" + datfrmclc.Text + "' AS datfrm, '" + dattocalc.Text + @"' AS datto, dbo.TBLdoctyp.docnm AS doctyp,
dbo.tblMovment.[internalTaxcred] as internaltax ,
dbo.tblMovment.stmpcheckcred ,
dbo.tblMovment.stmpdmndcred AS stmpdmnd,
dbo.tblMovment.earncred AS earn,
dbo.tblMovment.stmpkndcred AS stmpknd,
dbo.tblMovment.stmpnduscred AS stmpndus,
dbo.tblMovment.stmpconscred AS stmpcons,
(dbo.tblMovment.stmpsupcred)+ (dbo.tblMovment.stmpcontcred) AS stmpsup, dbo.tblMovment.suppcomcred AS suppcom,
dbo.tblMovment.fincombcred AS fincomb, dbo.tblMovment.freejbscred AS freejbs,
dbo.tblMovment.prftcred AS prft, dbo.tblMovment.stmpcred AS stmp, 0 AS depsum,
dbo.tblMovment.credsum
FROM dbo.tblMovment LEFT OUTER JOIN
dbo.TBLdoctyp ON dbo.tblMovment.doccd = dbo.TBLdoctyp.doccd
WHERE (cast( [move_date] as datetime) BETWEEN cast ( '" + datfrmclc.Text.Substring(6, 4) + @"/" + datfrmclc.Text.Substring(3, 2) + @"/" + datfrmclc.Text.Substring(0, 2) + @"' as datetime) AND cast ( '" + dattocalc.Text.Substring(6, 4) + @"/" + dattocalc.Text.Substring(3, 2) + @"/" + dattocalc.Text.Substring(0, 2) + @"' as datetime) ) AND dbo.tblMovment.credsum <>0 AND
(dbo.tblMovment.doccd = N'" + comboBox1.SelectedValue.ToString() + "') order by cast( dbo.tblMovment.docno as int )";
}
}
Static_class.fillTbl(tblsql);
Static_class.rptlbl1 = "";
Static_class.rptlbl2 = "";
Static_class.rptlbl3 = "";
Static_class.rptlbl4 = "";
{ Static_class.reportname = "taxclcRpt_dep"; }
//{ Static_class.reportname = "taxclcRpt"; }
Static_class.sysprint();
}
}
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();
}
