public ActionResult Index(int?page, DateTime?filterCreateDateFrom, DateTime?filterCreateDateTo, string filterUserLogin = "")
{
int pageSize = 20;
int pageNumber = (page ?? 1);
List <Salary> salaries = new List <Salary>();
bool roleAdmin = UserEntities.IsInRole(User.Identity.Name, "admin");
DateTime last3Months = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day).AddMonths(-3);
ViewBag.Last3Months = last3Months.ToShortDateString();
ViewBag.TodayDate = DateTime.Now.ToShortDateString();
ViewBag.Users = new SelectList(ListsForFilters.Users, "Login", "Login");
if (roleAdmin)
{
if (filterUserLogin != "" && filterUserLogin != "Выберите пользователя")
{
salaries = SalaryEntities.GetSalaries(filterCreateDateFrom, filterCreateDateTo, filterUserLogin);
return(View(salaries.OrderByDescending(c => c.CreateDate).ToPagedList(pageNumber, pageSize)));
}
salaries = SalaryEntities.GetSalaries(filterCreateDateFrom, filterCreateDateTo);
}
else
{
salaries = SalaryEntities.GetSalaries(filterCreateDateFrom, filterCreateDateTo, User.Identity.Name);
}
return(View(salaries.OrderByDescending(c => c.CreateDate).ToPagedList(pageNumber, pageSize)));
}
private void simpleButton1_Click(object sender, EventArgs e)
{
Worksheet sale = spreadsheetControl1.Document.Worksheets[1];
Cell odau, ocuoi;
SearchOptions option = new SearchOptions();
option.SearchBy = SearchBy.Columns;
option.SearchIn = SearchIn.Values;
option.MatchEntireCellContents = true;
option.MatchCase = false;
IEnumerable <Cell> searchResult;
string file_ct = lblFilename.Text.Substring(0, lblFilename.Text.LastIndexOf(@"\")) + @"\BC_CT.xlsx";
string file_th = lblFilename.Text.Substring(0, lblFilename.Text.LastIndexOf(@"\")) + @"\BC_TH.xlsx";
//Chi tiết
SpreadsheetControl spreadct = new SpreadsheetControl();
spreadct.Document.LoadDocument(file_ct);
Worksheet bcct = spreadct.Document.Worksheets[0];
//Tổng hợp
SpreadsheetControl spreadth = new SpreadsheetControl();
spreadth.Document.LoadDocument(file_th);
Worksheet bcth = spreadth.Document.Worksheets[0];
CR_Flight_Crew tvt;
int rowct = 5, rowth = 8;
int songuoi, stt, stt_th, rate;
stt = 1;
stt_th = 1;
rate = 235000;
#region Tìm các cột
// Tìm cột FlightID
searchResult = sale.Search("FLight ID", option);
if (searchResult.Count() == 0)
{
MessageBox.Show("Không tìm thấy cột Flight ID trong vùng dữ liệu được chọn");
return;
}
int colfltid = searchResult.First().LeftColumnIndex;
// Dòng dữ liệu đầu tiên
int firstRow = searchResult.First().TopRowIndex + 1;
// Tìm cột KPT Revenue
searchResult = sale.Search("KPT \nRevenue", option);
if (searchResult.Count() == 0)
{
searchResult = sale.Search("KPT\n Revenue", option);
if (searchResult.Count() == 0)
{
searchResult = sale.Search("KPT\nRevenue", option);
if (searchResult.Count() == 0)
{
MessageBox.Show("Không tìm thấy cột KPT Revenue trong vùng dữ liệu được chọn");
return;
}
}
}
int colrevenue = searchResult.First().LeftColumnIndex;
// Tìm cột Discrepancy
searchResult = sale.Search("Discrepancy", option);
if (searchResult.Count() == 0)
{
MessageBox.Show("Không tìm thấy cột Discrepancy trong vùng dữ liệu được chọn");
return;
}
int coldiscrepancy = searchResult.First().LeftColumnIndex;
// Tìm cột Cashier Collected
searchResult = sale.Search("Cashier\n Collected", option);
if (searchResult.Count() == 0)
{
searchResult = sale.Search("Cashier \nCollected", option);
if (searchResult.Count() == 0)
{
searchResult = sale.Search("Cashier\nCollected", option);
if (searchResult.Count() == 0)
{
MessageBox.Show("Không tìm thấy cột Cashier Collected trong vùng dữ liệu được chọn");
return;
}
}
}
int colcashier = searchResult.First().LeftColumnIndex;
// Tìm cột Card Settlement
searchResult = sale.Search("Card \nSettlement", option);
if (searchResult.Count() == 0)
{
searchResult = sale.Search("Card\n Settlement", option);
if (searchResult.Count() == 0)
{
searchResult = sale.Search("Card\nSettlement", option);
if (searchResult.Count() == 0)
{
MessageBox.Show("Không tìm thấy cột Card Settlement trong vùng dữ liệu được chọn");
return;
}
}
}
int colcard = searchResult.First().LeftColumnIndex;
// Tìm cột Manv DS
searchResult = sale.Search("MNV_DS", option);
if (searchResult.Count() == 0)
{
MessageBox.Show("Không tìm thấy cột MNV_DS trong vùng dữ liệu được chọn");
return;
}
int colds = searchResult.First().LeftColumnIndex;
// Tìm cột Manv S
searchResult = sale.Search("MNV_S", option);
if (searchResult.Count() == 0)
{
MessageBox.Show("Không tìm thấy cột MNV_S trong vùng dữ liệu được chọn");
return;
}
int cols = searchResult.First().LeftColumnIndex;
#endregion
HREntities hr = new HREntities();
ERMSEntities1 cb = new ERMSEntities1();
SalaryEntities sal = new SalaryEntities();
int flid, i;
string manvds, manvs, thang, mbase;
double kptrevenue, cashiercollected, cardsettlement, discrepancy, doanhthu, hoahong;
i = firstRow;
HoSoGoc hs;
Crew_dutyfree crewdt;
bool flag20, tvtban = false;
thang = lblFilename.Text.Substring(0, lblFilename.Text.IndexOf("_kqxl"));
mbase = thang.Substring(0, thang.LastIndexOf(" "));
thang = thang.Substring(thang.LastIndexOf(" ") + 1) + "-2018";
mbase = mbase.Substring(mbase.LastIndexOf(" ") + 1);
try {
while (true)
{
if (sale.Cells[i, colfltid].Value.IsEmpty) // Không có Flight ID ==> coi như hết. Nghỉ
{
break;
}
flid = Convert.ToInt32(sale.Cells[i, colfltid].Value.ToString());
manvds = sale.Cells[i, colds].Value.ToString().PadLeft(4, '0');
if (sale.Cells[i, cols].Value.IsEmpty)
{
manvs = "";
}
else
{
manvs = sale.Cells[i, cols].Value.ToString().PadLeft(4, '0');
}
if (!sale.Cells[i, coldiscrepancy].Value.IsEmpty)
{
discrepancy = Convert.ToDouble(sale.Cells[i, coldiscrepancy].Value.ToString());
}
else
{
discrepancy = 0.0;
}
if (!sale.Cells[i, colrevenue].Value.IsEmpty)
{
kptrevenue = Convert.ToDouble(sale.Cells[i, colrevenue].Value.ToString());
}
else
{
kptrevenue = 0.0;
}
if (!sale.Cells[i, colcashier].Value.IsEmpty)
{
cashiercollected = Convert.ToDouble(sale.Cells[i, colcashier].Value.ToString());
}
else
{
cashiercollected = 0.0;
}
if (!sale.Cells[i, colcard].Value.IsEmpty)
{
cardsettlement = Convert.ToDouble(sale.Cells[i, colcard].Value.ToString());
}
else
{
cardsettlement = 0.0;
}
//Doanh thu
doanhthu = kptrevenue;
// Tìm thông tin chuyến bay và danh sách tổ tiếp viên
var sector = cb.CR_FlightInfo.Where(x => x.FlightID == flid).FirstOrDefault();
bcct.Cells[rowct, 0].Value = stt;
bcct.Cells[rowct, 1].Value = sector.Date.ToString("dd/MM/yyyy");
bcct.Cells[rowct, 2].Value = sector.Routing;
bcct.Cells[rowct, 3].Value = sector.FlightNo;
bcct.Cells[rowct, 4].Value = doanhthu;
bcct.Cells[rowct, 5].Value = 0.08 * doanhthu;
stt++;
//Ghi lên file tổng hợp
bcth.Cells[rowth, 0].Value = stt_th;
bcth.Cells[rowth, 1].Value = sector.Date.ToString("dd/MM/yyyy");
bcth.Cells[rowth, 2].Value = sector.FlightNo;
bcth.Cells[rowth, 3].Value = sector.Routing;
bcth.Cells[rowth, 4].Value = doanhthu;
bcth.Cells[rowth, 5].Value = 0.08 * doanhthu;
bcth.Cells[rowth, 7].Value = 0.02 * doanhthu;
bcth.Cells[rowth, 9].Value = 0.1 * doanhthu;
stt_th++;
rowth++;
tvtban = false;
//TVT CB
if (flid == 286956)
{
tvt = cb.CR_Flight_Crew.Where(x => x.FlightID == flid && x.ca == "1" && x.Job == "P").FirstOrDefault();
}
else
{
tvt = cb.CR_Flight_Crew.Where(x => x.FlightID == flid && x.IsDeleted == false && x.ca == "1" && x.Job == "P").FirstOrDefault();
}
//TV DS
hs = hr.HoSoGocs.Where(x => x.mans.Trim() == manvds).FirstOrDefault();
if (hs != null)
{
bcct.Cells[rowct, 6].Value = hs.tenkodau.ToUpper();
bcct.Cells[rowct, 7].Value = "'" + manvds;
if (tvt.CrewID == hs.mans.Trim())
{
tvtban = true;
bcct.Cells[rowct, 8].Value = "DS/P";
bcct.Cells[rowct, 9].Value = 50;
bcct.Cells[rowct, 10].Value = 0.5 * 0.08 * doanhthu;
hoahong = 0.5 * 0.08 * doanhthu;
if (manvs == "")
{
bcct.Cells[rowct, 9].Value = 80;
bcct.Cells[rowct, 10].Value = 0.8 * 0.08 * doanhthu;
hoahong = 0.8 * 0.08 * doanhthu;
}
}
else
{
bcct.Cells[rowct, 8].Value = "DS";
bcct.Cells[rowct, 9].Value = 40;
bcct.Cells[rowct, 10].Value = 0.4 * 0.08 * doanhthu;
hoahong = 0.4 * 0.08 * doanhthu;
if (manvs == "")
{
bcct.Cells[rowct, 9].Value = 70;
bcct.Cells[rowct, 10].Value = 0.7 * 0.08 * doanhthu;
hoahong = 0.7 * 0.08 * doanhthu;
}
}
var crewdtds = sal.Crew_dutyfree.Where(x => x.crewid == manvds && x.flightid == flid && x.month == thang).FirstOrDefault();
if (crewdtds == null)
{
Crew_dutyfree newdt = new Crew_dutyfree();
newdt.flightid = flid;
newdt.crewid = manvds;
newdt.month = thang;
newdt.commission = hoahong;
newdt.rate = rate;
newdt.mainbase = mbase;
newdt.note = "Insert*" + rowct.ToString();
sal.Crew_dutyfree.Add(newdt);
}
else
{
crewdtds.note = crewdtds.note.Trim() + "Update*" + rowct.ToString();;
crewdtds.commission = hoahong;
}
sal.SaveChanges();
rowct++;
}
//TV S
if (manvs != "")
{
hs = hr.HoSoGocs.Where(x => x.mans.Trim() == manvs).FirstOrDefault();
if (hs != null)
{
bcct.Cells[rowct, 6].Value = hs.tenkodau.ToUpper();
bcct.Cells[rowct, 7].Value = "'" + manvs;
if (tvt.CrewID == hs.mans.Trim())
{
tvtban = true;
bcct.Cells[rowct, 8].Value = "S/P";
bcct.Cells[rowct, 9].Value = 40;
bcct.Cells[rowct, 10].Value = 0.4 * 0.08 * doanhthu;
hoahong = 0.4 * 0.08 * doanhthu;
}
else
{
bcct.Cells[rowct, 8].Value = "S";
bcct.Cells[rowct, 9].Value = 30;
bcct.Cells[rowct, 10].Value = 0.3 * 0.08 * doanhthu;
hoahong = 0.3 * 0.08 * doanhthu;
}
var crewdts = sal.Crew_dutyfree.Where(x => x.crewid == manvs && x.flightid == flid && x.month == thang).FirstOrDefault();
if (crewdts == null)
{
Crew_dutyfree newdt = new Crew_dutyfree();
newdt.flightid = flid;
newdt.crewid = manvs;
newdt.month = thang;
newdt.rate = rate;
newdt.commission = hoahong;
newdt.mainbase = mbase;
newdt.note = "Insert*" + rowct.ToString();
sal.Crew_dutyfree.Add(newdt);
}
else
{
crewdts.note = crewdts.note + "Update*" + rowct.ToString();
crewdts.commission = hoahong;
}
sal.SaveChanges();
rowct++;
}
}
//TVT
if (!tvtban)
{
hs = hr.HoSoGocs.Where(x => x.mans.Trim() == tvt.CrewID).FirstOrDefault();
bcct.Cells[rowct, 6].Value = hs.tenkodau.ToUpper();
bcct.Cells[rowct, 7].Value = "'" + tvt.CrewID;
bcct.Cells[rowct, 8].Value = "P";
bcct.Cells[rowct, 9].Value = 10;
bcct.Cells[rowct, 10].Value = 0.1 * 0.08 * doanhthu;
hoahong = 0.1 * 0.08 * doanhthu;
crewdt = sal.Crew_dutyfree.Where(x => x.crewid == tvt.CrewID && x.flightid == flid && x.month == thang).FirstOrDefault();
if (crewdt == null)
{
Crew_dutyfree newdt = new Crew_dutyfree();
newdt.flightid = flid;
newdt.crewid = tvt.CrewID;
newdt.month = thang;
newdt.rate = rate;
newdt.commission = hoahong;
newdt.mainbase = mbase;
newdt.note = "Insert*" + rowct.ToString();
sal.Crew_dutyfree.Add(newdt);
}
else
{
crewdt.note = crewdt.note + "Update*" + rowct.ToString();
crewdt.commission = hoahong;
}
sal.SaveChanges();
rowct++;
}
//Danh sách tiếp viên còn lại
List <CR_Flight_Crew> dstv;
if (flid == 286956)
{
dstv = cb.CR_Flight_Crew.Where(x => x.FlightID == flid && x.CrewID != tvt.CrewID && x.CrewID != manvds && x.CrewID != manvs && x.CrewID != "1265" && x.CrewID != "3583" && x.CrewID != "4247" && x.CrewID != "4623" && x.CrewID != "5445" && x.CrewID != "5482").ToList();
}
else
{
dstv = cb.CR_Flight_Crew.Where(x => x.FlightID == flid && x.IsDeleted == false && x.CrewID != tvt.CrewID && x.CrewID != manvds && x.CrewID != manvs).ToList();
}
flag20 = false;
songuoi = dstv.Count;
foreach (var tv in dstv)
{
hs = hr.HoSoGocs.Where(x => x.mans.Trim() == tv.CrewID).FirstOrDefault();
bcct.Cells[rowct, 6].Value = hs.tenkodau.ToUpper();
bcct.Cells[rowct, 7].Value = "'" + tv.CrewID;
bcct.Cells[rowct, 8].Value = "CA";
if (!flag20)
{
bcct.Cells[rowct, 9].Value = 20;
flag20 = true;
odau = bcct.Cells[rowct, 9];
}
bcct.Cells[rowct, 10].Value = 0.2 * 0.08 * doanhthu / songuoi;
ocuoi = bcct.Cells[rowct, 9];
hoahong = 0.2 * 0.08 * doanhthu / songuoi;
var crewdtv = sal.Crew_dutyfree.Where(x => x.crewid == tv.CrewID && x.flightid == flid && x.month == thang).FirstOrDefault();
if (crewdtv == null)
{
Crew_dutyfree newdt = new Crew_dutyfree();
newdt.flightid = flid;
newdt.crewid = tv.CrewID;
newdt.month = thang;
newdt.rate = rate;
newdt.commission = hoahong;
newdt.mainbase = mbase;
newdt.note = "Insert*" + rowct.ToString();
sal.Crew_dutyfree.Add(newdt);
}
else
{
crewdtv.note = crewdtv.note + "Update*" + rowct.ToString();
crewdtv.commission = hoahong;
}
sal.SaveChanges();
rowct++;
}
//bcct.Range[odau, ocuoi].Merge();
i++;
}
;
//Lưu Kết quả CT
string filekq;
filekq = lblFilename.Text.Substring(0, lblFilename.Text.IndexOf(".")) + "-BC_CT.xlsx";
IWorkbook workbook = spreadct.Document;
using (FileStream stream = new FileStream(filekq, FileMode.Create, FileAccess.ReadWrite))
{
workbook.SaveDocument(stream, DocumentFormat.Xlsx);
}
//Lưu Kết quả TH
string filekqth;
filekqth = lblFilename.Text.Substring(0, lblFilename.Text.IndexOf(".")) + "-BC_TH.xlsx";
IWorkbook workbookth = spreadth.Document;
using (FileStream streamth = new FileStream(filekqth, FileMode.Create, FileAccess.ReadWrite))
{
workbookth.SaveDocument(streamth, DocumentFormat.Xlsx);
}
}
//catch (Exception ex)
//{
//}
finally
{
hr.Dispose();
cb.Dispose();
}
MessageBox.Show("Complete");
}
protected void Page_Load(object sender, EventArgs e)
{
InitHours();
InitMinutes();
InitddlBaseSellary();
InitHoliday();
if (!IsPostBack)
{
ddlBaseSellary.DataSource = _baseSallary;
ddlBaseSellary.DataBind();
}
_salaryEntities = new SalaryEntities();
}
