public ActionResult GetOcHeadReturnList(Order_Head model)
{
var list = SalesOrderDAL.GetOcHeadReturnList(model);
//var result = new { rows = list };
//return Json(result, JsonRequestBehavior.AllowGet);
return(Json(list, JsonRequestBehavior.AllowGet));
}
public static Order_Head GetOcHeadByID(string OcID)
{
string strSql =
@"SELECT OcID,Ver,Convert(Varchar(10),OrderDate,111) AS OrderDate,CustomerID,CustomerCdesc,CustomerEdesc,OrderType,Convert(Varchar(10),ReceivedDate,111) AS ReceivedDate,
ForeignFirm,Area,SallerID,Season,Contacts,ContactsTel,ContactsFax,ContactsEmail,Merchandisers,MerchandisersTel,MerchandisersEmail,CurrencyID,CurrencyRate,DeliveredPort,DestinationPort,
PoNo,PaymentType,PriceType,Transport,DiscountRate,Discount,TaxNo,Tax,ProductAmount,TotalAmount,BankAccount,State,Remark,CreateBy,CreateAt,UpdateBy,UpdateAt
FROM dbo.oc_OrderHead Where OcID='" + OcID + "'";
Order_Head mdj = new Order_Head();
DataTable dt = SQLHelper.ExecuteSqlReturnDataTable(strSql);
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
mdj.OcID = dr["OcID"].ToString();
mdj.Ver = Convert.ToInt32(dr["Ver"]);
mdj.OrderDate = dr["OrderDate"].ToString();
mdj.CustomerID = dr["CustomerID"].ToString();
mdj.CustomerCdesc = dr["CustomerCdesc"].ToString();
mdj.CustomerEdesc = dr["CustomerEdesc"].ToString();
mdj.OrderType = dr["OrderType"].ToString();
mdj.ReceivedDate = dr["ReceivedDate"].ToString();
mdj.ForeignFirm = dr["ForeignFirm"].ToString();
mdj.Area = dr["Area"].ToString();
mdj.SallerID = dr["SallerID"].ToString();
mdj.Season = dr["Season"].ToString();
mdj.Contacts = dr["Contacts"].ToString();
mdj.ContactsTel = dr["ContactsTel"].ToString();
mdj.ContactsFax = dr["ContactsFax"].ToString();
mdj.ContactsEmail = dr["ContactsEmail"].ToString();
mdj.Merchandisers = dr["Merchandisers"].ToString();
mdj.MerchandisersTel = dr["MerchandisersTel"].ToString();
mdj.MerchandisersEmail = dr["MerchandisersEmail"].ToString();
mdj.CurrencyID = dr["CurrencyID"].ToString();
mdj.CurrencyRate = dr["CurrencyRate"].ToString();
mdj.DeliveredPort = dr["DeliveredPort"].ToString();
mdj.DestinationPort = dr["DestinationPort"].ToString();
mdj.PoNo = dr["PoNo"].ToString();
mdj.PaymentType = dr["PaymentType"].ToString();
mdj.PriceType = dr["PriceType"].ToString();
mdj.Transport = dr["Transport"].ToString();
mdj.DiscountRate = decimal.Parse(string.IsNullOrEmpty(dr["DiscountRate"].ToString()) ? "0.00" : dr["DiscountRate"].ToString());
mdj.Discount = decimal.Parse(string.IsNullOrEmpty(dr["Discount"].ToString()) ? "0.00" : dr["Discount"].ToString());
mdj.TaxNo = dr["TaxNo"].ToString();
mdj.Tax = decimal.Parse(string.IsNullOrEmpty(dr["Tax"].ToString()) ? "0.00" : dr["Tax"].ToString());
mdj.ProductAmount = decimal.Parse(string.IsNullOrEmpty(dr["ProductAmount"].ToString()) ? "0.00" : dr["ProductAmount"].ToString());
mdj.TotalAmount = decimal.Parse(string.IsNullOrEmpty(dr["TotalAmount"].ToString()) ? "0.00" : dr["TotalAmount"].ToString());
mdj.BankAccount = dr["BankAccount"].ToString();
mdj.State = dr["State"].ToString();
mdj.Remark = dr["Remark"].ToString();
mdj.CreateBy = dr["CreateBy"].ToString();
mdj.CreateAt = dr["CreateAt"].ToString();
mdj.UpdateBy = dr["UpdateBy"].ToString();
mdj.UpdateAt = dr["UpdateAt"].ToString();
}
return(mdj);
}
//返回明細資料
public JsonResult List(Order_Head model)
{
////构造成Json的格式传递
////var result = new { iTotalRecords = 100, iTotalDisplayRecords = 10, data = list };
//if (!string.IsNullOrEmpty(model.OcID))
//{
// var list = SalesOrderDAL.GetOcDetailsByID(model.OcID);
// var result = new { total = list.Count, rows = list };
// return Json(result, JsonRequestBehavior.AllowGet);
//}
//else
//{
// return Json(null, JsonRequestBehavior.AllowGet);
//}
var list = SalesOrderDAL.GetOcDetailsByID(model.OcID);
return(Json(list, JsonRequestBehavior.AllowGet));
}
public ActionResult AddHead(Order_Head model)
{
string result = SalesOrderDAL.UpdateOcHead(model);
return(Json(result));
}
public ActionResult EditHead(Order_Head model)
{
//string result = OrderDAL.UpdateOcHead(model);
return(View("Index"));
}
public static List <Order_Head> GetOcHeadReturnList(Order_Head model)
{
string strSql =
@"Select a.OcID,a.Ver,Convert(varchar(10),a.OrderDate,120) as OrderDate,a.CustomerID,a.State, b.RateDiscount,
b.AmountDiscount,b.AmountProduct,b.ProductMo,b.ProductID,b.ProductCdesc,b.OrderQty,b.OrderUnit,b.Price,b.PriceUnit
FROM oc_OrderHead a,oc_OrderDetails b Where a.OcID=b.OcID AND a.Ver=b.Ver ";
if (string.IsNullOrEmpty(model.OcID) && string.IsNullOrEmpty(model.OrderDate) && string.IsNullOrEmpty(model.ReceivedDate) &&
string.IsNullOrEmpty(model.Area) && string.IsNullOrEmpty(model.CustomerID) && string.IsNullOrEmpty(model.ForeignFirm) &&
string.IsNullOrEmpty(model.Season) && string.IsNullOrEmpty(model.SallerID) && string.IsNullOrEmpty(model.ContractID) &&
string.IsNullOrEmpty(model.BrandID) && string.IsNullOrEmpty(model.ProductMo) && string.IsNullOrEmpty(model.ProductID)
)
{
strSql += " AND 1=0 ";//返加空數據
}
else
{
if (!string.IsNullOrEmpty(model.OcID))
{
strSql += " AND a.OcID Like '%" + model.OcID + "%'";
}
if (!string.IsNullOrEmpty(model.OrderDate))
{
strSql += " AND a.OrderDate='" + model.OrderDate + "'";
}
if (!string.IsNullOrEmpty(model.ReceivedDate))
{
strSql += " AND a.ReceivedDate='" + model.ReceivedDate + "'";
}
if (!string.IsNullOrEmpty(model.Area))
{
strSql += " AND a.Area Like '%" + model.Area + "%'";
}
if (!string.IsNullOrEmpty(model.CustomerID))
{
strSql += " AND a.CustomerID Like '%" + model.CustomerID + "%'";
}
if (!string.IsNullOrEmpty(model.ForeignFirm))
{
strSql += " AND a.ForeignFirm Like '%" + model.ForeignFirm + "%'";
}
if (!string.IsNullOrEmpty(model.Season))
{
strSql += " AND a.Season Like '%" + model.Season + "%'";
}
if (!string.IsNullOrEmpty(model.SallerID))
{
strSql += " AND a.SallerID Like '%" + model.SallerID + "%'";
}
strSql += " AND a.state<>'2'";
if (!string.IsNullOrEmpty(model.ContractID))
{
strSql += " AND b.ContractID Like '%" + model.ContractID + "%'";
}
if (!string.IsNullOrEmpty(model.BrandID))
{
strSql += " AND b.BrandID Like '%" + model.BrandID + "%'";
}
if (!string.IsNullOrEmpty(model.ProductMo))
{
strSql += " AND b.ProductMo Like '%" + model.ProductMo + "%'";
}
if (!string.IsNullOrEmpty(model.ProductID))
{
strSql += " AND b.ProductID Like '%" + model.ProductID + "%'";
}
}
DataTable dt = SQLHelper.ExecuteSqlReturnDataTable(strSql);
List <Order_Head> lsOrder = new List <Order_Head>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
Order_Head mdj = new Order_Head();
mdj.OcID = dr["OcID"].ToString();
mdj.Ver = Convert.ToInt32(dr["Ver"]);
mdj.OrderDate = dr["OrderDate"].ToString();
mdj.State = dr["State"].ToString();
mdj.CustomerID = dr["CustomerID"].ToString();
mdj.ProductMo = dr["ProductMo"].ToString();
mdj.ProductID = dr["ProductID"].ToString();
mdj.ProductCdesc = dr["ProductCdesc"].ToString();
mdj.OrderQty = Convert.ToInt32(dr["OrderQty"]);
mdj.OrderUnit = dr["OrderUnit"].ToString();
mdj.Price = string.IsNullOrEmpty(dr["Price"].ToString()) ? 0 : decimal.Parse(dr["Price"].ToString());
mdj.PriceUnit = dr["PriceUnit"].ToString();
mdj.RateDiscount = string.IsNullOrEmpty(dr["RateDiscount"].ToString()) ? 0 : decimal.Parse(dr["RateDiscount"].ToString());
mdj.AmountDiscount = string.IsNullOrEmpty(dr["AmountDiscount"].ToString()) ? 0 : decimal.Parse(dr["AmountDiscount"].ToString());
mdj.AmountProduct = string.IsNullOrEmpty(dr["AmountProduct"].ToString()) ? 0 : decimal.Parse(dr["AmountProduct"].ToString());
lsOrder.Add(mdj);
}
return(lsOrder);
}
static string strRemoteDB = "DGERP2.cferp.dbo.";//SQLHelper.RemoteDB;//
public static string UpdateOcHead(Order_Head model)
{
string result = "";
string strSql = "";
string user_id = AdminUserContext.Current.LoginInfo.LoginName;
strSql += string.Format(@" SET XACT_ABORT ON ");
strSql += string.Format(@" BEGIN TRANSACTION ");
string OcID = "";
//if (model.OcID == "" || model.OcID == null)
//{
// Random rd = new Random();
// string dateTimeStr = System.DateTime.Now.ToString("yyyy/MM/dd hh:MM:dd");
// OcID = dateTimeStr.Substring(0, 4) + dateTimeStr.Substring(5, 2) + dateTimeStr.Substring(8, 2)
// + dateTimeStr.Substring(11, 2) + dateTimeStr.Substring(14, 2) + dateTimeStr.Substring(17, 2);// rd.Next(1, 1000000000).ToString();
//}
//else
// OcID = model.OcID;
OcID = model.OcID;
if (!CheckOcHead(OcID))
{
strSql += string.Format(
@" Insert Into oc_OrderHead (OcID,Ver,OrderDate,CustomerID,CustomerCdesc,CustomerEdesc,OrderType,ReceivedDate,ForeignFirm,Area,
SallerID,Season,Contacts,ContactsTel,ContactsFax,ContactsEmail,Merchandisers,MerchandisersTel,MerchandisersEmail,CurrencyID,CurrencyRate,
DeliveredPort,DestinationPort,PoNo,PaymentType,PriceType, Transport,DiscountRate,Discount,TaxNo,Tax,ProductAmount,TotalAmount
,BankAccount,State,Remark,CreateBy,CreateAt) Values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}',
'{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}',{28},'{29}','{30}',{31},{32},'{33}','{34}','{35}','{36}',getdate())",
OcID, model.Ver, model.OrderDate, model.CustomerID, model.CustomerCdesc, model.CustomerEdesc, model.OrderType, model.ReceivedDate, model.ForeignFirm, model.Area, model.SallerID,
model.Season, model.Contacts, model.ContactsTel, model.ContactsFax, model.ContactsEmail, model.Merchandisers, model.MerchandisersTel, model.MerchandisersEmail, model.CurrencyID,
model.CurrencyRate, model.DeliveredPort, model.DestinationPort, model.PoNo, model.PaymentType, model.PriceType, model.Transport, model.DiscountRate, model.Discount, model.TaxNo,
model.Tax, model.ProductAmount, model.TotalAmount, model.BankAccount, model.State, model.Remark, user_id);
}
else
{
strSql += string.Format(
@"UPDATE oc_OrderHead SET Ver='{0}',OrderDate='{1}',CustomerID='{2}',CustomerCdesc='{3}',CustomerEdesc='{4}',OrderType='{5}',ReceivedDate='{6}',ForeignFirm='{7}',Area='{8}',
SallerID='{9}',Season='{10}',Contacts='{11}',ContactsTel='{12}',ContactsFax='{13}',ContactsEmail='{14}',Merchandisers='{15}',MerchandisersTel='{16}',MerchandisersEmail='{17}',
CurrencyID='{18}',CurrencyRate={19},DeliveredPort='{20}',DestinationPort='{21}',PoNo='{22}',PaymentType='{23}',PriceType='{24}',Transport='{25}',DiscountRate={26},Discount={27},
TaxNo='{28}',Tax={29},ProductAmount={30},TotalAmount={31},BankAccount='{32}',State='{33}',Remark='{34}',UpdateBy='{35}',UpdateAt=getdate()
WHERE OcID='{36}'",
model.Ver, model.OrderDate, model.CustomerID, model.CustomerCdesc, model.CustomerEdesc, model.OrderType, model.ReceivedDate, model.ForeignFirm, model.Area,
model.SallerID, model.Season, model.Contacts, model.ContactsTel, model.ContactsFax, model.ContactsEmail, model.Merchandisers, model.MerchandisersTel,
model.MerchandisersEmail, model.CurrencyID, model.CurrencyRate, model.DeliveredPort, model.DestinationPort, model.PoNo, model.PaymentType, model.PriceType,
model.Transport, model.DiscountRate, model.Discount, model.TaxNo, model.Tax, model.ProductAmount, model.TotalAmount, model.BankAccount, model.State, model.Remark, user_id,
model.OcID);
}
strSql += string.Format(@" COMMIT TRANSACTION ");
result = SQLHelper.ExecuteSqlUpdate(strSql);
if (result == "")
{
result = "OK";
}
else
{
result = "ERROR";
}
return(result);
}
