public int SaveRowData(SaleRegister sr)
{
string query = "INSERT INTO [dbo].[SalesRegister] " +
"( [InvoiceNo], [InvoiceDate], [InvoiceType], " +
"[Qty], [MRP], [Discount], [BasicAmt], [TaxAmount], " +
"[RoundOff], [BillAmount], [PaymentMode]) " +
/*"[Coupon], [CouponAmt], [TalioringFlag], " +
* "[InstOrderCD])" +*/
" VALUES (@Inv,@InvD , @InvT, @QTY, @MRP, @Dis, @BAMT, " +
"@TAMT, @ROFF, @BILL, @PM)"; //, @CO,@CAMT, @TFG, @INST)";
SqlCommand cmd;
if (sqlDB == null || sqlDB.State != ConnectionState.Open)
{
cmd = new SqlCommand(query, (SqlConnection)DataBase.GetConnectionObject(ConType.SQLDB));
}
else
{
cmd = new SqlCommand(query, sqlDB);
}
// cmd.CommandText = query;
//cmd.Connection = GetConnection();
cmd.Parameters.AddWithValue("@Inv", sr.InvoiceNo);
cmd.Parameters.AddWithValue("@InvD", sr.InvDate);
cmd.Parameters.AddWithValue("@InvT", sr.InvType);
cmd.Parameters.AddWithValue("@QTY", sr.QTY);
cmd.Parameters.AddWithValue("@MRP", sr.MRP);
cmd.Parameters.AddWithValue("@Dis", sr.Discount);
cmd.Parameters.AddWithValue("@BAMT", sr.BasicRate);
cmd.Parameters.AddWithValue("@TAMT", sr.Tax);
cmd.Parameters.AddWithValue("@ROFF", sr.RoundOff);
cmd.Parameters.AddWithValue("@BILL", sr.BillAmnt);
cmd.Parameters.AddWithValue("@PM", sr.paymentType);
//cmd.Parameters.AddWithValue("@CO", sr.coupon);
//cmd.Parameters.AddWithValue("@CAMT", sr.couponAmt);
//cmd.Parameters.AddWithValue("@TFG", sr.Tailoring);
//cmd.Parameters.AddWithValue("@INST", sr.instaorder);
return(InsertQuerySql(cmd));
}
public int ReadDataSaleRegister(string fname, int start, int end, ProgressBar pBar)
{
DataTable dt = new DataTable("SalesRegister");
int Row = 0;
int r = 0, c = 0;
SaleRegister sr;
Logs.LogMe("Started reading");
foreach (var worksheet in Workbook.Worksheets(fname))
{
Logs.LogMe(worksheet.ToString());
foreach (var row in worksheet.Rows)
{
Logs.LogMe("Row=" + row.ToString() + "RowNo=" + Row);
if (Row <= end)
{
if (Row >= start)
{
Logs.LogMe("iRow=" + r);
sr = new SaleRegister();
c = 0;
foreach (var cell in row.Cells)
{
if (cell != null)
{
c = AddCol(cell, ref sr, c);
}
else
{
Logs.LogMe("C=" + c + "Null");
}
c++;
}
if (DB.SaveRowData(sr) > 0)
{
r++;
pBar.BeginInvoke(new Action(() =>
{
pBar.PerformStep();
}));
Logs.LogMe("Row=" + r + " got saved");
//if (InvokeRequired)
//{
// this.Invoke((MethodInvoker)delegate { pBar.PerformStep(); });
//}
}
}
Row++;
Logs.LogMe("Row will be" + Row + "\tr=" + r);
}
else
{
Logs.LogMe("End Target Matched , Breaking out now");
break;
}
}
}
Logs.LogMe("end , record=" + r);
return(r);
}
private int AddCol(Cell cell, ref SaleRegister sr, int c)
{
Logs.LogMe("cell(" + cell.ColumnIndex + ")=" + cell.Text);
switch (cell.ColumnIndex)
{
case 0:
sr.InvoiceNo = cell.Text;
c++;
break;
case 1:
sr.InvDate = DataConvertor.DateFromExcelFormatString(cell.Text);
c++;
break;
case 2:
sr.InvType = cell.Text;
c++;
break;
case 3:
sr.QTY = (int)cell.Amount;
c++;
break;
case 4:
sr.MRP = cell.Amount;
c++;
break;
case 6:
sr.BasicRate = cell.Amount;
c++;
break;
case 5:
sr.Discount = cell.Amount;
c++;
break;
case 7:
sr.Tax = cell.Amount;
c++;
break;
case 8:
sr.RoundOff = cell.Amount;
c++;
break;
case 9:
sr.BillAmnt = cell.Amount;
c++;
break;
case 10:
sr.paymentType = cell.Text;
c++;
break;
// case 11: sr.coupon = cell.Text; c++; break; case 12: sr.couponAmt = cell.Text;
// c++; break; case 13: sr.LP = cell.Value; c++; break; case 14: sr.instaorder =
// cell.Text; c++; break; case 15: sr.Tailoring = cell.Text; c++; break;
}
return(c);
}