public async Task <ResponseModel> UploadServiceableAreaPins(ProviderFileModel provider, DataTable table)
{
var sp = new List <SqlParameter>();
var pararm = new SqlParameter("@FileName", ToDBNull(provider.FileName));
sp.Add(pararm);
pararm = new SqlParameter("@RefKey", ToDBNull(provider.RefKey));
sp.Add(pararm);
pararm = new SqlParameter("@DataTable", SqlDbType.Structured)
{
TypeName = "AreaPinCodes",
Value = table
};
sp.Add(pararm);
pararm = new SqlParameter("@User", provider.UserId);
sp.Add(pararm);
pararm = new SqlParameter("@SysFileName", ToDBNull(provider.SysFileName));
sp.Add(pararm);
pararm = new SqlParameter("@Type", ToDBNull(provider.type));
sp.Add(pararm);
var sql = "UploadAreaPinCode @FileName,@RefKey,@DataTable, @User,@SysFileName,@Type";
var res = await _context.Database.SqlQuery <ResponseModel>(sql, sp.ToArray()).SingleOrDefaultAsync();
if (res.ResponseCode == 0)
{
res.IsSuccess = true;
}
return(res);
}
public async Task <ActionResult> Import(ProviderFileModel provider)
{
provider.CompanyId = CurrentUser.CompanyId;
provider.UserId = CurrentUser.UserId;
if (provider.DataFile != null)
{
string FileName = SaveFile(provider.DataFile, "Locations");
var excelPath = Server.MapPath("~/Files/Locations/");
string conString = string.Empty;
string extension = Path.GetExtension(provider.DataFile.FileName);
switch (extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
conString = string.Format(conString, excelPath + FileName);
DataTable dtExcelData = new DataTable();
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
dtExcelData.Columns.AddRange(new DataColumn[6] {
new DataColumn("Country Name", typeof(string)),
new DataColumn("State Name", typeof(string)),
new DataColumn("District Name", typeof(string)),
new DataColumn("Pin Code", typeof(string)),
new DataColumn("Location Name", typeof(string)),
new DataColumn("Is Active", typeof(string))
});
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT [Country Name],[State Name], [District Name],[Pin Code],[Location Name] ,[IS Active] FROM [" + sheet1 + "] where [Country Name] is not null", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
}
try
{
provider.FileName = FileName;
var response = await _RepoUploadFile.UploadCityLocations(provider, dtExcelData);
if (!response.IsSuccess)
{
System.IO.File.Delete(excelPath);
}
TempData["response"] = response;
return(RedirectToAction("ManageCityLocation"));
}
catch (Exception ex)
{
if (System.IO.File.Exists(excelPath))
{
System.IO.File.Delete(excelPath);
}
return(RedirectToAction("ManageCityLocation"));
}
}
return(RedirectToAction("index"));
}
public async Task <ActionResult> ImportServiceableAreaPinCodes(ProviderFileModel provider)
{
provider.CompanyId = CurrentUser.CompanyId;
provider.UserId = CurrentUser.UserId;
if (provider.DataFile != null)
{
provider.SysFileName = SaveFile(provider.DataFile, "ServiceProviders");
var excelPath = Server.MapPath("~/Files/ServiceProviders/");
string conString = string.Empty;
string extension = Path.GetExtension(provider.DataFile.FileName);
provider.FileName = Path.GetFileName(provider.DataFile.FileName);
switch (extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
conString = string.Format(conString, excelPath + provider.SysFileName);
DataTable dtExcelData = new DataTable();
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
dtExcelData.Columns.AddRange(new DataColumn[2] {
new DataColumn("Pin Code", typeof(string)),
new DataColumn("Is Active", typeof(string))
});
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT [Pin Code],[IS Active] FROM [" + sheet1 + "] where [Pin Code] is not null", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
}
try
{
var response = await _RepoUploadFile.UploadServiceableAreaPins(provider, dtExcelData);
if (!response.IsSuccess)
{
System.IO.File.Delete(excelPath);
}
TempData["response"] = response;
if (provider.type == "All")
{
return(RedirectToAction("Edit", new { id = provider.RefKey, @tab = "tab-6" }));
}
else
{
return(RedirectToAction("ManageServiceableAreaPinCode", new { ServiceId = provider.RefKey }));
}
}
catch (Exception ex)
{
if (System.IO.File.Exists(excelPath))
{
System.IO.File.Delete(excelPath);
}
if (provider.type == "All")
{
return(RedirectToAction("Edit", new { id = provider.RefKey, @tab = "tab-6" }));
}
else
{
return(RedirectToAction("ManageServiceableAreaPinCode", new { ServiceId = provider.RefKey }));
}
}
}
return(RedirectToAction("index"));
}
public async Task <ActionResult> ImportProviders(ProviderFileModel provider)
{
provider.CompanyId = CurrentUser.CompanyId;
provider.UserId = CurrentUser.UserId;
if (provider.DataFile != null)
{
provider.FileName = SaveFile(provider.DataFile, "Providers");
string excelPath = Server.MapPath("~/Files/Providers/" + provider.FileName);
string conString = string.Empty;
string extension = Path.GetExtension(provider.DataFile.FileName);
switch (extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
conString = string.Format(conString, excelPath);
DataTable dtExcelData = new DataTable();
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
dtExcelData.Columns.AddRange(new DataColumn[25] {
new DataColumn("Service Provider Name", typeof(string)),
new DataColumn("Organization Name", typeof(string)),
new DataColumn("Organization Code", typeof(string)),
new DataColumn("Organization IEC Number", typeof(string)),
new DataColumn("Statutory Type", typeof(string)),
new DataColumn("Applicable Tax Type", typeof(string)),
new DataColumn("GST Category", typeof(string)),
new DataColumn("GST Number", typeof(string)),
new DataColumn("PAN Card Number", typeof(string)),
new DataColumn("IsServiceCenter", typeof(string)),
new DataColumn("Contact Name", typeof(string)),
new DataColumn("Contact Mobile", typeof(string)),
new DataColumn("Contact Email", typeof(string)),
new DataColumn("Contact PAN", typeof(string)),
new DataColumn("Contact Voter Id", typeof(string)),
new DataColumn("Contact Adhaar", typeof(string)),
new DataColumn("Address Type", typeof(string)),
new DataColumn("Country", typeof(string)),
new DataColumn("State", typeof(string)),
new DataColumn("City", typeof(string)),
new DataColumn("Address", typeof(string)),
new DataColumn("Locality", typeof(string)),
new DataColumn("Near By Location", typeof(string)),
new DataColumn("Pin Code", typeof(string)),
new DataColumn("IsUser", typeof(string))
});
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT [Service Provider Name]," +
"[Organization Name],[Organization Code],[Organization IEC Number],[Statutory Type]," +
"[Applicable Tax Type],[GST Category],[GST Number],[PAN Card Number],[IsServiceCenter] ," +
"[Contact Name],[Contact Mobile],[Contact Email],[Contact PAN],[Contact Voter Id],[Contact Adhaar], " +
" [Address Type],[Country],[State],[City],[Address],[Locality],[Near By Location], " +
" [Pin Code], [IsUser] FROM [" + sheet1 + "] where [Service Provider Name] is not null", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
}
//try
//{
var response = await _RepoUploadFile.UploadServiceProviders(provider, dtExcelData);
if (!response.IsSuccess)
{
System.IO.File.Delete(excelPath);
}
TempData["response"] = response;
return(RedirectToAction("index"));
//}
//catch (Exception ex)
//{
// if (System.IO.File.Exists(excelPath))
// System.IO.File.Delete(excelPath);
// return RedirectToAction("index");
//}
}
return(RedirectToAction("index"));
}
