public void bindDataPurReturn()
{
// DateTime startDate, endDate;
DataSet ds = new DataSet();
DataSet dstt = new DataSet();
double tot = 0;
double tot1 = 0;
string intTrans = "";
string salesRet = "";
string delNote = "";
string condi = "";
intTrans = "NO";
salesRet = "YES";
delNote = "NO";
string Branch = Convert.ToString(Request.QueryString["Branch"].ToString());
DateTime startDate = Convert.ToDateTime(Request.QueryString["startdate"].ToString());
DateTime endDate = Convert.ToDateTime(Request.QueryString["enddate"].ToString());
condi = Request.QueryString["condi"].ToString();
condi = Server.UrlDecode(condi);
if (condi == "5%")
{
condi = " And tblSalesitems.vat = 5";
}
else if (condi == "14.5%")
{
condi = " And tblSalesitems.vat = 14.5";
}
if (condi == "All")
{
condi = "";
}
// startDate = Convert.ToDateTime(txtStrtDt.Text.Trim());
// endDate = Convert.ToDateTime(txtEdDt.Text.Trim());
// string Branch = DropDownList2.SelectedValue;
objBL = new BusinessLogic(ConfigurationManager.ConnectionStrings[Request.Cookies["Company"].Value].ToString());
ds = objBL.SalesPurRetAnnuxere(startDate, endDate, salesRet, intTrans, delNote, condi, Branch);
Double serialno = 1;
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("SNo"));
dt.Columns.Add(new DataColumn("Name of the buyer"));
dt.Columns.Add(new DataColumn("Buyer Tin"));
dt.Columns.Add(new DataColumn("Commodity Code"));
dt.Columns.Add(new DataColumn("Invoice No"));
dt.Columns.Add(new DataColumn("Invoice Date"));
dt.Columns.Add(new DataColumn("Sales Value"));
dt.Columns.Add(new DataColumn("Tax Rate"));
dt.Columns.Add(new DataColumn("Vat CST Paid"));
dt.Columns.Add(new DataColumn("Category"));
dt.Columns.Add(new DataColumn("Branchcode"));
DataRow dr_export1 = dt.NewRow();
dt.Rows.Add(dr_export1);
foreach (DataRow dr in ds.Tables[0].Rows)
{
DataRow dr_export = dt.NewRow();
dr_export["SNo"] = serialno;
dr_export["Name of the buyer"] = dr["LinkName"];
dr_export["Buyer Tin"] = dr["Tinnumber"];
dr_export["Commodity Code"] = "";
dr_export["Invoice No"] = dr["BillNo"];
string aa = dr["BillDate"].ToString().ToUpper().Trim();
string dtaa = Convert.ToDateTime(aa).ToString("dd/MM/yyyy");
dr_export["Invoice Date"] = dtaa;
tot = tot + Convert.ToDouble(dr["NetRate"]);
tot1 = tot1 + Convert.ToDouble(dr["ActualVAT"]);
dr_export["Branchcode"] = dr["Branchcode"];
dr_export["Sales Value"] = dr["NetRate"];
dr_export["Tax Rate"] = dr["vat"];
dr_export["Vat CST Paid"] = dr["ActualVAT"];
dr_export["Category"] = "";
dt.Rows.Add(dr_export);
serialno = serialno + 1;
}
DataRow dr_export2 = dt.NewRow();
dr_export2["SNo"] = "";
dr_export2["Name of the buyer"] = "";
dr_export2["Buyer Tin"] = "";
dr_export2["Commodity Code"] = "";
dr_export2["Invoice No"] = "";
dr_export2["Invoice Date"] = "Total =";
dr_export2["Sales Value"] = tot;
dr_export2["Tax Rate"] = "Total = ";
dr_export2["Vat CST Paid"] = tot1;
dr_export2["Category"] = "";
dt.Rows.Add(dr_export2);
// ExportToExcel(dt);
dstt.Tables.Add(dt);
}
}
else
{
ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
}
ReportsBL.ReportClass rptStock = new ReportsBL.ReportClass();
// DataSet ds = new DataSet(sDataSource);
// DataSet ds = rptStock.getCategory(sDataSource);
Grdreport.Visible = true;
Grdreport.DataSource = dstt;
Grdreport.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
sDataSource = ConfigurationManager.ConnectionStrings[Request.Cookies["Company"].Value].ToString();
Connection = Request.Cookies["Company"].Value;
if (!IsPostBack)
{
lblHeading.Text = " Purchase Comprehency Report";
DataSet companyInfo = new DataSet();
BusinessLogic bl = new BusinessLogic(sDataSource);
if (Request.Cookies["Company"] != null)
{
companyInfo = bl.getCompanyInfo(Request.Cookies["Company"].Value);
if (companyInfo != null)
{
if (companyInfo.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in companyInfo.Tables[0].Rows)
{
lblTNGST.Text = Convert.ToString(dr["TINno"]);
lblCompany.Text = Convert.ToString(dr["CompanyName"]);
lblPhone.Text = Convert.ToString(dr["Phone"]);
lblGSTno.Text = Convert.ToString(dr["GSTno"]);
lblAddress.Text = Convert.ToString(dr["Address"]);
lblCity.Text = Convert.ToString(dr["city"]);
lblPincode.Text = Convert.ToString(dr["Pincode"]);
lblState.Text = Convert.ToString(dr["state"]);
}
}
}
DataSet ds1 = bl.getImageInfo();
if (ds1 != null)
{
if (ds1.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
{
Image1.ImageUrl = "App_Themes/NewTheme/images/" + ds1.Tables[0].Rows[i]["img_filename"];
Image1.Height = 95;
Image1.Width = 114;
}
}
else
{
Image1.Height = 95;
Image1.Width = 114;
Image1.ImageUrl = "App_Themes/NewTheme/images/TESTLogo.png";
}
}
}
}
lblBillDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
//txtStartDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
DateTime indianStd = TimeZoneInfo.ConvertTimeBySystemTimeZoneId(DateTime.Now, "India Standard Time");
string dtaa = Convert.ToDateTime(indianStd).ToString("dd/MM/yyyy");
// txtStartDate.Text = dtaa;
// lblHeadDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
//string sDataSource = Server.MapPath("App_Data\\Store0910.mdb");
//string sDataSource = Server.MapPath(ConfigurationSettings.AppSettings["DataSource"].ToString());
divPrint.Visible = true;
divPr.Visible = true;
DataSet dstt = new DataSet();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
// DateTime startDate, endDate;
double brandTotal = 0;
double catTotal = 0;
double total = 0;
double producttot = 0;
string fLvlValueTemp = string.Empty;
string fLvlValue = string.Empty;
string tLvlValueTemp = string.Empty;
string tLvlValue = string.Empty;
string sLvlValueTemp = string.Empty;
string sLvlValue = string.Empty;
string connection = Request.Cookies["Company"].Value;
string brand = Convert.ToString(Request.QueryString["brand"].ToString());
string Category = Convert.ToString(Request.QueryString["category"].ToString());
string product = Convert.ToString(Request.QueryString["product"].ToString());
string Branch = Convert.ToString(Request.QueryString["Branch"].ToString());
DateTime startDate = Convert.ToDateTime(Request.QueryString["startDate"].ToString());
DateTime endDate = Convert.ToDateTime(Request.QueryString["enddate"].ToString());
objBL = new BusinessLogic(ConfigurationManager.ConnectionStrings[Request.Cookies["Company"].Value].ToString());
dt.Columns.Add(new DataColumn("Category"));
dt.Columns.Add(new DataColumn("Brand"));
dt.Columns.Add(new DataColumn("Product Name"));
dt.Columns.Add(new DataColumn("Model"));
dt.Columns.Add(new DataColumn("Branchcode"));
dt.Columns.Add(new DataColumn("Qty"));
dt.Columns.Add(new DataColumn("Rate"));
dt.Columns.Add(new DataColumn("Amount"));
ds = objBL.getPurchasereport(startDate, endDate, Category, brand, product, Branch);
if (ds.Tables[0].Rows.Count > 0)
{
DataRow dr_final11 = dt.NewRow();
dt.Rows.Add(dr_final11);
foreach (DataRow dr in ds.Tables[0].Rows)
{
fLvlValueTemp = dr["CategoryName"].ToString().ToUpper().Trim();
sLvlValueTemp = dr["Brand"].ToString().ToUpper().Trim();
tLvlValueTemp = dr["ProductName"].ToString().ToUpper().Trim();
if ((fLvlValue != "" && fLvlValue != fLvlValueTemp) ||
(sLvlValue != "" && sLvlValue != sLvlValueTemp) ||
(tLvlValue != "" && tLvlValue != tLvlValueTemp))
{
DataRow dr_final889 = dt.NewRow();
dt.Rows.Add(dr_final889);
DataRow dr_final8 = dt.NewRow();
dr_final8["Category"] = "";
dr_final8["Brand"] = "";
dr_final8["Product Name"] = "Total : " + tLvlValue;
dr_final8["Model"] = "";
dr_final8["Qty"] = "";
dr_final8["Rate"] = "";
dr_final8["Amount"] = Convert.ToString(Convert.ToDecimal(producttot));
producttot = 0;
dt.Rows.Add(dr_final8);
DataRow dr_final888 = dt.NewRow();
dt.Rows.Add(dr_final888);
}
if ((fLvlValue != "" && fLvlValue != fLvlValueTemp) ||
(sLvlValue != "" && sLvlValue != sLvlValueTemp))
{
DataRow dr_final8 = dt.NewRow();
dr_final8["Category"] = "";
dr_final8["Brand"] = "Total : " + sLvlValue;
dr_final8["Product Name"] = "";
dr_final8["Model"] = "";
dr_final8["Qty"] = "";
dr_final8["Rate"] = "";
dr_final8["Amount"] = Convert.ToString(Convert.ToDecimal(brandTotal));
brandTotal = 0;
dt.Rows.Add(dr_final8);
DataRow dr_final888 = dt.NewRow();
dt.Rows.Add(dr_final888);
}
if (fLvlValue != "" && fLvlValue != fLvlValueTemp)
{
//DataRow dr_final889 = dt.NewRow();
//dt.Rows.Add(dr_final889);
DataRow dr_final8 = dt.NewRow();
dr_final8["Category"] = "Total : " + fLvlValue;
dr_final8["Brand"] = "";
dr_final8["Product Name"] = "";
dr_final8["Model"] = "";
dr_final8["Qty"] = "";
dr_final8["Rate"] = "";
dr_final8["Amount"] = Convert.ToString(Convert.ToDecimal(catTotal));
catTotal = 0;
dt.Rows.Add(dr_final8);
DataRow dr_final888 = dt.NewRow();
dt.Rows.Add(dr_final888);
}
fLvlValue = fLvlValueTemp;
sLvlValue = sLvlValueTemp;
tLvlValue = tLvlValueTemp;
DataRow dr_final12 = dt.NewRow();
dr_final12["Category"] = dr["Categoryname"];
dr_final12["Brand"] = dr["Brand"];
dr_final12["Branchcode"] = dr["Branchcode"];
dr_final12["Product Name"] = dr["ProductName"];
dr_final12["Model"] = dr["model"];
dr_final12["Qty"] = dr["Qty"];
dr_final12["Rate"] = dr["purchaseRate"];
dr_final12["Amount"] = Convert.ToDouble(dr["Amount"]);
brandTotal = brandTotal + (Convert.ToDouble(dr["Amount"]));
catTotal = catTotal + (Convert.ToDouble(dr["Amount"]));
producttot = producttot + (Convert.ToDouble(dr["Amount"]));
total = total + (Convert.ToDouble(dr["Amount"]));
dt.Rows.Add(dr_final12);
}
}
DataRow dr_final879 = dt.NewRow();
dt.Rows.Add(dr_final879);
DataRow dr_final88 = dt.NewRow();
dr_final88["Category"] = "";
dr_final88["Brand"] = "";
dr_final88["Product Name"] = "Total : " + tLvlValueTemp;
dr_final88["Model"] = "";
dr_final88["Qty"] = "";
dr_final88["Rate"] = "";
dr_final88["Amount"] = Convert.ToString(Convert.ToDecimal(producttot));
dt.Rows.Add(dr_final88);
DataRow dr_final79 = dt.NewRow();
dt.Rows.Add(dr_final79);
DataRow dr_final89 = dt.NewRow();
dr_final88["Category"] = "";
dr_final89["Brand"] = "Total : " + sLvlValueTemp;
dr_final89["Product Name"] = "";
dr_final89["Model"] = "";
dr_final89["Qty"] = "";
dr_final89["Rate"] = "";
dr_final89["Amount"] = Convert.ToString(Convert.ToDecimal(brandTotal));
dt.Rows.Add(dr_final89);
DataRow dr_final8879 = dt.NewRow();
dt.Rows.Add(dr_final8879);
DataRow dr_final869 = dt.NewRow();
dr_final869["Category"] = "Total : " + fLvlValueTemp;
dr_final869["Brand"] = "";
dr_final869["Product Name"] = "";
dr_final869["Model"] = "";
dr_final869["Qty"] = "";
dr_final869["Rate"] = "";
dr_final869["Amount"] = Convert.ToString(Convert.ToDecimal(catTotal));
dt.Rows.Add(dr_final869);
DataRow dr_final9 = dt.NewRow();
dt.Rows.Add(dr_final9);
DataRow dr_final789 = dt.NewRow();
dr_final88["Category"] = "";
dr_final789["Brand"] = "Grand Total : ";
dr_final789["Product Name"] = "";
dr_final789["Model"] = "";
dr_final789["Qty"] = "";
dr_final789["Rate"] = "";
dr_final789["Amount"] = Convert.ToString(Convert.ToDecimal(total));
dt.Rows.Add(dr_final789);
if (ds.Tables[0].Rows.Count > 0)
{
// ExportToExcel(dt);
dstt.Tables.Add(dt);
ReportsBL.ReportClass rptStock = new ReportsBL.ReportClass();
// DataSet ds = new DataSet(sDataSource);
// DataSet ds = rptStock.getCategory(sDataSource);
Grdreport.Visible = true;
Grdreport.DataSource = dstt;
Grdreport.DataBind();
}
else
{
ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
}
}
catch (Exception ex)
{
TroyLiteExceptionManager.HandleException(ex);
}
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
sDataSource = ConfigurationManager.ConnectionStrings[Request.Cookies["Company"].Value].ToString();
Connection = Request.Cookies["Company"].Value;
if (!IsPostBack)
{
lblHeading.Text = "Zero / 1 Rupee Value Sales Report";
DataSet companyInfo = new DataSet();
BusinessLogic bl = new BusinessLogic(sDataSource);
if (Request.Cookies["Company"] != null)
{
companyInfo = bl.getCompanyInfo(Request.Cookies["Company"].Value);
if (companyInfo != null)
{
if (companyInfo.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in companyInfo.Tables[0].Rows)
{
lblTNGST.Text = Convert.ToString(dr["TINno"]);
lblCompany.Text = Convert.ToString(dr["CompanyName"]);
lblPhone.Text = Convert.ToString(dr["Phone"]);
lblGSTno.Text = Convert.ToString(dr["GSTno"]);
lblAddress.Text = Convert.ToString(dr["Address"]);
lblCity.Text = Convert.ToString(dr["city"]);
lblPincode.Text = Convert.ToString(dr["Pincode"]);
lblState.Text = Convert.ToString(dr["state"]);
}
}
}
DataSet ds1 = bl.getImageInfo();
if (ds1 != null)
{
if (ds1.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
{
Image1.ImageUrl = "App_Themes/NewTheme/images/" + ds1.Tables[0].Rows[i]["img_filename"];
Image1.Height = 95;
Image1.Width = 95;
}
}
else
{
Image1.Height = 95;
Image1.Width = 95;
Image1.ImageUrl = "App_Themes/NewTheme/images/TESTLogo.png";
}
}
}
}
lblBillDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
//txtStartDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
DateTime indianStd = TimeZoneInfo.ConvertTimeBySystemTimeZoneId(DateTime.Now, "India Standard Time");
string dtaa = Convert.ToDateTime(indianStd).ToString("dd/MM/yyyy");
// txtStartDate.Text = dtaa;
// lblHeadDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
//string sDataSource = Server.MapPath("App_Data\\Store0910.mdb");
//string sDataSource = Server.MapPath(ConfigurationSettings.AppSettings["DataSource"].ToString());
divPrint.Visible = true;
divPr.Visible = true;
double tot = 0;
DataSet dstt = new DataSet();
DataSet ds = new DataSet();
DataSet dsttt = new DataSet();
string connection = Request.Cookies["Company"].Value;
string Method = Convert.ToString(Request.QueryString["Method"].ToString());
DateTime startdate = Convert.ToDateTime(Request.QueryString["startdate"].ToString());
DateTime enddate = Convert.ToDateTime(Request.QueryString["enddate"].ToString());
string branch = Convert.ToString(Request.QueryString["Branch"].ToString());
int value = Convert.ToInt32(Request.QueryString["Value"].ToString());
ds = objBL.getzerosalesreport(connection,sDataSource, startdate, enddate, Method, value, branch);
DataTable dt = new DataTable("Sales");
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
dt.Columns.Add(new DataColumn("Brand"));
dt.Columns.Add(new DataColumn("ProductName"));
dt.Columns.Add(new DataColumn("Model"));
dt.Columns.Add(new DataColumn("ItemCode"));
dt.Columns.Add(new DataColumn("BillNo"));
dt.Columns.Add(new DataColumn("BillDate"));
dt.Columns.Add(new DataColumn("LedgerName"));
dt.Columns.Add(new DataColumn("LedgerDetails"));
dt.Columns.Add(new DataColumn("Internal Transfer"));
dt.Columns.Add(new DataColumn("Delivery Note"));
dt.Columns.Add(new DataColumn("Purchase Return"));
dt.Columns.Add(new DataColumn("Qty"));
// dt.Columns.Add(new DataColumn("Total"));
DataRow dr_export1 = dt.NewRow();
dt.Rows.Add(dr_export1);
foreach (DataRow dr in ds.Tables[0].Rows)
{
DataRow dr_final6 = dt.NewRow();
dr_final6["Brand"] = dr["brand"];
dr_final6["ProductName"] = dr["ProductName"];
dr_final6["Model"] = dr["Model"];
dr_final6["ItemCode"] = dr["Itemcode"];
dr_final6["BillNo"] = dr["BillNo"];
dr_final6["BillDate"] = dr["BillDate"];
dr_final6["LedgerName"] = dr["LedgerName"];
dr_final6["LedgerDetails"] = dr["customeraddress"];
dr_final6["Internal Transfer"] = dr["InternalTransfer"];
dr_final6["Delivery Note"] = dr["DeliveryNote"];
dr_final6["Purchase Return"] = dr["PurchaseReturn"];
dr_final6["Qty"] = dr["Qty"];
// dr_final6["Total"] = dr["Qty"];
tot = tot + Convert.ToDouble(dr["Qty"]);
dt.Rows.Add(dr_final6);
}
DataRow dr_export2 = dt.NewRow();
dr_export2["Brand"] = "Total";
dr_export2["ProductName"] = "";
dr_export2["Model"] = "";
dr_export2["ItemCode"] = "";
dr_export2["BillNo"] = "";
dr_export2["BillDate"] = "";
dr_export2["LedgerName"] = "";
dr_export2["LedgerDetails"] = "";
dr_export2["Internal Transfer"] = "";
dr_export2["Delivery Note"] = "";
dr_export2["Purchase Return"] = "";
dr_export2["Qty"] = tot;
dt.Rows.Add(dr_export2);
// ExportToExcel(dt);
dstt.Tables.Add(dt);
Grdreport.Visible = true;
Grdreport.DataSource = dt;
Grdreport.DataBind();
}
else
{
ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
}
}
else
{
ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
}
}
catch (Exception ex)
{
TroyLiteExceptionManager.HandleException(ex);
}
}
//// private string sDataSource = string.Empty;
// private string Connection = string.Empty;
protected void Page_Load(object sender, EventArgs e)
{
try
{
sDataSource = ConfigurationManager.ConnectionStrings[Request.Cookies["Company"].Value].ToString();
Connection = Request.Cookies["Company"].Value;
if (!IsPostBack)
{
DataSet companyInfo = new DataSet();
BusinessLogic bl = new BusinessLogic(sDataSource);
if (Request.Cookies["Company"] != null)
{
companyInfo = bl.getCompanyInfo(Request.Cookies["Company"].Value);
if (companyInfo != null)
{
if (companyInfo.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in companyInfo.Tables[0].Rows)
{
lblTNGST.Text = Convert.ToString(dr["TINno"]);
lblCompany.Text = Convert.ToString(dr["CompanyName"]);
lblPhone.Text = Convert.ToString(dr["Phone"]);
lblGSTno.Text = Convert.ToString(dr["GSTno"]);
lblAddress.Text = Convert.ToString(dr["Address"]);
lblCity.Text = Convert.ToString(dr["city"]);
lblPincode.Text = Convert.ToString(dr["Pincode"]);
lblState.Text = Convert.ToString(dr["state"]);
}
}
}
DataSet ds1 = bl.getImageInfo();
if (ds1 != null)
{
if (ds1.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
{
Image1.ImageUrl = "App_Themes/NewTheme/images/" + ds1.Tables[0].Rows[i]["img_filename"];
Image1.Height = 95;
Image1.Width = 114;
}
}
else
{
Image1.Height = 95;
Image1.Width = 114;
Image1.ImageUrl = "App_Themes/NewTheme/images/TESTLogo.png";
}
}
}
}
lblBillDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
//txtStartDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
DateTime indianStd = TimeZoneInfo.ConvertTimeBySystemTimeZoneId(DateTime.Now, "India Standard Time");
string dtaa = Convert.ToDateTime(indianStd).ToString("dd/MM/yyyy");
// txtStartDate.Text = dtaa;
// lblHeadDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
//string sDataSource = Server.MapPath("App_Data\\Store0910.mdb");
//string sDataSource = Server.MapPath(ConfigurationSettings.AppSettings["DataSource"].ToString());
divPrint.Visible = true;
divPr.Visible = true;
DataSet dstt = new DataSet();
string connection = Request.Cookies["Company"].Value;
string branch = Convert.ToString(Request.QueryString["Branch"].ToString());
string method = Convert.ToString(Request.QueryString["Method"].ToString());
if (method == "All")
{
lblHeading.Text = "All ItemList";
}
else if (method == "Absolute")
{
lblHeading.Text = "Obsolete ItemList";
}
else
{
lblHeading.Text = "Other Than Obsolete";
}
cond1 = Request.QueryString["cond1"].ToString();
cond1 = Server.UrlDecode(cond1);
string field = string.Empty;
// DataSet ds = objBL.GetAbsoluteProductlist(sDataSource, field, method, branch);
// bool IsPro= = Convert.ToBoolean(Request.QueryString["Method"].ToString());
DataSet ds = new DataSet();
ds = objBL.GetAbsoluteProductlist(sDataSource, connection, field, method, branch);
DataTable dt = new DataTable();
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
dt.Columns.Add(new DataColumn("Brand"));
dt.Columns.Add(new DataColumn("ProductName"));
dt.Columns.Add(new DataColumn("ItemCode"));
dt.Columns.Add(new DataColumn("Model"));
dt.Columns.Add(new DataColumn("Rol"));
dt.Columns.Add(new DataColumn("Stock"));
dt.Columns.Add(new DataColumn("Branchcode"));
char[] commaSeparator = new char[] { ',' };
string[] result;
result = cond1.Split(commaSeparator, StringSplitOptions.None);
foreach (string str in result)
{
dt.Columns.Add(new DataColumn(str));
}
dt.Columns.Remove("Column1");
DataRow dr_final123 = dt.NewRow();
dt.Rows.Add(dr_final123);
DataSet dst = new DataSet();
string itemcode = "";
foreach (DataRow dr in ds.Tables[0].Rows)
{
itemcode = Convert.ToString(dr["itemcode"]);
dst = objBL.GetAbsoluteProductpricelist(sDataSource, connection, itemcode, branch);
DataRow dr_final6 = dt.NewRow();
dr_final6["Brand"] = dr["brand"];
dr_final6["ProductName"] = dr["ProductName"];
dr_final6["Model"] = dr["Model"];
dr_final6["ItemCode"] = dr["Itemcode"];
dr_final6["Rol"] = dr["Rol"];
if (dst != null)
{
if (dst.Tables[0].Rows.Count > 0)
{
foreach (DataRow drt in dst.Tables[0].Rows)
{
dr_final6["Stock"] = drt["Stock"];
dr_final6["Branchcode"] = drt["Branchcode"];
char[] commaSeparator2 = new char[] { ',' };
string[] result2;
result2 = cond1.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"];
}
}
}
}
}
dt.Rows.Add(dr_final6);
}
dstt.Tables.Add(dt);
}
else
{
ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
}
}
else
{
ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
}
ReportsBL.ReportClass rptStock = new ReportsBL.ReportClass();
// DataSet ds = new DataSet(sDataSource);
// DataSet ds = rptStock.getCategory(sDataSource);
Grdreport.Visible = true;
Grdreport.DataSource = dstt;
Grdreport.DataBind();
// div1.Visible = false;
}
catch (Exception ex)
{
TroyLiteExceptionManager.HandleException(ex);
}
}
public void bindDatabrand(string sDataSource)
{
string trange = string.Empty;
string toption = string.Empty;
DataSet ds = new DataSet();
string Branch = Convert.ToString(Request.QueryString["Branch"].ToString());
int ttrange = Convert.ToInt32(Request.QueryString["range"].ToString());
int ttoption = Convert.ToInt32(Request.QueryString["option"].ToString());
DateTime refDate = Convert.ToDateTime(Request.QueryString["refdate"].ToString());
int tstock = 0;
int trol = 0;
// string Branch = drpBranchAdd.SelectedValue;
ds = objBL.getstocklevelbrand(sDataSource, refDate, trange, toption, Branch);
DataSet dst = new DataSet();
string itemcode = string.Empty;
DataTable dt = new DataTable();
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
//dt.Columns.Add(new DataColumn("Category"));
dt.Columns.Add(new DataColumn("Brand"));
//dt.Columns.Add(new DataColumn("ProductName"));
//dt.Columns.Add(new DataColumn("Model"));
//dt.Columns.Add(new DataColumn("ItemCode"));
dt.Columns.Add(new DataColumn("Brand %"));
dt.Columns.Add(new DataColumn("Stock"));
dt.Columns.Add(new DataColumn("Branchcode"));
// dt.Columns.Add(new DataColumn("Stock Value"));
//foreach (ListItem listItem1 in lstPricelist.Items)
//{
// if (listItem1.Selected)
// {
// string item1 = listItem1.Value + " Value %";
// dt.Columns.Add(new DataColumn(item1));
// }
//}
cond1 = Request.QueryString["cond1"].ToString();
cond1 = Server.UrlDecode(cond1);
char[] commaSeparator = new char[] { ',' };
string[] result;
result = cond1.Split(commaSeparator, StringSplitOptions.None);
foreach (string str in result)
{
string strr = str + " Value %";
dt.Columns.Add(new DataColumn(strr));
}
dt.Columns.Remove(" Value %");
DataRow dr_final111 = dt.NewRow();
dt.Rows.Add(dr_final111);
//if (ds.Tables[0].Rows.Count > 0)
//{
// for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
// {
// tstock = Convert.ToInt32(ds.Tables[0].Rows[i]["Stock"]);
// if (tstock == 0)
// {
// ds.Tables[0].Rows[i].Delete();
// }
// }
// ds.Tables[0].AcceptChanges();
//}
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
brand = Convert.ToString(dr["BrandName"]);
//dst = objBL.GetAbsoluteProductpricelist(sDataSource, itemcode, Branch);
DataRow dr_final6 = dt.NewRow();
//dr_final6["Category"] = dr["Categoryname"];
dr_final6["Brand"] = dr["brandname"];
//dr_final6["ProductName"] = dr["ProductName"];
//dr_final6["Model"] = dr["Model"];
dr_final6["BranchCode"] = Branch;
dr_final6["Brand %"] = dr["brandlevel"];
dr_final6["Stock"] = dr["Stock"];
//if (dst != null)
//{
// if (dst.Tables[0].Rows.Count > 0)
// {
// foreach (DataRow drt in dst.Tables[0].Rows)
// {
// dr_final6["Branchcode"] = drt["Branchcode"];
// foreach (ListItem listItem1 in lstPricelist.Items)
// {
// if (listItem1.Selected)
// {
// string item1 = listItem1.Value;
// string item123 = Convert.ToString(drt["pricename"]);
// if (item123 == item1)
// {
// dr_final6[item1] = drt["price"];
// }
// }
// }
// }
// }
//}
string connection = Request.Cookies["Company"].Value;
char[] commaSeparator2 = new char[] { ',' };
string[] result2;
result2 = cond1.Split(commaSeparator, StringSplitOptions.None);
foreach (string str2 in result2)
{
//if (str2)
//{
dst = objBL.GetAbsoluteCategorypricelist1(connection, sDataSource, brand, Branch, "brand", str2);
if (dst != null)
{
if (dst.Tables[0].Rows.Count > 0)
{
foreach (DataRow drt in dst.Tables[0].Rows)
{
string item1 = str2 + " Value %";
//string item123 = Convert.ToString(drt["pricename"]);
//if (item123 == item1)
//{
dr_final6[item1] = Convert.ToDouble(drt["price"]) / 100;
//}
}
}
//}
}
}
dt.Rows.Add(dr_final6);
}
DataSet dstt = new DataSet();
dstt.Tables.Add(dt);
double troll;
if (dstt.Tables[0].Rows.Count > 0)
{
if (ttrange == 1)
{
for (int i = 0; i < dstt.Tables[0].Rows.Count; i++)
{
tstock = Convert.ToInt32(dstt.Tables[0].Rows[i]["Stock"]);
trol = Convert.ToInt32(dstt.Tables[0].Rows[i]["brand %"]);
troll = Convert.ToDouble(dstt.Tables[0].Rows[i]["price"]) / 100;
if (tstock > trol)
{
dstt.Tables[0].Rows[i].Delete();
}
}
dstt.Tables[0].AcceptChanges();
}
else if (ttrange == 2)
{
for (int i = 0; i < dstt.Tables[0].Rows.Count; i++)
{
tstock = Convert.ToInt32(dstt.Tables[0].Rows[i]["Stock"]);
trol = Convert.ToInt32(dstt.Tables[0].Rows[i]["brand %"]);
troll = Convert.ToDouble(dstt.Tables[0].Rows[i]["price"]) / 100;
if (tstock < trol)
{
dstt.Tables[0].Rows[i].Delete();
}
}
dstt.Tables[0].AcceptChanges();
}
else if (ttrange == 3)
{
for (int i = 0; i < dstt.Tables[0].Rows.Count; i++)
{
tstock = Convert.ToInt32(dstt.Tables[0].Rows[i]["Stock"]);
trol = Convert.ToInt32(dstt.Tables[0].Rows[i]["brand %"]);
troll = Convert.ToDouble(dstt.Tables[0].Rows[i]["price"]) / 100;
if ((trol < 0) || (trol > 0))
{
dstt.Tables[0].Rows[i].Delete();
}
}
dstt.Tables[0].AcceptChanges();
}
}
//DataTable dtt = dstt.Tables[0];
//DataSet dsttt = new DataSet();
//dsttt.Tables.Add(dtt);
// ExportToExcel(dt);
Grdreport.Visible = true;
Grdreport.DataSource = dstt;
Grdreport.DataBind();
// ExportToExcel(dtt);
}
else
{
ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
}
}
else
{
ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
}
}
else
{
ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
}
}
public void bindDataSubTotBranch(string cond)
{
DateTime startDate, endDate, Transdt;
startDate = Convert.ToDateTime(Request.QueryString["startdate"].ToString());
endDate = Convert.ToDateTime(Request.QueryString["enddate"].ToString());
string condtion = "";
condtion = getCond();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
objBL = new BusinessLogic(ConfigurationManager.ConnectionStrings[Request.Cookies["Company"].Value].ToString());
//DataSet dsGird = GenerateGridColumns();
DataSet dst = new DataSet();
DataSet dts = new DataSet();
DataSet dstt = new DataSet();
DataSet dsGir = new DataSet();
DataSet dsG = new DataSet();
dst = objBL.gettotalBranchsales(startDate, endDate);
dts = objBL.gettotalBranchsalesdate(startDate, endDate);
if (dts.Tables[0].Rows.Count > 0)
{
dstt = objBL.ListBranch();
DataTable dtt = new DataTable();
DataColumn dc;
if (dstt.Tables[0].Rows.Count > 0)
{
dc = new DataColumn("Date");
dtt.Columns.Add(dc);
for (int i = 0; i < dstt.Tables[0].Rows.Count; i++)
{
string ledger = dstt.Tables[0].Rows[i]["Branchcode"].ToString();
dc = new DataColumn(ledger);
dtt.Columns.Add(dc);
}
dc = new DataColumn("Total");
dtt.Columns.Add(dc);
}
dsGir.Tables.Add(dtt);
DataRow dr_final14 = dtt.NewRow();
dtt.Rows.Add(dr_final14);
double credit = 0.00;
double Tottot = 0.00;
DateTime Transd = Convert.ToDateTime(Request.QueryString["startdate"].ToString());
foreach (DataRow dre in dts.Tables[0].Rows)
{
DataRow dr_final12 = dtt.NewRow();
Transdt = Convert.ToDateTime(dre["BillDate"]);
credit = 0.00;
foreach (DataRow dr in dst.Tables[0].Rows)
{
Transd = Convert.ToDateTime(dr["BillDate"]);
if (Transdt == Transd)
{
string aa = dr["BillDate"].ToString().ToUpper().Trim();
string dtaa = Convert.ToDateTime(aa).ToString("dd/MM/yyyy");
dr_final12["Date"] = dtaa;
string ledgernam = dr["Branchcode"].ToString().ToUpper().Trim();
// Tottot = Tottot + double.Parse(dr["Quantity"].ToString());
for (int ii = 1; ii < dsGir.Tables[0].Columns.Count; ii++)
{
string ledgerna = dsGir.Tables[0].Columns[ii].ToString();
if (ledgernam == ledgerna)
{
dr_final12[ledgerna] = double.Parse(dr["Quantity"].ToString());
credit = credit + double.Parse(dr["Quantity"].ToString());
Tottot = Tottot + double.Parse(dr["Quantity"].ToString());
}
else
{
if (dr_final12[ledgerna] == null)
{
dr_final12[ledgerna] = "";
}
}
}
dr_final12["Total"] = credit;
}
}
dtt.Rows.Add(dr_final12);
}
DataRow dr_final11 = dtt.NewRow();
dtt.Rows.Add(dr_final11);
DataRow dr_final88 = dtt.NewRow();
dr_final88["Date"] = "Total";
dr_final88["Total"] = Tottot;
dtt.Rows.Add(dr_final88);
Grdreport.Visible = true;
Grdreport.DataSource = dtt;
Grdreport.DataBind();
// ExportToExcel(dtt);
}
else
{
ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
}
}
//// private string sDataSource = string.Empty;
// private string Connection = string.Empty;
protected void Page_Load(object sender, EventArgs e)
{
try
{
sDataSource = ConfigurationManager.ConnectionStrings[Request.Cookies["Company"].Value].ToString();
Connection = Request.Cookies["Company"].Value;
if (!IsPostBack)
{
DataSet companyInfo = new DataSet();
BusinessLogic bl = new BusinessLogic(sDataSource);
if (Request.Cookies["Company"] != null)
{
lblHeading.Text = "Bank Reconciliation Report ";
companyInfo = bl.getCompanyInfo(Request.Cookies["Company"].Value);
if (companyInfo != null)
{
if (companyInfo.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in companyInfo.Tables[0].Rows)
{
lblTNGST.Text = Convert.ToString(dr["TINno"]);
lblCompany.Text = Convert.ToString(dr["CompanyName"]);
lblPhone.Text = Convert.ToString(dr["Phone"]);
lblGSTno.Text = Convert.ToString(dr["GSTno"]);
lblAddress.Text = Convert.ToString(dr["Address"]);
lblCity.Text = Convert.ToString(dr["city"]);
lblPincode.Text = Convert.ToString(dr["Pincode"]);
lblState.Text = Convert.ToString(dr["state"]);
}
}
}
DataSet ds1 = bl.getImageInfo();
if (ds1 != null)
{
if (ds1.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
{
Image1.ImageUrl = "App_Themes/NewTheme/images/" + ds1.Tables[0].Rows[i]["img_filename"];
Image1.Height = 95;
Image1.Width = 114;
}
}
else
{
Image1.Height = 95;
Image1.Width = 114;
Image1.ImageUrl = "App_Themes/NewTheme/images/TESTLogo.png";
}
}
}
}
lblBillDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
//txtStartDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
DateTime indianStd = TimeZoneInfo.ConvertTimeBySystemTimeZoneId(DateTime.Now, "India Standard Time");
string dtaa = Convert.ToDateTime(indianStd).ToString("dd/MM/yyyy");
// txtStartDate.Text = dtaa;
// lblHeadDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
//string sDataSource = Server.MapPath("App_Data\\Store0910.mdb");
//string sDataSource = Server.MapPath(ConfigurationSettings.AppSettings["DataSource"].ToString());
divPrint.Visible = true;
divPr.Visible = true;
DataSet dstt = new DataSet();
DataSet ds = new DataSet();
DataSet dsttt = new DataSet();
string connection = Request.Cookies["Company"].Value;
string btnlist = Convert.ToString(Request.QueryString["list"].ToString());
int iLedgerID = Convert.ToInt32(Request.QueryString["ledger"].ToString());
string Types = Convert.ToString(Request.QueryString["type"].ToString());
objBL = new BusinessLogic(ConfigurationManager.ConnectionStrings[Request.Cookies["Company"].Value].ToString());
//startDate = Convert.ToDateTime(txtSrtDate.Text);
string usernam = Request.Cookies["LoggedUserName"].Value;
if (btnlist == "All")
{
dsttt = objBL.checkbankreconciliation1(iLedgerID, sDataSource, usernam, Types);
if (dsttt != null)
{
if (dsttt.Tables[0].Rows.Count > 0)
{
dsttt = objBL.getbankreconciliation2(iLedgerID, sDataSource, usernam);
ds = objBL.getbankrecon1(iLedgerID, sDataSource, usernam, Types);
if (dsttt != null)
{
if (dsttt.Tables[0].Rows.Count > 0)
{
ds.Tables[0].Merge(dsttt.Tables[0]);
}
}
}
else
{
ds = objBL.getbankreconciliation3(iLedgerID, sDataSource, usernam);
}
}
else
{
ds = objBL.getbankreconciliation3(iLedgerID, sDataSource, usernam);
}
if (ds != null)
{
DataTable dt = new DataTable("bank ercon");
if (ds.Tables[0].Rows.Count > 0)
{
dt.Columns.Add(new DataColumn("TransNo"));
dt.Columns.Add(new DataColumn("TransDate"));
dt.Columns.Add(new DataColumn("Name"));
dt.Columns.Add(new DataColumn("Ledger Name"));
dt.Columns.Add(new DataColumn("Branch"));
dt.Columns.Add(new DataColumn("Amount"));
dt.Columns.Add(new DataColumn("Narration"));
dt.Columns.Add(new DataColumn("Voucher Type"));
dt.Columns.Add(new DataColumn("ChequeNo"));
dt.Columns.Add(new DataColumn("Reconcilated By"));
dt.Columns.Add(new DataColumn("Reconcilated Date"));
dt.Columns.Add(new DataColumn("Remarks"));
DataRow dr_final679 = dt.NewRow();
dt.Rows.Add(dr_final679);
foreach (DataRow dr in ds.Tables[0].Rows)
{
DataRow dr_final6 = dt.NewRow();
dr_final6["TransNo"] = dr["TransNo"];
dr_final6["TransDate"] = dr["TransDate"];
dr_final6["Name"] = dr["Debtor"];
dr_final6["Ledger Name"] = dr["Creditor"];
dr_final6["Branch"] = dr["BranchCode"];
dr_final6["Amount"] = dr["Amount"];
dr_final6["Narration"] = dr["Narration"];
dr_final6["ChequeNo"] = dr["ChequeNo"];
dr_final6["Voucher Type"] = dr["VoucherType"];
dr_final6["Reconcilated By"] = dr["ReconcilatedBy"];
dr_final6["Reconcilated Date"] = dr["Reconcilateddate"];
dr_final6["Remarks"] = dr["Result"];
dt.Rows.Add(dr_final6);
}
// ExportToExcel(dt);
dstt.Tables.Add(dt);
}
else
{
ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
}
}
else
{
ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
}
}
else if (btnlist == "Pending")
{
dsttt = objBL.getbankreconciliation2(iLedgerID, sDataSource, usernam);
if (dsttt != null)
{
DataTable dt = new DataTable("Bank recon");
if (dsttt.Tables[0].Rows.Count > 0)
{
dt.Columns.Add(new DataColumn("TransNo"));
dt.Columns.Add(new DataColumn("TransDate"));
dt.Columns.Add(new DataColumn("Name"));
dt.Columns.Add(new DataColumn("Ledger Name"));
dt.Columns.Add(new DataColumn("Branch"));
dt.Columns.Add(new DataColumn("Amount"));
dt.Columns.Add(new DataColumn("Narration"));
dt.Columns.Add(new DataColumn("Voucher Type"));
dt.Columns.Add(new DataColumn("ChequeNo"));
dt.Columns.Add(new DataColumn("Reconcilated By"));
dt.Columns.Add(new DataColumn("Reconcilated Date"));
dt.Columns.Add(new DataColumn("Remarks"));
DataRow dr_final679 = dt.NewRow();
dt.Rows.Add(dr_final679);
foreach (DataRow dr in dsttt.Tables[0].Rows)
{
DataRow dr_final6 = dt.NewRow();
dr_final6["TransNo"] = dr["TransNo"];
dr_final6["TransDate"] = dr["TransDate"];
dr_final6["Name"] = dr["Debtor"];
dr_final6["Ledger Name"] = dr["Creditor"];
dr_final6["Branch"] = dr["BranchCode"];
dr_final6["Amount"] = dr["Amount"];
dr_final6["Narration"] = dr["Narration"];
dr_final6["ChequeNo"] = dr["ChequeNo"];
dr_final6["Voucher Type"] = dr["VoucherType"];
dr_final6["Reconcilated By"] = dr["ReconcilatedBy"];
dr_final6["Reconcilated Date"] = dr["Reconcilateddate"];
dr_final6["Remarks"] = dr["Result"];
dt.Rows.Add(dr_final6);
}
// ExportToExcel(dt);
dstt.Tables.Add(dt);
}
else
{
ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
}
}
else
{
ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
}
}
else if (btnlist == "Reconciliated")
{
ds = objBL.getbankrecon1(iLedgerID, sDataSource, usernam, Types);
if (ds != null)
{
DataTable dt = new DataTable("Bank recon");
if (ds.Tables[0].Rows.Count > 0)
{
dt.Columns.Add(new DataColumn("TransNo"));
dt.Columns.Add(new DataColumn("TransDate"));
dt.Columns.Add(new DataColumn("Name"));
dt.Columns.Add(new DataColumn("Ledger Name"));
dt.Columns.Add(new DataColumn("BranchCode"));
dt.Columns.Add(new DataColumn("Amount"));
dt.Columns.Add(new DataColumn("Narration"));
dt.Columns.Add(new DataColumn("Voucher Type"));
dt.Columns.Add(new DataColumn("ChequeNo"));
dt.Columns.Add(new DataColumn("Reconcilated By"));
dt.Columns.Add(new DataColumn("Reconcilated Date"));
dt.Columns.Add(new DataColumn("Remarks"));
DataRow dr_final679 = dt.NewRow();
dt.Rows.Add(dr_final679);
foreach (DataRow dr in ds.Tables[0].Rows)
{
DataRow dr_final6 = dt.NewRow();
dr_final6["TransNo"] = dr["TransNo"];
dr_final6["TransDate"] = dr["TransDate"];
dr_final6["Name"] = dr["Debtor"];
dr_final6["Ledger Name"] = dr["Creditor"];
dr_final6["Branch"] = dr["BranchCode"];
dr_final6["Amount"] = dr["Amount"];
dr_final6["Narration"] = dr["Narration"];
dr_final6["ChequeNo"] = dr["ChequeNo"];
dr_final6["Voucher Type"] = dr["VoucherType"];
dr_final6["Reconcilated By"] = dr["ReconcilatedBy"];
dr_final6["Reconcilated Date"] = dr["Reconcilateddate"];
dr_final6["Remarks"] = dr["Result"];
dt.Rows.Add(dr_final6);
}
// ExportToExcel(dt);
dstt.Tables.Add(dt);
}
else
{
ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
}
}
else
{
ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
}
}
if (dstt.Tables[0].Rows.Count > 0)
{
// ReportsBL.ReportClass rptStock = new ReportsBL.ReportClass();
Grdreport.Visible = true;
Grdreport.DataSource = dstt;
Grdreport.DataBind();
}
else
{
ScriptManager.RegisterStartupScript(Page, typeof(Button), "MyScript", "alert('No Data Found');", true);
}
}
catch (Exception ex)
{
TroyLiteExceptionManager.HandleException(ex);
}
}