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