private void BindData()
{
int overallvalue = 0;
int overallstock = 0;
BusinessLogic bl = new BusinessLogic(sDataSource);
DateTime refDate = DateTime.Parse(txtStartDate.Text);
DateTime stdt = Convert.ToDateTime(txtStartDate.Text);
if (Request.QueryString["refDate"] != null)
{
stdt = Convert.ToDateTime(Request.QueryString["refDate"].ToString());
lblHeadDate.Text = stdt.ToString("dd/MM/yyyy");
cond = Request.QueryString["cond"].ToString();
cond = Server.UrlDecode(cond);
cond1 = Request.QueryString["cond1"].ToString();
cond1 = Server.UrlDecode(cond1);
//cond2 = Request.QueryString["cond2"].ToString();
//cond2 = Server.UrlDecode(cond2);
cond2 = cond.Replace("BranchCode", "S.BranchCode");
cond3 = cond.Replace("BranchCode", "P.BranchCode");
cond4 = cond.Replace("BranchCode", "SI.BranchCode");
//cond3 = Request.QueryString["cond3"].ToString();
//cond3 = Server.UrlDecode(cond3);
//cond4 = Request.QueryString["cond4"].ToString();
//cond4 = Server.UrlDecode(cond4);
cond5 = Request.QueryString["cond5"].ToString();
cond5 = cond5.ToString();
cond6 = Request.QueryString["cond6"].ToString();
cond6 = cond6.ToString();
}
refDate = Convert.ToDateTime(stdt);
DataSet ds = bl.GetProductlist(sDataSource, cond);
DataTable dt = new DataTable();
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
overallvalue = 0;
overallstock = 0;
dt.Columns.Add(new DataColumn("Brand"));
dt.Columns.Add(new DataColumn("ItemCode"));
dt.Columns.Add(new DataColumn("ProductName"));
dt.Columns.Add(new DataColumn("Model"));
dt.Columns.Add(new DataColumn("CategoryName"));
//dt.Columns.Add(new DataColumn("Rol"));
char[] commaSeparator = new char[] { ',' };
string[] result;
result = cond6.Split(commaSeparator, StringSplitOptions.None);
foreach (string str in result)
{
dt.Columns.Add(new DataColumn(str));
}
dt.Columns.Remove("Column1");
char[] commaSeparator1 = new char[] { ',' };
string[] result1;
result1 = cond5.Split(commaSeparator, StringSplitOptions.None);
foreach (string str1 in result1)
{
dt.Columns.Add(new DataColumn(str1));
dt.Columns.Add(new DataColumn(str1 + " - Value"));
}
dt.Columns.Add(new DataColumn("Overall Stock"));
dt.Columns.Add(new DataColumn("Overall Value"));
dt.Columns.Remove("Column1");
dt.Columns.Remove(" - Value");
DataRow dr_final123 = dt.NewRow();
dt.Rows.Add(dr_final123);
DataSet dst = new DataSet();
string itemcode = "";
foreach (DataRow dr in ds.Tables[0].Rows)
{
overallvalue = 0;
overallstock = 0;
itemcode = Convert.ToString(dr["itemcode"]);
dst = bl.getProducts(sDataSource, refDate, cond, cond1, cond2, cond3, cond4, itemcode);
DataRow dr_final6 = dt.NewRow();
dr_final6["Brand"] = dr["brand"];
dr_final6["ProductName"] = dr["ProductName"];
dr_final6["Model"] = dst.Tables[0].Rows[0]["Model"].ToString(); // dr["Model"];
dr_final6["ItemCode"] = dr["Itemcode"];
dr_final6["CategoryName"] = dst.Tables[0].Rows[0]["CategoryName"].ToString(); //dr["CategoryName"];
if (dst != null)
{
if (dst.Tables[0].Rows.Count > 0)
{
foreach (DataRow drt in dst.Tables[0].Rows)
{
char[] commaSeparator2 = new char[] { ',' };
string[] result2;
result2 = cond6.Split(commaSeparator, StringSplitOptions.None);
foreach (string str2 in result2)
{
string item1 = str2;
string item123 = Convert.ToString(drt["pricename"]);
if (item123 == item1)
{
dr_final6[item1] = drt["price"];
}
}
char[] commaSeparator3 = new char[] { ',' };
string[] result3;
result3 = cond5.Split(commaSeparator, StringSplitOptions.None);
foreach (string str3 in result3)
{
string item11 = str3;
string item1231 = Convert.ToString(drt["BranchCode"]);
if (item1231 == item11)
{
dr_final6[item11] = drt["Stock"];
overallstock = overallstock + Convert.ToInt32(dr_final6[item11]);
dr_final6[item1231 + " - Value"] = Convert.ToInt32(drt["price"]) * Convert.ToInt32(drt["Stock"]);
overallvalue = overallvalue + Convert.ToInt32(dr_final6[item1231 + " - Value"]);
}
}
}
}
dr_final6["Overall Stock"] = overallstock;
lblGrandStockTotal.Text = Convert.ToString(Convert.ToInt32(lblGrandStockTotal.Text) + Convert.ToInt32(dr_final6["Overall Stock"]));
dr_final6["Overall Value"] = overallvalue;
lblGrandValueTotal.Text = Convert.ToString(Convert.ToInt32(lblGrandValueTotal.Text) + Convert.ToInt32(dr_final6["Overall Value"]));
}
//dr_final6["Overall Value"] = tot;// Convert.ToInt32(dr_final6[result3 + " - Value"]) + Convert.ToInt32(dr_final6[result3 + " - Value"]);
dt.Rows.Add(dr_final6);
}
DataSet dst2 = new DataSet();
dst2.Tables.Add(dt);
ReportGridView1.DataSource = dst2;
ReportGridView1.DataBind();
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
sDataSource = ConfigurationManager.ConnectionStrings[Request.Cookies["Company"].Value].ToString();
dbfileName = sDataSource.Remove(0, sDataSource.LastIndexOf(@"App_Data\") + 9);
dbfileName = dbfileName.Remove(dbfileName.LastIndexOf(";Persist Security Info"));
if (!Page.IsPostBack)
{
txtQuery.Enabled = false;
txtmissingledsales.Enabled = false;
TextBox1.Enabled = false;
TextBox2.Enabled = false;
TextBox3.Enabled = false;
TextBox4.Enabled = false;
TextBox5.Enabled = false;
TextBox6.Enabled = false;
TextBox7.Enabled = false;
TextBox8.Enabled = false;
TextBox9.Enabled = false;
TextBox10.Enabled = false;
TextBox11.Enabled = false;
TextBox12.Enabled = false;
TextBox13.Enabled = false;
TextBox14.Enabled = false;
TextBox15.Enabled = false;
TextBox16.Enabled = false;
txtQuery.Text = "select pm.ItemCode,pm.ProductName,pm.Stock,a.qty1 as QuantityMismatch from tblproductmaster pm,(select itemcode,sum(qty) as qty1 from (select Itemcode,openingStock as qty from tblStock union all select Itemcode, qty from tblpurchaseItems union all SELECT Itemcode, qty FROM tblExecution,tblCompProduct Where tblExecution.CompID = tblCompProduct.CompID AND tblExecution.InOut = 'OUT' union all select itemcode, 0-qty from tblsalesItems union all SELECT Itemcode, 0-qty FROM tblExecution,tblCompProduct Where tblExecution.CompID = tblCompProduct.CompID AND tblExecution.InOut = 'IN' ) group by itemcode) a where pm.itemcode=a.itemcode and pm.stock <> a.qty1";
txtmissingledsales.Text = "select billno,billdate,customername,journalid,customerid from tblsales where customerid not in (select ledgerid from tblledger)";
TextBox1.Text = "select billno,billdate,journalid,supplierid,purchaseid from tblPurchase where supplierid not in (select ledgerid from tblledger)";
TextBox2.Text = "select receiptno,creditorid,paymode,journalid from tblReceipt where creditorid not in (select ledgerid from tblledger)";
TextBox3.Text = "select paymode,journalid,voucherno,billno,debtorid from tblPayment where debtorid not in (select ledgerid from tblledger)";
TextBox4.Text = "select cdtype,refno,amount,notedate,ledgerid,transno from tblCreditDebitNote where ledgerid not in (select ledgerid from tblledger)";
TextBox5.Text = "select creditorid,transno,transdate,narration,vouchertype from tbldaybook where creditorid not in (select ledgerid from tblledger)";
TextBox6.Text = "select debtorid,transno,transdate,narration,vouchertype from tbldaybook where debtorid not in (select ledgerid from tblledger)";
TextBox7.Text = "select transno,transdate,debtorid,creditorid,amount,vouchertype,refno,chequeno,narration from tbldaybook where vouchertype='Sales' and transno not in (select journalid from tblsales)";
TextBox8.Text = "select transno,transdate,debtorid,creditorid,amount,vouchertype,refno,chequeno,narration from tbldaybook where vouchertype='Purchase' and transno not in (select journalid from tblPurchase)";
TextBox9.Text = "select transno,transdate,debtorid,creditorid,amount,vouchertype,refno,chequeno,narration from tbldaybook where vouchertype='Receipt' and transno not in (select journalid from tblReceipt)";
TextBox10.Text = "select transno,transdate,debtorid,creditorid,amount,vouchertype,refno,chequeno,narration from tbldaybook where vouchertype='Payment' and transno not in (select journalid from tblPayment)";
TextBox11.Text = "select transno,transdate,debtorid,creditorid,amount,vouchertype,refno,chequeno,narration from tbldaybook where vouchertype='Note' and transno not in (select transno from tblcreditdebitnote)";
TextBox12.Text = "select billno,billdate,customername,journalid,customerid from tblsales where journalid not in (select transno from tbldaybook)";
TextBox13.Text = "select billno,billdate,journalid,supplierid,purchaseid from tblpurchase where journalid not in (select transno from tbldaybook)";
TextBox14.Text = "select receiptno,creditorid,paymode,journalid from tblreceipt where journalid not in (select transno from tbldaybook)";
TextBox15.Text = "select paymode,journalid,voucherno,billno,debtorid from tblpayment where journalid not in (select transno from tbldaybook)";
TextBox16.Text = "select cdtype,refno,amount,notedate,ledgerid,transno from tblcreditdebitnote where transno not in (select transno from tbldaybook)";
TextBox17.Text = "SELECT tblProductMaster.ItemCode, tblProductMaster.ProductName,tblProductMaster.productdesc, tblProductMaster.Model, tblProductMaster.CategoryID,tblProductMaster.Stock FROM tblProductMaster where tblProductMaster.Stock < 0 ";
BusinessLogic bl = new BusinessLogic(sDataSource);
DataSet ds = bl.GetDataForSQL(sDataSource, txtQuery.Text);
gvProducts.DataSource = ds;
gvProducts.DataBind();
DataSet dst = bl.GetDataForSQL(sDataSource, TextBox5.Text);
ReportGridView1.DataSource = dst;
ReportGridView1.DataBind();
DataSet dsttt = bl.GetDataForSQL(sDataSource, TextBox6.Text);
ReportGridView2.DataSource = dsttt;
ReportGridView2.DataBind();
DataSet dstttt = bl.GetDataForSQL(sDataSource, txtmissingledsales.Text);
ReportGridView3.DataSource = dstttt;
ReportGridView3.DataBind();
DataSet dstttttt = bl.GetDataForSQL(sDataSource, TextBox1.Text);
ReportGridView4.DataSource = dstttttt;
ReportGridView4.DataBind();
DataSet dsttttt = bl.GetDataForSQL(sDataSource, TextBox2.Text);
ReportGridView5.DataSource = dsttttt;
ReportGridView5.DataBind();
DataSet dsttttttt = bl.GetDataForSQL(sDataSource, TextBox3.Text);
ReportGridView6.DataSource = dsttttttt;
ReportGridView6.DataBind();
DataSet dstttttttdd = bl.GetDataForSQL(sDataSource, TextBox4.Text);
ReportGridView7.DataSource = dstttttttdd;
ReportGridView7.DataBind();
DataSet dstttttd = bl.GetDataForSQL(sDataSource, TextBox7.Text);
ReportGridView8.DataSource = dstttttd;
ReportGridView8.DataBind();
DataSet dstttttddd = bl.GetDataForSQL(sDataSource, TextBox8.Text);
ReportGridView9.DataSource = dstttttddd;
ReportGridView9.DataBind();
DataSet dstttttdt = bl.GetDataForSQL(sDataSource, TextBox9.Text);
ReportGridView10.DataSource = dstttttdt;
ReportGridView10.DataBind();
DataSet dstttttddt = bl.GetDataForSQL(sDataSource, TextBox10.Text);
ReportGridView11.DataSource = dstttttddt;
ReportGridView11.DataBind();
DataSet dstttttddtd = bl.GetDataForSQL(sDataSource, TextBox11.Text);
ReportGridView12.DataSource = dstttttddtd;
ReportGridView12.DataBind();
DataSet dstttttddtdd = bl.GetDataForSQL(sDataSource, TextBox12.Text);
ReportGridView13.DataSource = dstttttddtdd;
ReportGridView13.DataBind();
DataSet dstttttddtddd = bl.GetDataForSQL(sDataSource, TextBox13.Text);
ReportGridView14.DataSource = dstttttddtddd;
ReportGridView14.DataBind();
DataSet dstttttddtdddd = bl.GetDataForSQL(sDataSource, TextBox14.Text);
ReportGridView15.DataSource = dstttttddtdddd;
ReportGridView15.DataBind();
DataSet dstttttddtddddd = bl.GetDataForSQL(sDataSource, TextBox15.Text);
ReportGridView16.DataSource = dstttttddtddddd;
ReportGridView16.DataBind();
DataSet dstttttddtddddtd = bl.GetDataForSQL(sDataSource, TextBox16.Text);
ReportGridView17.DataSource = dstttttddtddddtd;
ReportGridView17.DataBind();
DataSet ddd = bl.GetDataForSQL(sDataSource, TextBox17.Text);
ReportGridView18.DataSource = ddd;
ReportGridView18.DataBind();
ExportToExcel();
}
}
catch (Exception ex)
{
TroyLiteExceptionManager.HandleException(ex);
}
}
private void BindData()
{
int overallvalue = 0;
int overallstock = 0;
BusinessLogic bl = new BusinessLogic(sDataSource);
DateTime refDate = DateTime.Parse(txtStartDate.Text);
DateTime stdt = Convert.ToDateTime(txtStartDate.Text);
if (Request.QueryString["refDate"] != null)
{
stdt = Convert.ToDateTime(Request.QueryString["refDate"].ToString());
lblHeadDate.Text = stdt.ToString("dd/MM/yyyy");
cond = Request.QueryString["cond"].ToString();
cond = Server.UrlDecode(cond);
cond1 = Request.QueryString["cond1"].ToString();
cond1 = Server.UrlDecode(cond1);
cond2 = Request.QueryString["cond2"].ToString();
cond2 = Server.UrlDecode(cond2);
cond3 = Request.QueryString["cond3"].ToString();
cond3 = Server.UrlDecode(cond3);
cond4 = Request.QueryString["cond4"].ToString();
cond4 = Server.UrlDecode(cond4);
cond5 = Request.QueryString["cond5"].ToString();
cond5 = cond5.ToString();
cond6 = Request.QueryString["cond6"].ToString();
cond6 = cond6.ToString();
}
refDate = Convert.ToDateTime(stdt);
lblbranch.Text = cond;
DataSet ds = bl.GetProductlist1(sDataSource, cond);
DataTable dt = new DataTable();
double openingStock = 0;
if (ds != null)
// Model,tblProductMaster.CategoryID,tblCategories.CategoryName
{
if (ds.Tables[0].Rows.Count > 0)
{
overallvalue = 0;
overallstock = 0;
dt.Columns.Add(new DataColumn("ItemCode"));
// dt.Columns.Add(new DataColumn("Model"));
// dt.Columns.Add(new DataColumn("CategoryName"));
dt.Columns.Add(new DataColumn("openingStock"));
dt.Columns.Add(new DataColumn("purchaseStock"));
dt.Columns.Add(new DataColumn("salestock"));
dt.Columns.Add(new DataColumn("Currentcalculatedstock"));
dt.Columns.Add(new DataColumn("CurrentPhysicalstock"));
DataSet dst = new DataSet();
string itemcode = "";
double opstock;
double purstock;
double salstock;
double ovstock;
// double openingStock1;
string product = "";
foreach (DataRow dr in ds.Tables[0].Rows)
{
// overallvalue = 0;getoverallStockforallreport
// overallstock = 0;
itemcode = Convert.ToString(dr["itemcode"]);
product = Convert.ToString(dr["itemcode"]) + " - " + Convert.ToString(dr["Model"]) + " - " + Convert.ToString(dr["CategoryName"]);
// model = Convert.ToString(dr["itemcode"]);
// category = Convert.ToString(dr["itemcode"]);
opstock = Convert.ToDouble(bl.getOpeningStockforallreport(sDataSource, itemcode, cond));
purstock = Convert.ToDouble(bl.getOpeningStockPurchaseforallreport(sDataSource, itemcode, stdt, cond3));
salstock = Convert.ToDouble(bl.getOpeningStockSalesforallreport(sDataSource, itemcode, stdt, cond2));
ovstock = Convert.ToDouble(bl.getoverallStockforallreport(sDataSource, itemcode, cond));
openingStock = opstock + purstock - salstock;
// openingStock = Convert.ToDouble(bl.getOpeningStockforallreport(sDataSource, itemcode, cond)) +
// (Convert.ToDouble(bl.getOpeningStockPurchaseforallreport(sDataSource, itemcode, stdt, cond3)) - Convert.ToDouble(bl.getOpeningStockSalesforallreport(sDataSource, itemcode, stdt, cond4)));
DataRow dr_final6 = dt.NewRow();
dr_final6["ItemCode"] = product;
dr_final6["openingStock"] = opstock;
dr_final6["purchaseStock"] = purstock;
dr_final6["salestock"] = salstock;
dr_final6["Currentcalculatedstock"] = openingStock;
dr_final6["CurrentPhysicalstock"] = ovstock;
dt.Rows.Add(dr_final6);
}
DataSet dst2 = new DataSet();
dst2.Tables.Add(dt);
ReportGridView1.DataSource = dst2;
int cc = dst2.Tables[0].Rows.Count;
//ScriptManager.RegisterStartupScript(Page, Page.GetType(), Guid.NewGuid().ToString(), "alert(' Please Select Branch. It cannot be Left blank.');", true);
ReportGridView1.DataBind();
}
}
}