/// <summary>
/// Method to write into the excel report file to print.
/// </summary>
public void ConvertToExcel()
{
//InventoryClass obj=new InventoryClass();
SqlDataReader SqlDtr = null;
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\StockLedgerReport.xls";
StreamWriter sw = new StreamWriter(path);
string prod_name = "";
string pack1 = "";
string pack2 = "";
string trans_type = "";
string Cat = "";
//prod_name = drpProductName.SelectedItem.Value.ToString().Trim();
prod_name = drpProductName.Value.ToString().Trim();
if (prod_name.LastIndexOf(":") > -1)
{
string[] strArr = prod_name.Split(new char[] { ':' }, prod_name.Length);
prod_name = strArr[0].Trim();
if (strArr[1].Trim().IndexOf("Loose") > -1)
{
pack1 = "0";
pack2 = "0";
Cat = "Loose";
}
else
{
string[] strPack = strArr[1].Trim().Split(new char[] { 'X' }, strArr[1].Length);
pack1 = strPack[0].Trim();
pack2 = strPack[1].Trim();
Cat = "Others";
}
}
else
{
pack1 = "0";
pack2 = "0";
Cat = "Fuel";
}
//int f = 0;
//trans_type = drpTransType.SelectedItem.Value.ToString().Trim();
object obj = null;
dbobj.ExecProc(OprType.Insert, "sp_stockLedger", ref obj, "@Prod_Name", prod_name, "@Pack11", pack1, "@Pack22", pack2, "@Trans_Type", trans_type, "@fromdate", GenUtil.str2MMDDYYYY(txtDateFrom.Text.Trim()), "@Todate", GenUtil.str2MMDDYYYY(txtDateTo.Text.Trim()), "@Cat", Cat);
dbobj.SelectQuery("Select * from Stock_Ledger order by " + Cache["strorderby"] + "", ref SqlDtr);
sw.WriteLine("From Date\t" + txtDateFrom.Text);
sw.WriteLine("To Date\t" + txtDateTo.Text);
//sw.WriteLine("Product Name\t"+drpProductName.SelectedItem.Text);
sw.WriteLine("Product Name\t" + drpProductName.Value);
sw.WriteLine("Transaction Type\t" + drpTransType.SelectedItem.Text);
sw.WriteLine();
sw.WriteLine("Shipping Party\tTransaction Type\tTransaction ID\tDate\tQty Nos(IN)\tQty Ltr(IN)\tQty Nos(OUT)\tQty Ltr(OUT)\tClosing Balance(Nos)\tClosing Balance(Ltr)");
SqlDataReader rdr = null;
if (SqlDtr.HasRows)
{
while (SqlDtr.Read())
{
string trans_no = "";
string Party = "";
if (SqlDtr.GetValue(0).ToString().StartsWith("Sales Invoice"))
{
trans_no = SqlDtr.GetValue(1).ToString();
//*********************
dbobj.SelectQuery("select Cust_Name from Customer c,Sales_Master sm where c.Cust_ID=sm.Cust_ID and Invoice_No='" + trans_no + "'", ref rdr);
if (rdr.Read())
{
Party = rdr["Cust_Name"].ToString();
}
rdr.Close();
//*********************
if (System.Convert.ToString(int.Parse(FromDate).ToString() + ToDate).Length > 3)
{
trans_no = trans_no.Substring(4);
}
else
{
trans_no = trans_no.Substring(3);
}
}
//*********************
else if (SqlDtr.GetValue(0).ToString().StartsWith("Purchase Invoice"))
{
trans_no = SqlDtr.GetValue(1).ToString();
dbobj.SelectQuery("select supp_name from purchase_master pm,supplier s where supp_id=vendor_id and Invoice_No='" + trans_no + "'", ref rdr);
if (rdr.Read())
{
Party = rdr["Supp_Name"].ToString();
}
rdr.Close();
}
//*********************
else
{
trans_no = SqlDtr.GetValue(1).ToString();
}
sw.WriteLine(Party + "\t" + SqlDtr.GetValue(0).ToString() + "\t" +
//SqlDtr.GetValue(1).ToString()+"\t"+
trans_no + "\t" +
GenUtil.str2MMDDYYYY(trimDate(SqlDtr.GetValue(2).ToString())) + "\t" +
checkValue1(SqlDtr.GetValue(3).ToString()) + "\t" +
checkValue1(SqlDtr.GetValue(4).ToString()) + "\t" +
checkValue1(SqlDtr.GetValue(5).ToString()) + "\t" +
checkValue1(SqlDtr.GetValue(6).ToString()) + "\t" +
checkValue1(SqlDtr.GetValue(7).ToString()) + "\t" +
checkValue1(SqlDtr.GetValue(8).ToString()));
}
sw.WriteLine("Total\t\t\t\t" + InQtyNos.ToString() + "\t" + InQtyLtr.ToString() + "\t" + OutQtyNos.ToString() + "\t" + OutQtyLtr.ToString() + "\t" + closing_bal_nos.ToString() + "\t" + closing_bal_ltr.ToString());
}
else
{
Stock_Ledger.Visible = false;
sw.Close();
MessageBox.Show("Data not available");
return;
}
SqlDtr.Close();
sw.Close();
}
/// <summary>
/// Its fires the sp_StockLedger procedure and fetch the values from Stock_Ledger Report and writes the return values into StockLedgerReport.txt file.
/// </summary>
protected void btnPrint_Click(object sender, System.EventArgs e)
{
if (!checkValidity())
{
return;
}
/*
* =================================================
* Stock Ledger Report From mm/dd/yyyy To mm/dd/yyyy
* =================================================
*
* Product Name : Petrol(MS)
* Transaction Type : Sales
+----------------------+-----+----------+------------+------------+------------+
| | | | IN | OUT |CLOSING BAL.|
| Transaction |Trans| Date |----+-------|----+-------|----+-------|
| Type | ID | |Qty.|Qty. in|Qty.|Qty. in|Bal.|Bal. in|
| | | |Nos | Ltr. |Nos | Ltr. |Nos | Ltr. |
+----------------------+-----+----------+----+-------+----+-------+----+-------+
| Opening Balance 1001 mm/dd/yyyy 1234 1234567 1234 1234567 1234 1234567
| Closing Balance
| Purchase Invoice
| Stock Adjustment(OUT)
| 123456789012345678|
+----------------------+-----+----------+----+-------+----+-------+----+-------+
*/
try
{
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\StockLedgerReport.txt";
StreamWriter sw = new StreamWriter(path);
string info = "";
// Condensed
sw.Write((char)27); //added by vishnu
sw.Write((char)67); //added by vishnu
sw.Write((char)0); //added by vishnu
sw.Write((char)12); //added by vishnu
sw.Write((char)27); //added by vishnu
sw.Write((char)78); //added by vishnu
sw.Write((char)5); //added by vishnu
sw.Write((char)27); //added by vishnu
sw.Write((char)15);
//**********
string des = "--------------------------------------------------------------------------------";
string Address = GenUtil.GetAddress();
string[] addr = Address.Split(new char[] { ':' }, Address.Length);
sw.WriteLine(GenUtil.GetCenterAddr(addr[0], des.Length).ToUpper());
sw.WriteLine(GenUtil.GetCenterAddr(addr[1] + addr[2], des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("Tin No : " + addr[3], des.Length));
sw.WriteLine(des);
//**********
sw.WriteLine(GenUtil.GetCenterAddr("=================================================", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("Stock Ledger Report From " + txtDateFrom.Text.Trim() + " To " + txtDateTo.Text.Trim(), des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("=================================================", des.Length));
//sw.WriteLine("");
sw.WriteLine("Product Name : " + drpProductName.Value);
//sw.WriteLine("Transaction Type : "+drpTransType.SelectedItem.Value);
sw.WriteLine("+------------------------------+----------------------+-----+----------+------------+------------+------------+ ");
sw.WriteLine("| | | | | IN | OUT |CLOSING BAL.|");
sw.WriteLine("| Shipping Party | Transaction |Trans| Date |----+-------|----+-------|----+-------|");
sw.WriteLine("| | | ID | |Qty.|Qty. in|Qty.|Qty. in|Bal.|Bal. in|");
sw.WriteLine("| | | | |Nos | Ltr. |Nos | Ltr. |Nos | Ltr. |");
sw.WriteLine("+------------------------------+----------------------+-----+----------+----+-------+----+-------+----+-------+");
// 123456789012345678901234567890 1234567890123456789012 1001 mm/dd/yyyy 1234 1234567 1234 1234567 1234 1234567
info = " {0,-30:S} {1,-22:S} {2,-5:S} {3,-10:F} {4,4:F} {5,7:F} {6,4:F} {7,7:F} {8,4:F} {9,7:F}";
string prod_name = "";
string pack1 = "";
string pack2 = "";
string trans_type = "";
string Cat = "";
prod_name = drpProductName.Value.ToString().Trim();
if (prod_name.LastIndexOf(":") > -1)
{
string[] strArr = prod_name.Split(new char[] { ':' }, prod_name.Length);
prod_name = strArr[0].Trim();
if (strArr[1].Trim().IndexOf("Loose") > -1)
{
pack1 = "0";
pack2 = "0";
Cat = "Loose";
}
else
{
string[] strPack = strArr[1].Trim().Split(new char[] { 'X' }, strArr[1].Length);
pack1 = strPack[0].Trim();
pack2 = strPack[1].Trim();
Cat = "Others";
}
}
else
{
pack1 = "0";
pack2 = "0";
Cat = "Fuel";
}
int f = 0;
trans_type = drpTransType.SelectedItem.Value.ToString().Trim();
object obj = null;
dbobj.ExecProc(OprType.Insert, "sp_stockLedger", ref obj, "@Prod_Name", prod_name, "@Pack11", pack1, "@Pack22", pack2, "@Trans_Type", trans_type, "@fromdate", GenUtil.str2MMDDYYYY(txtDateFrom.Text.Trim()), "@Todate", GenUtil.str2MMDDYYYY(txtDateTo.Text.Trim()), "@Cat", Cat);
SqlDataReader SqlDtr = null;
//dbobj.SelectQuery("Select * from Stock_Ledger order by trans_date ",ref SqlDtr);
dbobj.SelectQuery("Select * from Stock_Ledger order by " + Cache["strorderby"] + "", ref SqlDtr);
//sql=sql+" order by "+""+Cache["strorderby"]+"";
SqlDataReader rdr = null;
if (SqlDtr.HasRows)
{
while (SqlDtr.Read())
{
string trans_no = "";
string Party = "";
if (SqlDtr.GetValue(0).ToString().StartsWith("Sales Invoice"))
{
trans_no = SqlDtr.GetValue(1).ToString();
//*********************
//Coment by vikas 23.10.09 dbobj.SelectQuery("select Cust_Name from Customer c,Sales_Master sm where c.Cust_ID=sm.Cust_ID and Invoice_No='"+trans_no+"'",ref rdr);
dbobj.SelectQuery("select Cust_Name+':'+cast(c.City as varchar) Cust_Name from Customer c,Sales_Master sm where c.Cust_ID=sm.Cust_ID and Invoice_No='" + trans_no + "'", ref rdr);
if (rdr.Read())
{
Party = rdr["Cust_Name"].ToString();
}
rdr.Close();
//*********************
if (System.Convert.ToString(int.Parse(FromDate).ToString() + ToDate).Length > 3)
{
trans_no = trans_no.Substring(4);
}
else
{
trans_no = trans_no.Substring(3);
}
}
//*********************
else if (SqlDtr.GetValue(0).ToString().StartsWith("Purchase Invoice"))
{
trans_no = SqlDtr.GetValue(1).ToString();
dbobj.SelectQuery("select supp_name from purchase_master pm,supplier s where supp_id=vendor_id and Invoice_No='" + trans_no + "'", ref rdr);
if (rdr.Read())
{
Party = rdr["Supp_Name"].ToString();
}
rdr.Close();
}
//*********************
else
{
trans_no = SqlDtr.GetValue(1).ToString();
}
sw.WriteLine(info, GenUtil.TrimLength(Party, 30), SqlDtr.GetValue(0).ToString(),
//SqlDtr.GetValue(1).ToString(),
trans_no,
GenUtil.str2MMDDYYYY(trimDate(SqlDtr.GetValue(2).ToString())),
checkValue1(SqlDtr.GetValue(3).ToString()),
checkValue1(SqlDtr.GetValue(4).ToString()),
checkValue1(SqlDtr.GetValue(5).ToString()),
checkValue1(SqlDtr.GetValue(6).ToString()),
checkValue1(SqlDtr.GetValue(7).ToString()),
checkValue1(SqlDtr.GetValue(8).ToString()));
}
}
else
{
Stock_Ledger.Visible = false;
f = 1;
sw.Close();
MessageBox.Show("Data not available");
return;
}
SqlDtr.Close();
sw.WriteLine("+------------------------------+----------------------+-----+----------+----+-------+----+-------+----+-------+");
sw.WriteLine(info, " Total", "", "", "", InQtyNos.ToString(), InQtyLtr.ToString(), OutQtyNos.ToString(), OutQtyLtr.ToString(), closing_bal_nos.ToString(), closing_bal_ltr.ToString());
sw.WriteLine("+------------------------------+----------------------+-----+----------+----+-------+----+-------+----+-------+");
SqlDtr.Close();
sw.Close();
if (f == 0)
{
Print();
}
else
{
return;
}
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:StockLedgerReport.aspx,Method:btnPrint_Click" + " EXCEPTION " + ex.Message + " userid " + uid);
}
}