/// <summary>
/// Method to write into the excel report file to print.
/// </summary>
public void ConvertToExcel()
{
i = 1;
InventoryClass obj = new InventoryClass();
SqlDataReader rdr;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string strExcelPath = home_drive + "\\Servosms_ExcelFile\\Export\\";
Directory.CreateDirectory(strExcelPath);
string path = home_drive + @"\Servosms_ExcelFile\Export\SchemeSecondrySalesReport.xls";
StreamWriter sw = new StreamWriter(path);
string sql = "";
if (chkFOC.Checked)
{
sql = "select distinct cust_name,p.Prod_ID,prod_name+':'+pack_type ProdName,Pack_Type,Prod_Code,cust_type,substring(cast(sm.invoice_no as varchar),4,10) Invoice_No,sm.invoice_date,qty*total_qty as qty,Amount,c.cust_id,qty ltr from sales_master sm,customer c,sales_details sd,products p where sm.cust_id=c.cust_id and sd.invoice_no=sm.invoice_no and p.prod_id=sd.prod_id and cast(floor(cast(invoice_date as float)) as datetime)>=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateFrom"].ToString()) + "',103) and cast(floor(cast(invoice_date as float)) as datetime)<=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateTo"].ToString()) + "',103)";
}
else
{
sql = "select distinct cust_name,p.Prod_ID,prod_name+':'+pack_type ProdName,Pack_Type,Prod_Code,cust_type,substring(cast(sm.invoice_no as varchar),4,10) Invoice_No,sm.invoice_date,qty*total_qty as qty,Amount,c.cust_id,qty ltr from sales_master sm,customer c,sales_details sd,products p where sm.cust_id=c.cust_id and sd.invoice_no=sm.invoice_no and p.prod_id=sd.prod_id and amount>0 and cast(floor(cast(invoice_date as float)) as datetime)>=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateFrom"].ToString()) + "',103) and cast(floor(cast(invoice_date as float)) as datetime)<=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateTo"].ToString()) + "',103)";
}
sql = sql + " order by " + Cache["strOrderBy"];
rdr = obj.GetRecordSet(sql);
if (droptype.SelectedIndex == 0)
{
sw.WriteLine("Product\tPack Type\tMaterial Code\tCustomer Name\tName of Customer Category\t\t\t\t\tCustomer Category\tInvoice No\tInvoice Date\tLtr/Discount\tDiscount allowed since previous report");
sw.WriteLine("\t\t\t\tRO\tBazar\tFleet\tOE\tOthers\t\t\t\t\tQty(KL)\tAmount(Rs.)");
}
else
{
sw.WriteLine("SN\tDate\tBill No\tCust Type\tName Of Customer\tProduct Code\tProd Name\tPack TYpe\tQty\tLTR\tDiscount\tSch Disc Total");
}
while (rdr.Read())
{
if (droptype.SelectedIndex == 0)
{
sw.WriteLine(rdr["ProdName"].ToString() + "\t" +
rdr["Pack_type"].ToString() + "\t" +
rdr["Prod_Code"].ToString() + "\t" +
rdr["Cust_Name"].ToString() + "\t" +
GetRO(rdr["Cust_Type"].ToString(), rdr["qty"].ToString()) + "\t" +
GetBazar(rdr["Cust_Type"].ToString(), rdr["qty"].ToString()) + "\t" +
GetFleet(rdr["Cust_Type"].ToString(), rdr["qty"].ToString()) + "\t" +
GetOE(rdr["Cust_Type"].ToString(), rdr["qty"].ToString()) + "\t" +
GetOthers(rdr["Cust_Type"].ToString(), rdr["qty"].ToString()) + "\t" +
rdr["Cust_Type"].ToString() + "\t" +
rdr["Invoice_No"].ToString() + "\t" +
GenUtil.str2DDMMYYYY(GenUtil.trimDate(rdr["Invoice_Date"].ToString())) + "\t" +
GetDiscount(rdr["Prod_ID"].ToString(), rdr["Invoice_Date"].ToString()) + "\t" +
rdr["Qty"].ToString() + "\t" +
GetAmount(rdr["Qty"].ToString())
);
}
else
{
sw.WriteLine(i.ToString() + "\t" +
GenUtil.str2DDMMYYYY(GenUtil.trimDate(rdr["Invoice_Date"].ToString())) + "\t" +
rdr["Invoice_No"].ToString() + "\t" +
rdr["Cust_Type"].ToString() + "\t" +
rdr["Cust_Name"].ToString() + "\t" +
rdr["Prod_Code"].ToString() + "\t" +
rdr["ProdName"].ToString() + "\t" +
rdr["Pack_type"].ToString() + "\t" +
rdr["qty"].ToString() + "\t" +
rdr["ltr"].ToString() + "\t" +
Discount(rdr["Prod_id"].ToString()) + "\t" +
DiscAmt(rdr["Prod_id"].ToString(), rdr["Qty"].ToString())
);
i++;
}
}
if (droptype.SelectedIndex == 0)
{
sw.WriteLine(" Total\t\t\t\t" + TotalRo.ToString() + "\t" + TotalBazar.ToString() + "\t" + TotalFleet.ToString() + "\t" + TotalOE.ToString() + "\t" + TotalOthers.ToString() + "\t\t\t\t\t\t" + TotalAmount.ToString());
}
else
{
sw.WriteLine("\tTotal\t\t\t\t\t\t\t\t\t\t" + TotClaim.ToString());
}
sw.Close();
}
protected void gvInviceCount_ItemDataBound(object sender, GridItemEventArgs e)
{
//foreach (GridDataItem dataItem in gvInviceCount.MasterTableView.Items)
//{
// //TextBox txtLT = (TextBox)(dataItem.FindControl("txtRequest_Qty") as TextBox);
// //TextBox txtUNt = (TextBox)(dataItem.FindControl("txtUnitPrice") as TextBox);
// //TextBox txtDis = (TextBox)(dataItem.FindControl("txtDiscount") as TextBox);
// HiddenField lblID = (HiddenField)(dataItem.FindControl("lblID") as HiddenField);
// ImageButton btnDelete = (ImageButton)(dataItem.FindControl("ImgDelete") as ImageButton);
//}
if (e.Item is GridDataItem)
{
GridDataItem dataItem = e.Item as GridDataItem;
if ((dataItem["Clean"].FindControl("btnClean") as Button).Text != "")
{
totalClean += int.Parse((dataItem["Clean"].FindControl("btnClean") as Button).Text);
}
if ((dataItem["Dirty"].FindControl("btnDirty") as Button).Text != "")
{
TotalDirty += int.Parse((dataItem["Dirty"].FindControl("btnDirty") as Button).Text);
}
if ((dataItem["DIRECTINV"].FindControl("btnDIRECTINV") as Button).Text != "")
{
TotalDirectInv += int.Parse((dataItem["DIRECTINV"].FindControl("btnDIRECTINV") as Button).Text);
}
if ((dataItem["LATEPO"].FindControl("btnLATEPO") as Button).Text != "")
{
TotalLatePO += int.Parse((dataItem["LATEPO"].FindControl("btnLATEPO") as Button).Text);
}
if ((dataItem["REWORKIN"].FindControl("btnREWORKIN") as Button).Text != "")
{
TotalReworkIN += int.Parse((dataItem["REWORKIN"].FindControl("btnREWORKIN") as Button).Text);
}
if ((dataItem["REWORKOUT"].FindControl("btnREWORKOUT") as Button).Text != "")
{
TotalReworkOut += int.Parse((dataItem["REWORKOUT"].FindControl("btnREWORKOUT") as Button).Text);
}
if ((dataItem["OTHERS"].FindControl("btnOTHERS") as Button).Text != "")
{
TotalOthers += int.Parse((dataItem["OTHERS"].FindControl("btnOTHERS") as Button).Text);
}
TotalInvoice = totalClean + TotalDirty + TotalDirectInv + TotalLatePO + TotalReworkIN + TotalReworkOut + TotalOthers;
}
else if (e.Item is GridFooterItem)
{
GridFooterItem footer = (GridFooterItem)e.Item;
(footer["Clean"].FindControl("lblTotal_Clean") as Label).Text = totalClean.ToString();
clientID = (footer["Clean"].FindControl("lblTotal_Clean") as Label).ClientID;
(footer["Dirty"].FindControl("lblTotal_Dirty") as Label).Text = TotalDirty.ToString();
clientID = (footer["Dirty"].FindControl("lblTotal_Dirty") as Label).ClientID;
(footer["DIRECTINV"].FindControl("lblTotal_Direct") as Label).Text = TotalDirectInv.ToString();
clientID = (footer["DIRECTINV"].FindControl("lblTotal_Direct") as Label).ClientID;
(footer["LATEPO"].FindControl("lblTotal_LatePO") as Label).Text = TotalLatePO.ToString();
clientID = (footer["LATEPO"].FindControl("lblTotal_LatePO") as Label).ClientID;
(footer["REWORKIN"].FindControl("lblTotal_ReworkIN") as Label).Text = TotalReworkIN.ToString();
clientID = (footer["REWORKIN"].FindControl("lblTotal_ReworkIN") as Label).ClientID;
(footer["REWORKOUT"].FindControl("lblTotal_ReworkOUT") as Label).Text = TotalReworkOut.ToString();
clientID = (footer["REWORKOUT"].FindControl("lblTotal_ReworkOUT") as Label).ClientID;
(footer["OTHERS"].FindControl("lblTotal_Others") as Label).Text = TotalOthers.ToString();
clientID = (footer["OTHERS"].FindControl("lblTotal_Others") as Label).ClientID;
(footer["Report_Name"].FindControl("lblTotal_Invoice") as Label).Text = "Total" + " " + TotalInvoice.ToString() + " " + "Invoices";
clientID = (footer["OTHERS"].FindControl("lblTotal_Others") as Label).ClientID;
}
}