/// <summary>
///
/// </summary>
/// <param name="sr"></param>
/// <returns></returns>
public int SaveRowData(Cust sr)
{
Logs.LogMe("SaveRowData: Customer");
string[] s;
//TODO: Cust Insert
if (sr != null && sr.FirstName.Trim().Length > 0)
{
if (!IsCustomer(sr.MobileNo))
{
s = sr.FirstName.Split(' ');
if (s.Length > 0)
{
sr.FirstName = s[0] + " " + s[1];
}
if (s[0].ToLower().Contains("mr."))
{
sr.Gen = 1;
}
else if (s[0].ToLower().Contains("mrs.") || s[0].ToLower().Contains("miss."))
{
sr.Gen = 2;
}
for (int i = 2; i < s.Length; i++)
{
sr.LastName = sr.LastName + " " + s[i];
}
if (sr.MobileNo.Length <= 0)
{
sr.MobileNo = "NA";
}
}
else
{
return(-2);
}
}
else
{
return(-1);
}
string query = "INSERT INTO [dbo].[Customer] " +
"( [Age], [FirstName], [LastName], " +
"[City], [MobileNo], [Gender], [NoOfBills], [TotalAmount]) " +
" VALUES (@Age,@fname , @lname, @city, @Mob, @gen, @nof, " +
"@TAMT)";
SqlCommand cmd = new SqlCommand(query, vDb.DBCon);
cmd.Parameters.AddWithValue("@fname", sr.FirstName);
cmd.Parameters.AddWithValue("@lname", sr.LastName);
cmd.Parameters.AddWithValue("@Mob", sr.MobileNo);
cmd.Parameters.AddWithValue("@Age", 0);
cmd.Parameters.AddWithValue("@gen", sr.Gen);
cmd.Parameters.AddWithValue("@city", sr.Address);
cmd.Parameters.AddWithValue("@nof", 0);
cmd.Parameters.AddWithValue("@TAMT", 0);
Logs.LogMe("Customer:Querry:=>" + query);
return(InsertQuerySql(cmd));
}
// Adding Col start from Here
private int AddColCustomer(Cell cell, ref Cust sr, int c)
{
Logs.LogMe("Customer:cell(" + cell.ColumnIndex + ")=" + cell.Text);
switch (cell.ColumnIndex)
{
case 6:
sr.FirstName = cell.Text;
c++;
break;
case 7:
sr.Address = cell.Text;
c++;
break;
case 8:
sr.MobileNo = 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);
}
public int ReadCustomer(string fname, int start, int end, ProgressBar pBar, string tablename)
{
DataTable dt = new DataTable(tablename);
int Row = 0;
int r = 0, c = 0;
Cust sr;
Logs.LogMe("Started Reading Excel Sheet for table: " + tablename);
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 Cust();
c = 0;
foreach (var cell in row.Cells)
{
if (cell != null)
{
c = AddColCustomer(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");
}
}
Row++;
Logs.LogMe("Row will be" + Row + "\tr=" + r);
}
else
{
Logs.LogMe("End Target Matched , Breaking out now");
break;
}
}
}
Logs.LogMe("End of line");
return(r);
}