//更新主表
public static string UpdateBuyHead(BuyHead 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 ID = "";
ID = model.ID;
if (!CheckOcHead(ID))
{
strSql += string.Format(
@" Insert Into pu_BuyHead (ID,Ver,OrderDate,Vendor,VendorID,VendorAddress,BuyerName,BuyerID,CurrencyID,CurrencyRate,PaymentType,PaymentAmt,OtherAmt,TotalAmt,State,Remark,
DepartMentID,PriceType,CustomerID,CustomerCdesc,Contacts,ContactsTel,ContactsFax,Packing,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}',getdate())",
ID, model.Ver, model.OrderDate, model.Vendor, model.VendorID, model.VendorAddress, model.BuyerName, model.BuyerID, model.CurrencyID, model.CurrencyRate, model.PaymentType,
model.PaymentAmt, model.OtherAmt, model.TotalAmt, model.State, model.Remark, model.DepartMentID, model.PriceType, model.CustomerID, model.CustomerCdesc, model.Contacts,
model.ContactsTel, model.ContactsFax, model.Packing, user_id);
//更新最大序號
DataTable dt = SQLHelper.ExecuteSqlReturnDataTable(@"SELECT fixation_value_1 AS prex FROM sys_bill_rule A WHERE A.bill_id = 'PUR1'");
string strPrex = dt.Rows[0]["prex"].ToString(); //"VWH";
dt = SQLHelper.ExecuteSqlReturnDataTable(string.Format(@"SELECT '1' FROM sys_bill_max with(nolock) WHERE bill_id = 'PUR1' and bill_text1='{0}'", strPrex));
if (dt.Rows.Count > 0)
{
strSql += string.Format(@" Update sys_bill_max SET bill_code='{0}' Where bill_id='PUR1' And bill_text1='{1}' ", ID, strPrex);
}
else
{
strSql += string.Format(@" Insert INTO sys_bill_max(bill_id,bill_code,bill_text1) Values('{0}','{1}','{2}')", "PUR1", ID, strPrex);
}
}
else
{
strSql += string.Format(
@" UPDATE pu_BuyHead SET OrderDate='{0}',Vendor='{1}',VendorID='{2}',VendorAddress='{3}',BuyerName='{4}',BuyerID='{5}',CurrencyID='{6}',CurrencyRate={7},PaymentType='{8}',
PaymentAmt={9},OtherAmt={10},TotalAmt={11},State='{12}',Remark='{13}',DepartMentID='{14}',PriceType='{15}',CustomerID='{16}',CustomerCdesc='{17}',Contacts='{18}',
ContactsTel='{19}',ContactsFax='{20}',Packing='{21}',UpdateBy='{22}',UpdateAt=getdate()
WHERE ID='{23}' AND Ver='{24}'",
model.OrderDate, model.Vendor, model.VendorID, model.VendorAddress, model.BuyerName, model.BuyerID, model.CurrencyID, model.CurrencyRate, model.PaymentType,
model.PaymentAmt, model.OtherAmt, model.TotalAmt, model.State, model.Remark, model.DepartMentID, model.PriceType, model.CustomerID, model.CustomerCdesc, model.Contacts,
model.ContactsTel, model.ContactsFax, model.Packing, user_id, ID, model.Ver);
}
strSql += string.Format(@" COMMIT TRANSACTION ");
result = SQLHelper.ExecuteSqlUpdate(strSql);
if (result == "")
{
result = "OK";
}
else
{
result = "ERROR";
}
return(result);
}
//返回主檔資料
public static List <BuyHead> GetBuyHeadByID(string ID)
{
string strSql =
@"SELECT ID,Ver,Convert(Varchar(10),OrderDate,111) AS OrderDate,VendorID,Vendor,VendorAddress,CustomerID,CustomerCdesc,Contacts,
ContactsTel,ContactsFax,BuyerID,BuyerName,DepartMentID,CurrencyID,CurrencyRate,PaymentType,PriceType,PaymentAmt,OtherAmt,TotalAmt,State,
Remark,CreateBy,CreateAt,UpdateBy,UpdateAt,Packing
FROM dbo.pu_BuyHead Where ID='" + ID + "'";
BuyHead mdj = new BuyHead();
DataTable dt = SQLHelper.ExecuteSqlReturnDataTable(strSql);
List <BuyHead> lsHead = new List <BuyHead>();
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
mdj.ID = dr["ID"].ToString();
mdj.Ver = Convert.ToInt32(dr["Ver"]);
mdj.OrderDate = dr["OrderDate"].ToString();
mdj.VendorID = dr["VendorID"].ToString();
mdj.Vendor = dr["Vendor"].ToString();
mdj.VendorAddress = dr["VendorAddress"].ToString();
mdj.CustomerID = dr["CustomerID"].ToString();
mdj.CustomerCdesc = dr["CustomerCdesc"].ToString();
mdj.Contacts = dr["Contacts"].ToString();
mdj.ContactsTel = dr["ContactsTel"].ToString();
mdj.ContactsFax = dr["ContactsFax"].ToString();
mdj.BuyerID = dr["BuyerID"].ToString();
mdj.BuyerName = dr["BuyerName"].ToString();
mdj.DepartMentID = dr["DepartMentID"].ToString();
mdj.CurrencyID = dr["CurrencyID"].ToString();
mdj.CurrencyRate = decimal.Parse(string.IsNullOrEmpty(dr["CurrencyRate"].ToString()) ? "0.00" : dr["CurrencyRate"].ToString());
mdj.PaymentType = dr["PaymentType"].ToString();
mdj.PaymentAmt = decimal.Parse(string.IsNullOrEmpty(dr["PaymentAmt"].ToString()) ? "0.00" : dr["PaymentAmt"].ToString());
mdj.OtherAmt = decimal.Parse(string.IsNullOrEmpty(dr["OtherAmt"].ToString()) ? "0.00" : dr["OtherAmt"].ToString());
mdj.TotalAmt = decimal.Parse(string.IsNullOrEmpty(dr["TotalAmt"].ToString()) ? "0.00" : dr["TotalAmt"].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();
mdj.PriceType = dr["PriceType"].ToString();
mdj.Packing = dr["Packing"].ToString();
lsHead.Add(mdj);
}
return(lsHead);
}
public static BuyHead GetBuyerByID(string strBuyerID)
{
strBuyerID = strBuyerID.PadLeft(10, '0');
string strSql = string.Format(
@"SELECT Top 1 id,Rtrim(name) as name FROM {0}cd_personnel WHERE within_code='0000' and id='{1}' and state<>'2' and Isnull(personnel_state,'')<>'2'", strRemoteDB, strBuyerID);
DataTable dt = SQLHelper.ExecuteSqlReturnDataTable(strSql);
BuyHead objModel = new BuyHead();
if (dt.Rows.Count > 0)
{
objModel.BuyerID = dt.Rows[0]["id"].ToString();
objModel.BuyerName = dt.Rows[0]["name"].ToString();
}
else
{
objModel.BuyerID = "";
objModel.BuyerName = "";
}
return(objModel);
}
//返回供應商地址信息等
public static BuyHead GetVendorByID(string strVendorID)
{
string strSql = string.Format(
@"SELECT A.name AS Vendor,ISNULL(A.add_address,'') AS VendorAddress,ISNULL(A.linkman,'') AS Contacts,
ISNULL(A.l_phone,'') AS ContactsTel,ISNULL(A.fax,'') AS ContactsFax,ISNULL(A.money_id,'') AS CurrencyID,
ISNULL(SS.exchange_rate,0) AS CurrencyRate
FROM dbo.bs_vendor A LEFT JOIN
(SELECT aa.id,aa.exchange_rate FROM bs_exchange_rate aa,
(select id,max(days) as days from bs_exchange_rate where state <>'2' group by id) s
WHERE aa.id=s.id And aa.days=s.days
) SS ON ISNULL(A.money_id,'')=SS.id
WHERE A.id='{0}' AND A.state ='1'", strVendorID);
DataTable dt = SQLHelper.ExecuteSqlReturnDataTable(strSql);
BuyHead objModel = new BuyHead();
if (dt.Rows.Count > 0)
{
objModel.Vendor = dt.Rows[0]["Vendor"].ToString();
objModel.VendorAddress = dt.Rows[0]["VendorAddress"].ToString();
objModel.Contacts = dt.Rows[0]["Contacts"].ToString();
objModel.ContactsTel = dt.Rows[0]["ContactsTel"].ToString();
objModel.ContactsFax = dt.Rows[0]["ContactsFax"].ToString();
objModel.CurrencyID = dt.Rows[0]["CurrencyID"].ToString();
objModel.CurrencyRate = decimal.Parse(dt.Rows[0]["CurrencyRate"].ToString());
}
else
{
objModel.Vendor = "";
objModel.VendorAddress = "";
objModel.Contacts = "";
objModel.ContactsTel = "";
objModel.ContactsFax = "";
objModel.CurrencyID = "";
objModel.CurrencyRate = 0;
}
return(objModel);
}
//返回明細資料
public JsonResult List(BuyHead model)
{
var list = PurchaseDAL.GetBuyDetailsByID(model.ID);
return(Json(list, JsonRequestBehavior.AllowGet));
}
public ActionResult AddHead(BuyHead model)
{
string result = PurchaseDAL.UpdateBuyHead(model);
return(Json(result));
}