public static void InsertIntoTblDtl(string JobCardNo, SessionDataBLL obj)
{
using (var context = new BSSDBEntities())
{
var JobCardId = context.TblJobCardHdrs.Where(x => x.JobCardNo == JobCardNo && x.DealerID == obj.DealerID).Select(x => x.JobCardId).FirstOrDefault();
var Check = context.TblJobCardDtls.Where(x => x.JobCardId == JobCardId).FirstOrDefault();
if (Check == null)
{
TblJobCardDtl tb = new TblJobCardDtl
{
JobCardId = JobCardId,
StatusID = 17,
IsDeleted = false,
CreatedBy = obj.UserId,
CreationDate = DateTime.Now,
//ModifiedBy=obj.UserId,
//ModifiedDate=DateTime.Now
};
context.Entry(tb).State = EntityState.Added;
context.SaveChanges();
}
}
}
public static SessionDataBLL CheckLogin(LoginBLL obj)
{
using (var dbContext = new BSSDBEntities())
{
var Check = dbContext.TblUsers.Where(x => x.UserName == obj.UserName && x.Password == obj.Password && x.IsActive == true).FirstOrDefault();
if (Check != null)
{
var DesignationID = dbContext.TblEmployees.Where(x => x.EmpId == Check.EmpId).Select(x => x.DesignationID).FirstOrDefault();
var Details = dbContext.TblEmployees.Where(x => x.EmpId == Check.EmpId).FirstOrDefault();
SessionDataBLL SessionData = new SessionDataBLL
{
UserId = Check.UserID,
UserName = Check.UserName,
AccountId = Details.AccountID.Value,
EmployeeId = Check.EmpId.Value,
DesignationID = Convert.ToInt64(DesignationID),
DealerID = Details.DealerID.Value,
};
return(SessionData);
}
else
{
return(null);
}
}
}
public static List <SpGetStatusReadyDeliver_Result> GetStatusName(SessionDataBLL Obj)
{
List <SpGetStatusReadyDeliver_Result> spData = null;
using (var dbContext = new BSSDBEntities())
{
var ReqData = dbContext.SpGetStatusReadyDeliver(Obj.DealerID, Obj.AccountId);
spData = ReqData.ToList();
}
return(spData);
}
public static List <SpGetStatus_Result> GetData(SessionDataBLL Obj)
{
List <SpGetStatus_Result> StatusResult = null;
using (var dbContext = new BSSDBEntities())
{
var ReqData = dbContext.SpGetStatus(Obj.DealerID, Obj.AccountId);
StatusResult = ReqData.ToList();
}
return(StatusResult);
}
public static List <EmailListModel> GetData(SessionDataBLL Obj)
{
using (var DbContext = new BSSDBEntities())
{
var ReqData = DbContext.SP_GetEmailList(Obj.DealerID, Obj.AccountId).ToList();
List <EmailListModel> EmailList = ReqData.Select(x => new EmailListModel {
Id = x.Id,
PersonName = x.PersonName,
Email_ID = x.Email_ID,
IsDeleted = x.IsDeleted,
AccountId = x.AccountId,
DealerId = x.DealerId,
}).ToList();
return(EmailList);
}
}
public static string GenerateHtml(SessionDataBLL Obj)
{
using (var dbContext = new BSSDBEntities())
{
var ReqData = dbContext.SP_StatusWiseCompleteReport(Obj.DealerID, Obj.AccountId).ToList();
string Html = "<!DOCTYPE html><html><head><style>table, th, td {border: 1px solid black;border-collapse: collapse;}</style></head><body><br /> <label>Please find attachment for group wise report. Status wise report is Shown below :-</label><br /><br /><table><thead><tr><th>Status Name:</th><th>No. Of JobCards</th></tr></thead>";
Html = Html + "<tbody>";
foreach (var data in ReqData)
{
Html = Html + "<tr><td>" + data.StatusName + "</td><td>" + data.NoOfJobCards + "</td></tr>";
}
Html = Html + "</tbody></table><br /><br />";
Html = Html + "<label>Regards,</label><br /><label>BodyShop Application</label><br /><br />";
Html = Html + "<label>This is an auto generated mail, Do not reply</label><br /></body></html>";
return(Html);
}
}
public static List <InsuranceCmpList> GetInsuranceCmpList(SessionDataBLL Obj)
{
using (var DbContext = new BSSDBEntities())
{
List <InsuranceCmpList> InsuranceCmpList = null;
var ReqData = DbContext.SP_GetInsuranceCmpList(Obj.DealerID, Obj.AccountId).ToList();
InsuranceCmpList = ReqData.Select(x => new InsuranceCmpList
{
InsuranceCompanyId = x.InsuranceCompanyId,
CompanyName = x.CompanyName,
CompanyCode = x.CompanyCode,
IsDeleted = x.IsDeleted,
AccountId = x.AccountId,
DealerId = x.DealerId
}).ToList();
return(InsuranceCmpList);
}
}
public static DashBoardBLL GetData(SessionDataBLL Obj)
{
using (var dbContext = new BSSDBEntities())
{
var Total = dbContext.SpTotalCount(Obj.DealerID, Obj.AccountId).FirstOrDefault();
var Delivered = dbContext.SpDeliveredCount(Obj.DealerID, Obj.AccountId).FirstOrDefault();
var Pending = dbContext.SpPendingCount(Obj.DealerID, Obj.AccountId).FirstOrDefault();
var Ready = dbContext.SpReadyCount(Obj.DealerID, Obj.AccountId).FirstOrDefault();
DashBoardBLL obj = new DashBoardBLL()
{
Total = Convert.ToInt64(Total),
Delivered = Convert.ToInt64(Delivered),
Pending = Convert.ToInt64(Pending),
Ready = Convert.ToInt64(Ready)
};
return(obj);
}
}
public static void InsertIntoTblContractor(DataTable info, SessionDataBLL obj)
{
using (var context = new BSSDBEntities())
{
foreach (DataRow Dr in info.Rows)
{
string ContractorName = Dr["ContractorName"].ToString().Trim();
string ContractorCode = Convert.ToString(Dr["ContractorCode"]);
if (ContractorCode != null && ContractorCode != string.Empty && ContractorName != null && ContractorName != string.Empty)
{
string ContractorAddress = Convert.ToString(Dr["ContractorAddress"]);
string PhoneNo = Convert.ToString(Dr["PhoneNo"]);
var Check = context.tblContractorMasters.Where(x => x.ContractorCode == ContractorCode && x.DealerID == obj.DealerID).FirstOrDefault();
if (Check == null)
{
tblContractorMaster dt = new tblContractorMaster
{
ContractorCode = ContractorCode,
ContractorName = ContractorName,
IsDeleted = false,
Address = ContractorAddress,
AccountID = obj.AccountId,
DealerID = obj.DealerID,
Phone = PhoneNo,
CreatedBy = Convert.ToString(obj.UserId),
CreationDate = DateTime.Now
};
context.Entry(dt).State = EntityState.Added;
context.SaveChanges();
}
else
{
continue;
}
}
}
}
}
public static List <CompleteDetailsModel> GetData(SessionDataBLL Obj)
{
//List<SpGetCompleteDetails_Result> ListData = null;
using (var dbContext = new BSSDBEntities())
{
var ReqData = dbContext.SpGetCompleteDetails(Obj.DealerID, Obj.AccountId);
List <CompleteDetailsModel> ListData = ReqData.Select(x => new CompleteDetailsModel()
{
JobCardId = x.JobCardId,
JobCardNo = x.JobCardNo,
CustomerCategory = x.CustomerCategory,
CustomerName = x.CustomerName,
DateAndTime = x.DateAndTime,
Model = x.Model,
PhoneNo = x.PhoneNo,
PromisedDate = x.PromisedDate,
SA = x.SA,
Service = x.Service,
RegistrationNo = x.RegistrationNo,
PSFStatus = x.PSFStatus,
Technician = x.Technician,
DealerID = x.DealerID,
CreatedBy = x.CreatedBy,
Id = x.Id,
No_Of_Panel = x.No_Of_Panel,
PanelReplaced = x.PanelReplaced,
Status = x.Status,
StatusID = x.StatusID,
ModifiedDate_Formatted = x.ModifiedDate_Formatte,
Contractor_Id = x.Contractor_Id,
ContractorName = x.ContractorName,
InsCompId = x.InsuranceCompanyId,
PaymentMode = x.PaymentMode,
Glass = x.NoOfGlass,
PhotoUrl = x.PhotoUrl
}).ToList();
return(ListData);
}
}
public static List <ContractorModel> GetContractor(SessionDataBLL Obj)
{
using (var dbContext = new BSSDBEntities())
{
var ReqData = dbContext.tblContractorMasters.Where(x => x.DealerID == Obj.DealerID && x.AccountID == Obj.AccountId && x.IsDeleted == false).ToList();
List <ContractorModel> ContractorData = ReqData.Select(x => new ContractorModel()
{
Contractor_Id = x.Contractor_Id,
ContractorCode = x.ContractorCode,
ContractorName = x.ContractorName,
IsDeleted = x.IsDeleted,
Address = x.Address,
Phone = x.Phone,
CreatedBy = x.CreatedBy,
CreationDate = x.CreationDate,
ModifiedBy = x.ModifiedBy
}).ToList();
return(ContractorData);
}
}
public DashBoardBLL GetData(SessionDataBLL Obj)
{
return(DashBoardDAL.GetData(Obj));
}
public List <InsuranceCmpList> GetInsuranceCmpList(SessionDataBLL Obj)
{
return(InsuranceCmpDAL.GetInsuranceCmpList(Obj));
}
public List <SpGetStatus_Result> GetStatusName(SessionDataBLL Obj)
{
return(StatusMasterDAL.GetData(Obj));
}
public List <ContractorModel> GetContractor(SessionDataBLL Obj)
{
return(ContractorDAL.GetContractor(Obj));
}
public List <EmailListModel> GetData(SessionDataBLL Obj)
{
return(EmailMasterDAL.GetData(Obj));
}
public async Task <HttpResponseMessage> PostData()
{
string location = "";
string fileName = "";
int k = 0;
//long UserId = 0;
// Check if the request contains multipart/form-data.
string strUniqueId = System.Guid.NewGuid().ToString();
if (!Request.Content.IsMimeMultipartContent())
{
throw new HttpResponseException(HttpStatusCode.UnsupportedMediaType);
}
var root = System.Web.HttpContext.Current.Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["ExcelPath"]);
Directory.CreateDirectory(root);
var provider = new MultipartFormDataStreamProvider(root);
var result = await Request.Content.ReadAsMultipartAsync(provider);
DataTable tbl = new DataTable();
try
{
for (int i = 0; i < result.FileData.Count; i++)
{
fileName = result.FileData[i].Headers.ContentDisposition.FileName;
string fileserial = result.FileData[i].Headers.ContentDisposition.Name.Trim('"');
if (fileName.StartsWith("\"") && fileName.EndsWith("\""))
{
fileName = fileName.Trim('"');
}
if (fileName.Contains(@"/") || fileName.Contains(@"\"))
{
fileName = Path.GetFileName(fileName);
Match regex = Regex.Match(root, @"(.+) \((\d+)\)\.\w+");
if (regex.Success)
{
fileName = regex.Groups[1].Value;
}
}
//Storing file to temporary location in project
try
{
string fileType = Path.GetExtension(fileName);
string filename = DateTime.Now.Ticks.ToString(); //Path.GetFileNameWithoutExtension(fileName);
string ExlName = filename + fileType;
string str;
str = filename.Substring(filename.Length - 1, 1);
string ImageWithPath = string.Format("{0}{1}", filename + str, fileType);
location = Path.Combine(root, ImageWithPath);
File.Move(result.FileData[i].LocalFileName, Path.Combine(root, ImageWithPath));
}
catch (Exception e)
{
return(Request.CreateResponse(HttpStatusCode.OK, "2"));
}
FileInfo f = new FileInfo(location);
Stream s = f.OpenRead();
bool hasHeader = true;
//open xlsx file
var excel = new ExcelPackage(s);
var workbook = excel.Workbook;
if (i == 0)
{
var sheet = excel.Workbook.Worksheets[1];
foreach (var firstRowCell in sheet.Cells[1, 1, 1, sheet.Dimension.End.Column])
{
tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
}
// headers of excel file
// headers of excel file
if (tbl.Columns.Count == 15)
{
if (tbl.Columns[1].ColumnName != "Job Card No." || tbl.Columns[2].ColumnName != "Date & Time" || tbl.Columns[3].ColumnName != "Customer Name" || tbl.Columns[4].ColumnName != "Phone & Mobile No." || tbl.Columns[5].ColumnName != "Customer Catg." || tbl.Columns[6].ColumnName != "PSF Status" || tbl.Columns[7].ColumnName != "Registration")
{
return(Request.CreateResponse(HttpStatusCode.OK, "3"));
}
if (tbl.Columns[8].ColumnName != "Model" || tbl.Columns[9].ColumnName != "S.A" || tbl.Columns[10].ColumnName != "Technician" || tbl.Columns[11].ColumnName != "Service" || tbl.Columns[12].ColumnName != "Promised Dt." || tbl.Columns[13].ColumnName != "Rev. Promised Dt." || tbl.Columns[14].ColumnName != "Ready Date & Time")
{
return(Request.CreateResponse(HttpStatusCode.OK, "3"));
}
}
else
{
return(Request.CreateResponse(HttpStatusCode.OK, "2"));
}
var startRow = hasHeader ? 2 : 1;
for (int rowNum = startRow; rowNum <= sheet.Dimension.End.Row; rowNum++)
{
var wsRow = sheet.Cells[rowNum, 1, rowNum, sheet.Dimension.End.Column];
DataRow row = tbl.Rows.Add();
//for (k = 0; k < 14;k++)
foreach (var cell in wsRow)
{
if (cell.Start.Column < 14)
{
row[cell.Start.Column - 1] = cell.Text;
}
else
{
continue;
}
}
}
}
}
var model = result.FormData["data"];
//var Data = JsonConvert.DeserializeObject<long>(model);
SessionDataBLL obj = new SessionDataBLL();
obj = JsonConvert.DeserializeObject <SessionDataBLL>(model);
// function calling for insertion of excel Data into DataBase table
ExcelUploadDAL.InsertData(tbl, obj);
}
catch (Exception e)
{
return(Request.CreateResponse(HttpStatusCode.Accepted, e.Message));
}
return(Request.CreateResponse(HttpStatusCode.Accepted, "0"));
}
public List <CompleteDetailsModel> GetData(SessionDataBLL Obj)
{
return(GridViewDAL.GetData(Obj));
}
public static void InsertData(DataTable info, SessionDataBLL obj)
{
using (var context = new BSSDBEntities())
{
var dt1 = context.TblJobCardHdrs.ToList();
foreach (DataRow Dr in info.Rows)
{
string JobCardNo = Dr["Job Card No."].ToString().Trim();
if (JobCardNo != null && JobCardNo != string.Empty)
{
//LogService("JobCardNo: " + JobCardNo);
//LogService("************************: " + DateTime.Now.ToString());
string DateAndTime1 = Convert.ToString(Dr["Date & Time"]);
//LogService("DateAndTime1: " + DateAndTime1);
//LogService("************************: " + DateTime.Now.ToString());
DateTime DateAndTime = Convert.ToDateTime(DateAndTime1);
//LogService("DateAndTime: " + DateAndTime);
//LogService("************************: " + DateTime.Now.ToString());
//DateTime DateAndTime = Convert.ToDateTime(Dr["Date & Time"]);
string CustomerName = Convert.ToString(Dr["Customer Name"]);
long PhoneNo = Convert.ToInt64(Dr["Phone & Mobile No."]);
string CustomerCategory = Convert.ToString(Dr["Customer Catg."]);
string PSFStatus = Convert.ToString(Dr["PSF Status"]);
string RegistrationNo = Convert.ToString(Dr["Registration"]);
string Model = Convert.ToString(Dr["Model"]);
string SA = Convert.ToString(Dr["S.A"]);
string Technician = Convert.ToString(Dr["Technician"]);
string Service = Convert.ToString(Dr["Service"]);
string PromisedDate = Convert.ToString(Dr["Promised Dt."]);
//LogService("PromisedDate: " + PromisedDate);
//LogService("************************: " + DateTime.Now.ToString());
string RevPromisedDate = Convert.ToString(Dr["Rev. Promised Dt."]);// Dr["Rev. Promised Dt."].ToString();
//LogService("RevPromisedDate: " + RevPromisedDate);
//LogService("************************: " + DateTime.Now.ToString());
//DateTime RevDATE = Convert.ToDateTime(Dr["Rev. Promised Dt."]);
string ReadyDateTime = Convert.ToString(Dr["Ready Date & Time"]);
//LogService("ReadyDateTime: " + ReadyDateTime);
//LogService("************************: " + DateTime.Now.ToString());
var Check = context.TblJobCardHdrs.Where(x => x.JobCardNo == JobCardNo && x.DealerID == obj.DealerID).FirstOrDefault();
if (Check == null)
{
TblJobCardHdr dt = new TblJobCardHdr
{
JobCardNo = JobCardNo,
DateAndTime = DateAndTime,
CustomerName = CustomerName,
PhoneNo = PhoneNo,
CustomerCategory = CustomerCategory,
PSFStatus = PSFStatus,
RegistrationNo = RegistrationNo,
Model = Model,
SA = SA,
Technician = Technician,
Service = Service,
PromisedDate = PromisedDate,
RevPromisedDate = RevPromisedDate,
ReadyDateTime = ReadyDateTime,
AccountID = obj.AccountId,
DealerID = obj.DealerID,
CreatedBy = obj.UserId,
CreationDate = DateTime.Now
};
context.Entry(dt).State = EntityState.Added;
context.SaveChanges();
InsertIntoTblDtl(JobCardNo, obj);
}
else
{
continue;
}
}
}
}
}
public static List <ContractorWiseReportModel> GetContractorWiseReport(ContractorModel obj)
{
using (var dbContext = new BSSDBEntities())
{
List <ContractorWiseReportModel> ReportResult = new List <ContractorWiseReportModel>();
List <ContractorWiseReportModel> FinalResult = new List <ContractorWiseReportModel>();
var ReqData = dbContext.SP_ContractorWiseReport(obj.DealerID, obj.AccountID).ToList();
SessionDataBLL DataObj = new SessionDataBLL
{
DealerID = obj.DealerID,
AccountId = obj.AccountID
};
var StatusList = StatusMasterDAL.GetData(DataObj);
foreach (var item in ReqData)
{
if (ReportResult.Where(x => x.Contractor_Id == item.Contractor_Id).FirstOrDefault() == null)
{
ReportResult.Add(new ContractorWiseReportModel
{
Contractor_Id = item.Contractor_Id,
ContractorName = item.ContractorName,
StatusList = ReqData.Where(x => x.Contractor_Id == item.Contractor_Id).Select(x => new StatusList
{
StatusCode = x.StatusCode,
StatusID = x.StatusID,
StatusName = x.StatusName,
JobCards = x.JobCards
}).ToList()
});
}
}
foreach (var result in ReportResult)
{
int Total = 0;
List <StatusList> SL = new List <StatusList>();
foreach (var pt in StatusList)
{
var Check = result.StatusList.Where(x => x.StatusID == pt.StatusID).FirstOrDefault();
int? JobCards = 0;
string StatusName = pt.StatusName;
string StatusCode = pt.StatusCode;
long? StatusID = 0;
if (Check != null)
{
SL.Add(new BODYSHPBLL.ImplBLL.StatusList
{
JobCards = Check.JobCards,
StatusName = Check.StatusName,
StatusCode = Check.StatusCode,
StatusID = Check.StatusID
});
}
else
{
SL.Add(new BODYSHPBLL.ImplBLL.StatusList
{
JobCards = JobCards,
StatusName = StatusName,
StatusCode = StatusCode,
StatusID = StatusID
});
}
}
FinalResult.Add(new ContractorWiseReportModel
{
ContractorName = result.ContractorName,
Contractor_Id = result.Contractor_Id,
Total = Total,
StatusList = SL
});
}
List <StatusList> VerticalCount = new List <StatusList>();
int RowTotal = 0;
for (int i = 0; i < FinalResult[0].StatusList.Count; i++)
{
int ColumnTotal = 0;
for (int j = 0; j < FinalResult.Count; j++)
{
ColumnTotal = ColumnTotal + FinalResult[j].StatusList[i].JobCards.Value;
}
VerticalCount.Add(new StatusList
{
JobCards = ColumnTotal
});
}
FinalResult.Add(new ContractorWiseReportModel
{
ContractorName = "Total",
Contractor_Id = 0,
Total = 0,
StatusList = VerticalCount
});
foreach (var result in FinalResult)
{
for (int i = 0; i < result.StatusList.Count; i++)
{
result.Total = result.Total + result.StatusList[i].JobCards;
}
}
return(FinalResult);
}
}
