public PlanHeader QueryHeaderById(long id)
{
//throw new NotImplementedException();
PlanHeader header = new PlanHeader();
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@ID",id)
};
SQLiteDataReader dataReader = dbHelper.ExecuteReader(TableActionSQL.QueryPlanHeaderById, parameters);
while (dataReader.Read())
{
//Trace.WriteLine(string.Format("ID:{0},NAME:{1}", dataReader.GetInt64(0), dataReader.GetString(1)), "SD");
header =
new PlanHeader()
{
Id = dataReader.GetInt32(0),
ProductId = dataReader.GetInt32(13),
Product = new Product()
{
Id = dataReader.GetInt32(13),
Name = dataReader.GetString(14),
Enabled = dataReader.GetBoolean(15),
CreatedBy = dataReader.GetString(16),
UpdatedBy = dataReader.GetString(17),
CreatedDateTime = dataReader.GetDateTime(18),
UpdatedDateTime = dataReader.GetDateTime(19)
},
OrderQuantity = dataReader.GetInt32(2),
DailyCapacityPerOne = dataReader.GetInt32(3),
AssignedCount = dataReader.GetInt32(4),
StartedDate = dataReader.GetDateTime(5),
PlannedEndDate = dataReader.GetDateTime(6),
ActualEndDate = dataReader.GetDateTime(7),
State = (PlanState)dataReader.GetInt32(8),
CreatedBy = dataReader.GetString(9),
UpdatedBy = dataReader.GetString(10),
CreatedDateTime = dataReader.GetDateTime(11),
UpdatedDateTime = dataReader.GetDateTime(12)
};
}
return header;
}
private void CurrrentChanged(PlanHeader p)
{
if (p != null)
{
switch (p.State)
{
case PlanState.New:
{
tsmiMarkLineCancel.Enabled = true;
tsmiMarkLineDone.Enabled = true;
tsmiMarkLineStart.Enabled = true;
tsmiRevertLine.Enabled = false;
break;
}
case PlanState.InProgress:
{
tsmiMarkLineCancel.Enabled = true;
tsmiMarkLineDone.Enabled = true;
tsmiMarkLineStart.Enabled = false;
tsmiRevertLine.Enabled = true;
break;
}
case PlanState.Cancel:
{
tsmiMarkLineCancel.Enabled = false;
tsmiMarkLineDone.Enabled = false;
tsmiMarkLineStart.Enabled = false;
tsmiRevertLine.Enabled = true;
break;
}
case PlanState.Done:
{
tsmiMarkLineCancel.Enabled = true;
tsmiMarkLineDone.Enabled = false;
tsmiMarkLineStart.Enabled = false;
tsmiRevertLine.Enabled = true;
break;
}
default:
{
tsmiMarkLineCancel.Enabled = false;
tsmiMarkLineDone.Enabled = false;
tsmiMarkLineStart.Enabled = false;
tsmiRevertLine.Enabled = false;
break;
}
}
}
}
public Plan(PlanHeader header, IList<PlanLine> lines)
{
Header = header;
Lines = lines;
}
public ActionResult Import(PlanHeader items)
{
var file = Request.Files["FileUpload"];
try
{
if (file == null || file.ContentLength == 0)
{
using (var dbConn = OrmliteConnection.openConn())
{
var id_head = 0;
var nam_ke_hoach = 0;
var check_head = dbConn.FirstOrDefault <PlanHeader>(s => s.don_vi_phu_trach == items.don_vi_phu_trach && s.nam_ke_hoach == items.nam_ke_hoach);
if (check_head == null)
{
items.ngay_tao = DateTime.Now;
items.nguoi_tao = currentUser.ma_nguoi_dung;
items.nguoi_cap_nhat = "";
items.ngay_cap_nhat = DateTime.Parse("1900-01-01");
items.nguoi_duyet = "";
items.ngay_duyet = DateTime.Parse("1900-01-01");
items.trang_thai = "MOI";
dbConn.Insert(items);
nam_ke_hoach = items.nam_ke_hoach;
id_head = (int)dbConn.GetLastInsertId();
}
else
{
return(Json(new { success = false, message = "Kế hoạch năm đã được tạo. Vui lòng cập nhật kế hoạch năm" }));
}
return(Json(new { success = true }));
}
}
else
{
var fileExtension = System.IO.Path.GetExtension(file.FileName);
if (fileExtension != ".xlsx" && fileExtension != ".xls")
{
return(Json(new { success = false, message = "File không không đúng định dạng excel *.xlsx,*.xls" }));
}
string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
string fileLocation = string.Format("{0}/{1}", Server.MapPath("~/ExcelImport"), "[" + currentUser.ma_nguoi_dung + "-" + datetime + file.FileName);
string errorFileLocation = string.Format("{0}/{1}", Server.MapPath("~/ExcelImport"), "[" + currentUser.ma_nguoi_dung + "-" + datetime + "-Error]" + file.FileName);
string linkerror = "[" + currentUser.ma_nguoi_dung + "-" + datetime + "-Error]" + file.FileName;
if (!System.IO.Directory.Exists(Server.MapPath("~/ExcelImport")))
{
System.IO.Directory.CreateDirectory(fileLocation);
}
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
file.SaveAs(fileLocation);
var rownumber = 2;
var total = 0;
var error = 0;
FileInfo fileInfo = new FileInfo(fileLocation);
var excelPkg = new ExcelPackage(fileInfo);
FileInfo template = new FileInfo(Server.MapPath(@"~\ExportExcelFile\Plan.xlsx"));
template.CopyTo(errorFileLocation);
FileInfo _fileInfo = new FileInfo(errorFileLocation);
var _excelPkg = new ExcelPackage(_fileInfo);
ExcelWorksheet oSheet = excelPkg.Workbook.Worksheets["Data"];
ExcelWorksheet eSheet = _excelPkg.Workbook.Worksheets["Data"];
int totalRows = oSheet.Dimension.End.Row;
using (var dbConn = OrmliteConnection.openConn())
{
//tao hearder
var id_head = 0;
var nam_ke_hoach = 0;
var check_head = dbConn.FirstOrDefault <PlanHeader>(s => s.don_vi_phu_trach == items.don_vi_phu_trach && s.nam_ke_hoach == items.nam_ke_hoach);
if (check_head == null)
{
items.ngay_tao = DateTime.Now;
items.nguoi_tao = currentUser.ma_nguoi_dung;
items.nguoi_cap_nhat = "";
items.ngay_cap_nhat = DateTime.Parse("1900-01-01");
items.nguoi_duyet = "";
items.ngay_duyet = DateTime.Parse("1900-01-01");
items.trang_thai = "MOI";
dbConn.Insert(items);
nam_ke_hoach = items.nam_ke_hoach;
id_head = (int)dbConn.GetLastInsertId();
}
else
{
return(Json(new { success = false, message = "Kế hoạch năm đã được tạo. Vui lòng cập nhật kế hoạch năm" }));
}
for (int i = 7; i <= totalRows; i++)
{
string idNhomSP = oSheet.Cells[i, 1].Value != null ? oSheet.Cells[i, 1].Value.ToString() : "";
if (idNhomSP != "")
{
continue;
}
string ma_san_pham = oSheet.Cells[i, 2].Value != null ? oSheet.Cells[i, 2].Value.ToString() : "";
string don_vi_phu_trach = oSheet.Cells[i, 3].Value != null ? oSheet.Cells[i, 3].Value.ToString().Split('/')[0] : "";
string don_vi_tinh = oSheet.Cells[i, 4].Value != null ? oSheet.Cells[i, 4].Value.ToString().Split('/')[0] : "";
string so_luong_du_kien = oSheet.Cells[i, 5].Value != null ? oSheet.Cells[i, 5].Value.ToString() : "";
string don_gia_du_kien = oSheet.Cells[i, 6].Value != null ? oSheet.Cells[i, 6].Value.ToString() : "";
string tong_tien_ke_hoach_nam = oSheet.Cells[i, 7].Value != null ? oSheet.Cells[i, 7].Value.ToString() : "";
string ke_hoach_nam_truoc = oSheet.Cells[i, 8].Value != null ? oSheet.Cells[i, 8].Value.ToString() : "";
string thuc_hien_nam_truoc = oSheet.Cells[i, 9].Value != null ? oSheet.Cells[i, 9].Value.ToString() : "";
string chech_lech = oSheet.Cells[i, 10].Value != null ? oSheet.Cells[i, 10].Value.ToString() : "";
string ghi_chu = oSheet.Cells[i, 11].Value != null ? oSheet.Cells[i, 11].Value.ToString() : "";
try
{
if (!string.IsNullOrEmpty(ma_san_pham))
{
ma_san_pham = ma_san_pham.Split('-')[0];
if (so_luong_du_kien == "")
{
continue;
}
}
else
{
continue;
}
var itemeexit = dbConn.FirstOrDefault <PlanDetail>(s => s.ma_san_pham == int.Parse(ma_san_pham) && s.nam_ke_hoach == id_head && s.don_vi_phu_trach == items.don_vi_phu_trach);
if (itemeexit != null)
{
itemeexit.ma_san_pham = int.Parse(ma_san_pham);
//itemeexit.ma_don_vi = int.Parse(don_vi_phu_trach);
itemeexit.don_vi_phu_trach = don_vi_phu_trach;
itemeexit.ma_don_vi_tinh = don_vi_tinh;
itemeexit.so_luong_du_kien = int.Parse(so_luong_du_kien);
itemeexit.don_gia_du_kien_vat = double.Parse(don_gia_du_kien);
itemeexit.total_tien_du_kien = double.Parse(tong_tien_ke_hoach_nam);
itemeexit.ke_hoach_nam_truoc = double.Parse(ke_hoach_nam_truoc);
itemeexit.thuc_hien_nam_truoc = double.Parse(thuc_hien_nam_truoc);
itemeexit.chech_lech = double.Parse(chech_lech);
itemeexit.ghi_chu = ghi_chu;
itemeexit.ngay_tao = DateTime.Now;
itemeexit.ngay_cap_nhat = DateTime.Parse("1900-01-01");
itemeexit.nguoi_tao = currentUser.ma_nguoi_dung;
itemeexit.nguoi_cap_nhat = "";
dbConn.Update <PlanDetail>(itemeexit);
}
else
{
var item = new PlanDetail();
item.ma_san_pham = int.Parse(ma_san_pham);
//item.ma_don_vi = items.ma_don_vi;
item.don_vi_phu_trach = don_vi_phu_trach;
item.ma_don_vi_tinh = don_vi_tinh;
item.nam_ke_hoach = id_head;
item.so_luong_du_kien = int.Parse(so_luong_du_kien);
item.don_gia_du_kien_vat = double.Parse(don_gia_du_kien);
item.total_tien_du_kien = double.Parse(tong_tien_ke_hoach_nam);
item.ke_hoach_nam_truoc = double.Parse(ke_hoach_nam_truoc);
item.thuc_hien_nam_truoc = double.Parse(thuc_hien_nam_truoc);
item.chech_lech = double.Parse(chech_lech);
item.trang_thai = "MOi";
item.ghi_chu = ghi_chu;
item.nguoi_tao = currentUser.ma_nguoi_dung;
item.nguoi_cap_nhat = "";
dbConn.Insert <PlanDetail>(item);
total++;
rownumber++;
}
total++;
rownumber++;
}
catch (Exception e)
{
eSheet.Cells[rownumber, 2].Value = ma_san_pham;
eSheet.Cells[rownumber, 3].Value = don_vi_phu_trach;
eSheet.Cells[rownumber, 4].Value = don_vi_tinh;
eSheet.Cells[rownumber, 5].Value = so_luong_du_kien;
eSheet.Cells[rownumber, 6].Value = don_gia_du_kien;
eSheet.Cells[rownumber, 7].Value = tong_tien_ke_hoach_nam;
eSheet.Cells[rownumber, 8].Value = ke_hoach_nam_truoc;
eSheet.Cells[rownumber, 9].Value = thuc_hien_nam_truoc;
eSheet.Cells[rownumber, 10].Value = chech_lech;
eSheet.Cells[rownumber, 11].Value = ghi_chu;
eSheet.Cells[rownumber, 12].Value = e.Message;
rownumber++;
error++;
continue;
}
}
}
_excelPkg.Save();
return(Json(new { success = true, total = total, totalError = error, link = linkerror }));
}
}
catch (Exception ex)
{
return(Json(new { success = false, message = ex.Message }));
}
}
public Plan()
{
Header = new PlanHeader();
Lines = new List<PlanLine>();
}
