/// <summary> /// Save Row Data /// </summary> /// <param name="sr"> SaleItemWise object</param> /// <returns>return no of record added</returns> public int SaveRowData(SaleItemWise sr) { string query = "INSERT INTO [dbo].[Sales] " + "( InvoiceNo, InvoiceDate, InvoiceType, " + "BrandName,ProductName,ItemDescrpetion,HSNCode,BarCode,StyleCode," + "Qty, MRP, Discount, BasicAmt, TaxAmount,SGST,CGST, " + "RoundOff, LineTotal,BillAmount, Salesman,PaymentMode,LP) " + " VALUES (@Inv,@InvD , @InvT, @BName,@PName,@IDes,@hsnCode,@BCode,@SCode," + "@QTY, @MRP, @Dis, @BAMT, @TAMT,@SGST, @CGST, @ROFF,@Ltotal, @BILL,@Sman, @PM, @lp)"; Logs.LogMe("SqlQuery=" + query); SqlCommand cmd = new SqlCommand(query, vDb.DBCon); cmd.Parameters.AddWithValue("@Inv", sr.InvoiceNo); //ok cmd.Parameters.AddWithValue("@InvD", sr.InvDate); //ok cmd.Parameters.AddWithValue("@InvT", sr.InvType); //ok cmd.Parameters.AddWithValue("@hsnCode", sr.HSNCode); //ok cmd.Parameters.AddWithValue("@QTY", sr.QTY); //ok cmd.Parameters.AddWithValue("@MRP", sr.MRP); //ok cmd.Parameters.AddWithValue("@Dis", sr.Discount); //ok cmd.Parameters.AddWithValue("@BAMT", sr.BasicRate); //ok cmd.Parameters.AddWithValue("@TAMT", sr.Tax); //ok cmd.Parameters.AddWithValue("@ROFF", sr.RoundOff); //ok cmd.Parameters.AddWithValue("@BILL", sr.BillAmnt); //ok cmd.Parameters.AddWithValue("@PM", sr.PaymentType); //ok cmd.Parameters.AddWithValue("@Sman", sr.Saleman); //ok cmd.Parameters.AddWithValue("@BCode", sr.Barcode); //ok cmd.Parameters.AddWithValue("@SCode", sr.StyleCode); //ok cmd.Parameters.AddWithValue("@Ltotal", sr.LineTotal); //ok cmd.Parameters.AddWithValue("@BName", sr.BrandName); //ok cmd.Parameters.AddWithValue("@PName", sr.ProductName); //ok cmd.Parameters.AddWithValue("@IDes", sr.ItemDesc); //ok cmd.Parameters.AddWithValue("@SGST", sr.SGST); //ok cmd.Parameters.AddWithValue("@CGST", sr.CGST); //ok cmd.Parameters.AddWithValue("@lp", sr.LP); //ok return(InsertQuerySql(cmd)); }
private int AddColSale(Cell cell, ref SaleItemWise sr, int c) { switch (cell.ColumnIndex) { case 0: //Logs.LogMe("cell(" + cell.ColumnIndex + ")=" + cell.Text); sr.InvoiceNo = cell.Text; c++; break; case 1: //Logs.LogMe ("cell(" + cell.ColumnIndex + ")=" + cell.Text); sr.InvDate = cell.Text; //DataConvertor.DateFromExcelFormatString (cell.Text); c++; break; case 2: sr.InvType = cell.Text; c++; break; case 3: sr.BrandName = cell.Text; c++; break; case 4: sr.ProductName = cell.Text; c++; break; case 5: sr.ItemDesc = cell.Text; c++; break; case 6: sr.HSNCode = cell.Text; //Logs.LogMe("HSNCode:'" + cell.Text + "'"); c++; break; case 7: sr.Barcode = cell.Text; c++; break; case 8: sr.StyleCode = cell.Text; c++; break; case 9: sr.QTY = (int)cell.Amount; c++; break; case 10: sr.MRP = cell.Amount; c++; break; case 11: sr.Discount = cell.Amount; c++; break; case 12: sr.BasicRate = cell.Amount; c++; break; case 13: sr.Tax = cell.Amount; c++; break; case 14: sr.SGST = cell.Amount; c++; break; case 15: sr.CGST = cell.Amount; c++; break; case 16: sr.RoundOff = cell.Amount; c++; break; case 17: sr.LineTotal = cell.Amount; c++; break; case 18: sr.BillAmnt = cell.Amount; c++; break; case 19: sr.PaymentType = cell.Text; c++; break; case 20: sr.Saleman = cell.Text; c++; break; case 25: sr.LP = cell.Text; c++; break; } return(c); }
public int ReadDataSales(string fname, int start, int end, ProgressBar pBar, string tablename) { //TODO: GST Sale Invoice is implemented. Fine tune can be done in future. DataTable dt = new DataTable(tablename); int Row = 0; int r = 0, c = 0; SaleItemWise sr; Logs.LogMe("Started reading"); foreach (var worksheet in Workbook.Worksheets(fname)) { foreach (var row in worksheet.Rows) { // Logs.LogMe ("Row=" + row.ToString () + "RowNo=" + Row); if (Row <= end) { if (Row >= start)// TODO: It will Start from 8 . Do check execl file { // Logs.LogMe ("iRow=" + r); sr = new SaleItemWise(); c = 0; foreach (var cell in row.Cells) { if (cell != null) { c = AddColSale(cell, ref sr, c); } else { if (c == 6) { Logs.LogMe("C=" + c + " is Null"); } c++; } } if (Db.SaveRowData(sr) > 0) { r++; pBar.BeginInvoke(new Action(() => { pBar.PerformStep(); })); Logs.LogMe("Row=" + r + " got saved"); } } Row++; // Logs.LogMe ("Row will be" + Row + "\tr=" + r); } else { Logs.LogMe("End Target Matched , Breaking out now"); break; } } break; } Logs.LogMe("end , record=" + r); return(r); }