public JsonResult GetList(string[] stringArray)
{
ViewBag.IsView = true;
DALExportSite dalExportSite = new DALExportSite();
BALExportSite model = new BALExportSite();
string filepath = Convert.ToString(Session["FilePathSite"]);
model.ItemList = dalExportSite.getExcelColumnName(filepath);
List<string> MatchListOfExcel = new List<string>();
model.ListtblColumnName = (List<string>)Session["DBColumnNameSite"];
List<tblColumn> list = new List<tblColumn>();
MatchListOfExcel.Add("ClientId");
MatchListOfExcel.Add("Client");
MatchListOfExcel.Add("BillingFrequencyId");
MatchListOfExcel.Add("Billing Frequency");
MatchListOfExcel.Add("ConsumerDepartmentId");
MatchListOfExcel.Add("Consumer Department");
foreach (var item in stringArray)
{
MatchListOfExcel.Add(item);
}
DataTable dt = dalExportSite.GetExcelTable(filepath, 0);
model.dtItem = dalExportSite.ExportView(dt, MatchListOfExcel.ToArray(), model.ListtblColumnName.ToArray(), filepath);
foreach (DataRow row in model.dtItem.Rows)
{
tblColumn tblColumnObj = new tblColumn();
tblColumnObj.col1 = Convert.ToString(row[0]);
tblColumnObj.col2 = Convert.ToString(row[1]);
tblColumnObj.col3 = Convert.ToString(row[2]);
tblColumnObj.col4 = Convert.ToString(row[3]);
tblColumnObj.col5 = Convert.ToString(row[4]);
tblColumnObj.col6 = Convert.ToString(row[5]);
tblColumnObj.col7 = Convert.ToString(row[6]);
tblColumnObj.col8 = Convert.ToString(row[7]);
tblColumnObj.col9 = Convert.ToString(row[8]);
tblColumnObj.col10 = Convert.ToString(row[9]);
tblColumnObj.col11 = Convert.ToString(row[10]);
tblColumnObj.col12 = Convert.ToString(row[11]);
tblColumnObj.col13 = Convert.ToString(row[12]);
tblColumnObj.col14 = Convert.ToString(row[13]);
tblColumnObj.col15 = Convert.ToString(row[14]);
tblColumnObj.col16 = Convert.ToString(row[15]);
tblColumnObj.col17 = Convert.ToString(row[16]);
tblColumnObj.col18 = Convert.ToString(row[17]);
tblColumnObj.col19 = Convert.ToString(row[18]);
tblColumnObj.col20 = Convert.ToString(row[19]);
tblColumnObj.col21 = Convert.ToString(row[20]);
tblColumnObj.col22 = Convert.ToString(row[21]);
tblColumnObj.col23 = Convert.ToString(row[22]);
tblColumnObj.col24 = Convert.ToString(row[23]);
list.Add(tblColumnObj);
}
return this.Json(list);
}
public string Save(string[] stringArray)
{
int i = 0;
try
{
DALExportSite dalExportSite = new DALExportSite();
BALExportSite model = new BALExportSite();
string filepath = Convert.ToString(Session["FilePathSite"]);
model.ItemList = dalExportSite.getExcelColumnName(filepath);
ViewBag.IsView = true;
List<string> MatchListOfExcel = new List<string>();
model.ListtblColumnName = (List<string>)Session["DBColumnNameSite"];
MatchListOfExcel.Add("ClientId");
MatchListOfExcel.Add("Client");
MatchListOfExcel.Add("BillingFrequencyId");
MatchListOfExcel.Add("Billing Frequency");
MatchListOfExcel.Add("ConsumerDepartmentId");
MatchListOfExcel.Add("Consumer Department");
foreach (var item in stringArray)
{
MatchListOfExcel.Add(item);
i++;
if (i == 18)
{
break;
}
}
DataTable dt = dalExportSite.GetExcelTable(filepath, 0);
if (!dt.Columns.Contains("ClientId") || !dt.Columns.Contains("Client") || !dt.Columns.Contains("BillingFrequencyId")
&& !dt.Columns.Contains("Billing Frequency") || !dt.Columns.Contains("ConsumerDepartmentId") || !dt.Columns.Contains("Consumer Department"))
{
dalExportSite.AddTemplateValue(dt, stringArray);
}
model.dtItem = dalExportSite.ExportView(dt, MatchListOfExcel.ToArray(), model.ListtblColumnName.ToArray(), filepath);
LoginSession loginsession = (LoginSession)Session["Login"];
dalExportSite.AddCreatedById(model.dtItem, loginsession.ClientID);
SqlParameterSite sqlParameterSite = new SqlParameterSite();
sqlParameterSite.ExecuteExportData(model.dtItem);
return "Import Data Sucessfully.";
}
catch (Exception ex)
{
return ex.Message;
}
}
public ActionResult ImportSite(HttpPostedFileBase file, BALExportSite model, string Command)
{
DALExportSite dalExportSite = new DALExportSite();
LoginSession loginsession = (LoginSession)Session["Login"];
ImportSite importSite = new ImportSite();
try
{
if (Command == "Export")
{
if (importSite.ImportExcel(Convert.ToString(model.ClientID), Convert.ToString(model.BillingFrequency), Convert.ToString(model.DepartmentID)))
{
string filepath = Path.Combine(Server.MapPath("~/Upload"), "SiteTemplate.xlsx");
var fileName = "SiteTemplate.xlsx";
var mimeType = "application/vnd.ms-excel";
return File(new FileStream(filepath, FileMode.Open), mimeType, fileName);
}
else
{
if (loginsession.ClientID != null)
{
ViewBag.Client = new SelectList(BAL.ClientModel.ClientDropDownList(loginsession.ClientID), "Value", "Text");
}
else
{
ViewBag.Client = new SelectList(BAL.ClientModel.ClientDropDownList(), "Value", "Text");
}
ViewBag.Notif = new SelectList(BAL.NotificationModel.NotificationDropDownList(3), "Value", "Text");
ViewBag.Department = new SelectList(BAL.DepartmentModels.DepartmentDropDownList(), "Value", "Text");
}
}
else if (Command == "Submit")
{
ViewBag.Isload = true;
string extension = Path.GetExtension(file.FileName);
fileName = string.Format(@"{0}." + extension, Guid.NewGuid());
var path = Path.Combine(Server.MapPath("~/ExportFileUpload"), fileName);
file.SaveAs(path);
//Get Excel Column
model.ItemList = dalExportSite.getExcelColumnName(path);
model.Istemplate = dalExportSite.IsTemplateUsed(model.ItemList);
//Get DB Table Column
model.ListtblColumnName = dalExportSite.GetTableColumnName();
Session["DBColumnNameSite"] = model.ListtblColumnName;
Session["FilePathSite"] = Path.Combine(Server.MapPath("~/ExportFileUpload"), fileName);
if (loginsession.ClientID != null)
{
ViewBag.Client = new SelectList(BAL.ClientModel.ClientDropDownList(loginsession.ClientID), "Value", "Text");
}
else
{ ViewBag.Client = new SelectList(BAL.ClientModel.ClientDropDownList(), "Value", "Text"); }
ViewBag.Notif = new SelectList(BAL.NotificationModel.NotificationDropDownList(3), "Value", "Text");
ViewBag.Department = new SelectList(BAL.DepartmentModels.DepartmentDropDownList(), "Value", "Text");
}
}
catch (Exception Ex)
{
ViewBag.Isload = false;
if (loginsession.ClientID != null)
{
ViewBag.Client = new SelectList(BAL.ClientModel.ClientDropDownList(loginsession.ClientID), "Value", "Text");
}
else
{ ViewBag.Client = new SelectList(BAL.ClientModel.ClientDropDownList(), "Value", "Text"); }
ViewBag.Notif = new SelectList(BAL.NotificationModel.NotificationDropDownList(3), "Value", "Text");
ViewBag.Department = new SelectList(BAL.DepartmentModels.DepartmentDropDownList(), "Value", "Text");
ViewBag.Message = Ex.Message;
}
return View(model);
}
public ActionResult ImportSite()
{
if (Session["Login"] != null)
{
BAL.Common.DeleteAllFromFolder("~/ExportFileUpload");
var model = new BALExportSite();
ViewBag.Isload = false;
ViewBag.IsView = false;
LoginSession loginsession = (LoginSession)Session["Login"];
if (loginsession.ClientID != null)
{
ViewBag.Client = new SelectList(BAL.ClientModel.ClientDropDownList(loginsession.ClientID), "Value", "Text");
}
else
{ ViewBag.Client = new SelectList(BAL.ClientModel.ClientDropDownList(), "Value", "Text"); }
ViewBag.Notif = new SelectList(BAL.NotificationModel.NotificationDropDownList(3), "Value", "Text");
ViewBag.Department = new SelectList(BAL.DepartmentModels.DepartmentDropDownList(), "Value", "Text");
return View(model);
}
else
{ return RedirectToAction("Index", "Home"); }
}