/// <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); } }