public DataSourceResult GetPage(DataSourceRequest request, string whereCondition, string SONumber)
{
List <SqlParameter> param = new List <SqlParameter>();
param.Add(new SqlParameter("@Page", request.Page));
param.Add(new SqlParameter("@PageSize", request.PageSize));
param.Add(new SqlParameter("@WhereCondition", whereCondition));
param.Add(new SqlParameter("@Sort", CustomModel.GetSortStringFormRequest(request)));
//param.Add(new SqlParameter("@SONumber", SONumber));
DataTable dt = new SqlHelper().ExecuteQuery("p_Select_DC_AD_SO_Detail_DynamicView", param);
var lst = new List <SODetail>();
foreach (DataRow row in dt.Rows)
{
var item = new SODetail();
item.ItemName = !row.IsNull("ItemName") ? row["ItemName"].ToString() : "";
item.ItemCode = !row.IsNull("ItemCode") ? row["ItemCode"].ToString() : "";
item.UnitName = !row.IsNull("UnitName") ? row["UnitName"].ToString() : "";
item.Qty = !row.IsNull("Qty") ? int.Parse(row["Qty"].ToString()) : 0;
item.Price = !row.IsNull("Price") ? double.Parse(row["Price"].ToString()) : 0;
item.Size = !row.IsNull("Size") ? row["Size"].ToString() : "";
item.Type = !row.IsNull("Type") ? row["Type"].ToString() : "";
item.ShapeTemplate = !row.IsNull("ShapeTemplate") ? row["ShapeTemplate"].ToString() : "";
item.UpdatedAt = !row.IsNull("UpdatedAt") ? DateTime.Parse(row["UpdatedAt"].ToString()) : DateTime.Parse("01/01/1900");
item.CreatedAt = !row.IsNull("CreatedAt") ? DateTime.Parse(row["CreatedAt"].ToString()) : DateTime.Parse("01/01/1900");
item.TotalAmt = !row.IsNull("TotalAmt") ? double.Parse(row["TotalAmt"].ToString()) : 0;
item.CreatedBy = !row.IsNull("CreatedBy") ? row["CreatedBy"].ToString() : "";
item.UpdatedBy = !row.IsNull("UpdatedBy") ? row["UpdatedBy"].ToString() : "";
item.UnitID = !row.IsNull("UnitID") ? row["UnitID"].ToString() : "";
item.SONumber = !row.IsNull("SONumber") ? row["SONumber"].ToString() : "";
lst.Add(item);
}
request.Filters = null;
DataSourceResult result = new DataSourceResult();
result.Data = lst;
result.Total = dt.Rows.Count > 0 ? Convert.ToInt32(dt.Rows[0]["RowCount"]) : 0;
return(result);
}
public DataSourceResult GetPage(DataSourceRequest request, string whereCondition, string SONumber)
{
List<SqlParameter> param = new List<SqlParameter>();
param.Add(new SqlParameter("@Page", request.Page));
param.Add(new SqlParameter("@PageSize", request.PageSize));
param.Add(new SqlParameter("@WhereCondition", whereCondition));
param.Add(new SqlParameter("@Sort", CustomModel.GetSortStringFormRequest(request)));
//param.Add(new SqlParameter("@SONumber", SONumber));
DataTable dt = new SqlHelper().ExecuteQuery("p_Select_DC_AD_SO_Detail_DynamicView", param);
var lst = new List<SODetail>();
foreach (DataRow row in dt.Rows)
{
var item = new SODetail();
item.ItemName = !row.IsNull("ItemName") ? row["ItemName"].ToString() : "";
item.ItemCode = !row.IsNull("ItemCode") ? row["ItemCode"].ToString() : "";
item.UnitName = !row.IsNull("UnitName") ? row["UnitName"].ToString() : "";
item.Qty = !row.IsNull("Qty") ? int.Parse(row["Qty"].ToString()) : 0;
item.Price = !row.IsNull("Price") ? double.Parse(row["Price"].ToString()) : 0;
item.Size = !row.IsNull("Size") ? row["Size"].ToString() : "";
item.Type = !row.IsNull("Type") ? row["Type"].ToString() : "";
item.ShapeTemplate = !row.IsNull("ShapeTemplate") ? row["ShapeTemplate"].ToString() : "";
item.UpdatedAt = !row.IsNull("UpdatedAt") ? DateTime.Parse(row["UpdatedAt"].ToString()) : DateTime.Parse("01/01/1900");
item.CreatedAt = !row.IsNull("CreatedAt") ? DateTime.Parse(row["CreatedAt"].ToString()) : DateTime.Parse("01/01/1900");
item.TotalAmt = !row.IsNull("TotalAmt") ? double.Parse(row["TotalAmt"].ToString()) : 0;
item.CreatedBy = !row.IsNull("CreatedBy") ? row["CreatedBy"].ToString() : "";
item.UpdatedBy = !row.IsNull("UpdatedBy") ? row["UpdatedBy"].ToString() : "";
item.UnitID = !row.IsNull("UnitID") ? row["UnitID"].ToString() : "";
item.SONumber = !row.IsNull("SONumber") ? row["SONumber"].ToString() : "";
lst.Add(item);
}
request.Filters = null;
DataSourceResult result = new DataSourceResult();
result.Data = lst;
result.Total = dt.Rows.Count > 0 ? Convert.ToInt32(dt.Rows[0]["RowCount"]) : 0;
return result;
}
public ActionResult ConfirmCreate()
{
var dbConn = new OrmliteConnection().openConn();
if (userAsset.ContainsKey("Insert") && userAsset["Insert"])
{
try
{
string SONumber = Request["SONumber"];
var header = new SOHeader();
var detail = new SODetail();
if (string.IsNullOrEmpty(SONumber))
{
string datetimeSO = DateTime.Now.ToString("yyMMdd");
var existSO = dbConn.SingleOrDefault<SOHeader>("SELECT id, SONumber FROM SOHeader ORDER BY Id DESC");
if (existSO != null)
{
var nextNo = Int32.Parse(existSO.SONumber.Substring(8, 5)) + 1;
SONumber = "SO" + datetimeSO + String.Format("{0:00000}", nextNo);
}
else
{
SONumber = "SO" + datetimeSO + "00001";
}
}
if (!string.IsNullOrEmpty(Request["SODate"]))
{
DateTime fromDateValue;
if (!DateTime.TryParseExact(Request["SODate"], "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out fromDateValue))
{
return Json(new { message = "Ngày tạo không đúng." });
}
}
if (dbConn.Select<DC_OCM_Merchant>(p => p.MerchantID == Request["MerchantID"]).Count() <= 0)
{
return Json(new { success = false, message = "Nhà cung cấp không tồn tại." });
}
if (dbConn.Select<SODetail>(p => p.SONumber == SONumber).Count() > 0)
{
if (!string.IsNullOrEmpty(Request["ItemCode"]))
{
if (dbConn.Select<Products>(p => p.Code == Request["ItemCode"]).Count() <= 0)
{
return Json(new { success = false, message = "Ấn phẩm không tồn tại." });
}
var itemcode = dbConn.Select<Products>(s => s.Code == Request["ItemCode"]).FirstOrDefault();
var itemunit = dbConn.Select<Products>(s => s.UnitID == itemcode.Unit).FirstOrDefault();
if (dbConn.Select<SODetail>(p => p.ItemCode == Request["ItemCode"] && p.SONumber == SONumber).Count() > 0)
{
var success = dbConn.Execute(@"UPDATE SODetail Set Qty = @Qty, TotalAmt =@TotalAmt ,UpdatedAt = @UpdatedAt, UpdatedBy = @UpdatedBy, Price = @Price
WHERE SONumber = '" + SONumber + "' AND ItemCode = '" + Request["ItemCode"] + "'", new
{
Qty = dbConn.Select<SODetail>(s => s.ItemCode == Request["ItemCode"] && s.SONumber == SONumber).Sum(s => s.Qty) + int.Parse(Request["Qty"]),
TotalAmt = itemcode != null ? itemcode.VATPrice * (dbConn.Select<SODetail>(s => s.ItemCode == Request["ItemCode"] && s.SONumber == SONumber).Sum(s => s.Qty) + int.Parse(Request["Qty"])) : 0,
Price = itemcode.VATPrice,
UpdatedBy = currentUser.UserID,
UpdatedAt = DateTime.Now,
}) == 1;
if (!success)
{
return Json(new { success = false, message = "Không thể lưu" });
}
}
else
{
detail.SONumber = SONumber;
detail.ItemCode = !string.IsNullOrEmpty(Request["ItemCode"]) ? Request["ItemCode"] : "";
detail.ItemName = !string.IsNullOrEmpty(itemcode.Name) ? itemcode.Name : "";
detail.Price = itemcode != null ? itemcode.VATPrice : 0;
detail.Qty = int.Parse(Request["Qty"]);
detail.TotalAmt = itemcode.VATPrice * int.Parse(Request["Qty"]);
detail.UnitID = !string.IsNullOrEmpty(itemunit.UnitID) ? itemunit.UnitID : "";
detail.UnitName = !string.IsNullOrEmpty(itemunit.UnitName) ? itemunit.UnitName : "";
detail.Note = "";
detail.Status = "";
detail.CreatedBy = currentUser.UserID;
detail.CreatedAt = DateTime.Now;
detail.UpdatedBy = "";
detail.UpdatedAt = DateTime.Parse("1900-01-01");
dbConn.Insert<SODetail>(detail);
}
}
}
else
{
if (string.IsNullOrEmpty(Request["ItemCode"]) || dbConn.Select<Products>(p => p.Code == Request["ItemCode"]).Count() <= 0)
{
return Json(new { success = false, message = "Ấn phẩm không tồn tại." });
}
var itemcode = dbConn.Select<Products>(s => s.Code == Request["ItemCode"]).FirstOrDefault();
var itemunit = dbConn.Select<Products>(s => s.UnitID == itemcode.Unit).FirstOrDefault();
detail.SONumber = SONumber;
detail.ItemCode = !string.IsNullOrEmpty(Request["ItemCode"]) ? Request["ItemCode"] : "";
detail.ItemName = !string.IsNullOrEmpty(itemcode.Name) ? itemcode.Name : "";
detail.Price = itemcode != null ? itemcode.VATPrice :0 ;
detail.Qty = int.Parse(Request["Qty"]);
detail.TotalAmt = itemcode.VATPrice * int.Parse(Request["Qty"]);
detail.UnitID = !string.IsNullOrEmpty(itemunit.UnitID) ? itemunit.UnitID : "";
detail.UnitName = !string.IsNullOrEmpty(itemunit.UnitName) ? itemunit.UnitName : "";
detail.Note = "";
detail.Status = "";
detail.CreatedBy = currentUser.UserID;
detail.CreatedAt = DateTime.Now;
detail.UpdatedBy = "";
detail.UpdatedAt = DateTime.Parse("1900-01-01");
dbConn.Insert<SODetail>(detail);
}
if (dbConn.Select<SOHeader>(p => p.SONumber == SONumber).Count() > 0)
{
var success = dbConn.Execute(@"UPDATE SOHeader Set TotalQty = @TotalQty, TotalAmt =@TotalAmt ,UpdatedAt = @UpdatedAt, UpdatedBy = @UpdatedBy,
MerchantID = @MerchantID, SODate = @SODate , WHLID =@WHLID, WHID =@WHID WHERE SONumber = '" + SONumber + "'", new
{
TotalQty = dbConn.Select<SODetail>(s =>s.SONumber == SONumber).Sum(s => s.Qty),
TotalAmt = dbConn.Select<SODetail>(s => s.SONumber == SONumber).Sum(s => s.TotalAmt),
SODate = !string.IsNullOrEmpty(Request["SODate"]) ? DateTime.Parse(DateTime.ParseExact(Request["SODate"], "dd/MM/yyyy", CultureInfo.InvariantCulture).ToString("yyyy-MM-dd")) : DateTime.Now,
WHID = !string.IsNullOrEmpty(Request["WHID"]) ? Request["WHID"] : "",
WHLID = !string.IsNullOrEmpty(Request["WHLID"]) ? Request["WHLID"] : "",
MerchantID = !string.IsNullOrEmpty(Request["MerchantID"]) ? Request["MerchantID"] : "",
UpdatedBy = currentUser.UserID,
UpdatedAt = DateTime.Now,
}) == 1;
if (!success)
{
return Json(new { success = false, message = "Không thể lưu" });
}
}
else
{
header.SONumber = SONumber;
header.SODate = !string.IsNullOrEmpty(Request["SODate"]) ? DateTime.Parse(DateTime.ParseExact(Request["SODate"], "dd/MM/yyyy", CultureInfo.InvariantCulture).ToString("yyyy-MM-dd")) : DateTime.Now;
header.VendorID = !string.IsNullOrEmpty(Request["MerchantID"]) ? Request["MerchantID"] : "";
header.Note = !string.IsNullOrEmpty(Request["Note"]) ? Request["Note"] : "";
header.TotalQty = dbConn.Select<SODetail>(s =>s.SONumber == SONumber).Sum(s => s.Qty);
header.WHID = Request["WHID"];
header.Status = "Mới";
header.WHLID = !string.IsNullOrEmpty(Request["WHLID"]) ? Request["WHLID"] : "";
header.TotalAmt = dbConn.Select<SODetail>(s => s.SONumber == SONumber).Sum(s => s.TotalAmt);
header.CreatedBy = currentUser.UserID;
header.CreatedAt = DateTime.Now;
header.UpdatedBy = "";
header.UpdatedAt = DateTime.Parse("1900-01-01");
dbConn.Insert<SOHeader>(header);
}
return Json(new { success = true, SONumber = SONumber });
}
catch (Exception e)
{
return Json(new { success = false, message = e.Message });
}
}
else
{
return Json(new { success = false, message = "Không có quyền tạo." });
}
}
public ActionResult CreateHeader(SOHeader item)
{
var dbConn = new OrmliteConnection().openConn();
if (userAsset.ContainsKey("Insert") && userAsset["Insert"])
{
try
{
string SONumber = Request["SONumber"];
var header = new SOHeader();
var detail = new SODetail();
if (string.IsNullOrEmpty(SONumber))
{
string datetimeSO = DateTime.Now.ToString("yyMMdd");
var existSO = dbConn.SingleOrDefault<SOHeader>("SELECT id, SONumber FROM SOHeader ORDER BY Id DESC");
if (existSO != null)
{
var nextNo = Int32.Parse(existSO.SONumber.Substring(8, 5)) + 1;
SONumber = "SO" + datetimeSO + String.Format("{0:00000}", nextNo);
}
else
{
SONumber = "SO" + datetimeSO + "00001";
}
}
if (string.IsNullOrEmpty(Request["VendorID"]))
{
return Json(new { message = "Nhà cung cấp không tồn tai." });
}
if (!string.IsNullOrEmpty(Request["SODate"]))
{
DateTime fromDateValue;
if (!DateTime.TryParseExact(Request["SODate"], "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out fromDateValue))
{
return Json(new { message = "Ngày tạo không đúng." });
}
}
header.SONumber = SONumber;
header.SODate = !string.IsNullOrEmpty(Request["SODate"]) ? DateTime.Parse(DateTime.ParseExact(Request["SODate"], "dd/MM/yyyy", CultureInfo.InvariantCulture).ToString("yyyy-MM-dd")) : DateTime.Now;
header.VendorID = !string.IsNullOrEmpty(Request["VendorID"]) ? Request["VendorID"] : "";
header.Note = !string.IsNullOrEmpty(Request["Note"]) ? Request["Note"] : "";
header.TotalQty = 0;
header.WHID = "";
header.Status = "Mới";
header.WHLID = "";
header.TotalAmt = 0;
header.CreatedBy = currentUser.UserID;
header.CreatedAt = DateTime.Now;
header.UpdatedBy = "";
header.UpdatedAt = DateTime.Now;
//header.UpdatedAt = DateTime.Parse("1900-01-01");
dbConn.Insert<SOHeader>(header);
dbConn.Close();
return Json(new { success = true, SONumber = SONumber });
}
catch (Exception e)
{
return Json(new { success = false, message = e.Message });
}
}
else
{
dbConn.Close();
return Json(new { success = false, message = "Không có quyền tạo." });
}
}
//public ActionResult ReadDetail([DataSourceRequest] DataSourceRequest request, string SONumber)
//{
// log4net.Config.XmlConfigurator.Configure();
// string whereCondition = "";
// if (request.Filters.Count > 0)
// {
// whereCondition = " AND " + new KendoApplyFilter().ApplyFilter(request.Filters[0]);
// }
// var data = new DC_AD_SO_Detail().GetPage(request, whereCondition, SONumber);
// return Json(data);
//}
public ActionResult Update([DataSourceRequest] DataSourceRequest request, [Bind(Prefix = "models")] IEnumerable<Products> list, string SONumber)
{
var dbConn = new OrmliteConnection().openConn();
try
{
if (list != null && ModelState.IsValid)
{
foreach (var item in list)
{
if (item.Qty > 0)
{
//string SONumber = Request["SONumber"];
var header = new SOHeader();
var detail = new SODetail();
if (dbConn.Select<SODetail>(s => s.SONumber == SONumber && s.ItemCode == item.Code).Count() > 0)
{
dbConn.Update<SODetail>(set: "Qty = '" + item.Qty + "', TotalAmt = '" + item.Qty * item.Price + "'", where: "SONumber = '" + SONumber + "'");
}
else
{
var data = new SODetail();
data.ItemName = item.Name;
data.ItemCode = item.Code;
data.Note = "";
data.Price = item.VATPrice;
data.Qty = item.Qty;
data.SONumber = SONumber;
data.UnitID = item.Unit;
data.UnitName = dbConn.Select<INUnit>(s => s.UnitID == item.Unit).FirstOrDefault().UnitName;
data.TotalAmt = item.Qty * item.VATPrice;
data.Status = "";
data.CreatedAt = DateTime.Now;
data.CreatedBy = currentUser.UserID;
//data.UpdatedAt = DateTime.Parse("1900-01-01");
data.UpdatedAt = DateTime.Now;
data.UpdatedBy = "";
dbConn.Insert<SODetail>(data);
}
dbConn.Update<SOHeader>(set: "TotalQty ='" + dbConn.Select<SODetail>(s => s.SONumber == SONumber).Sum(s => s.Qty) + "', TotalAmt = '" + +dbConn.Select<SODetail>(s => s.SONumber == SONumber).Sum(s => s.TotalAmt) + "'", where: "SONumber ='" + SONumber + "'");
}
else
{
dbConn.Delete<SOHeader>(s => s.SONumber == SONumber);
ModelState.AddModelError("error", "Đơn hàng được tạo khi số lượng > 0");
return Json(list.ToDataSourceResult(request, ModelState));
}
}
}
dbConn.Close();
}
catch (Exception e)
{
dbConn.Close();
ModelState.AddModelError("error", e.Message);
return Json(list.ToDataSourceResult(request, ModelState));
}
return Json(new { sussess = true });
}