public Response GetPurchaseOrderLine(string token, [FromBody] PurchaseOrderHead param) { Response response = new Response(); if (string.IsNullOrEmpty(token) || !token.Equals(_token)) { response.code = "404"; response.message = "Invild token"; } else { List <PurchaseOrderLine> lstPOL = PurchaseOrderHelper.GetPurchaseOrderLine(param); if (lstPOL == null || lstPOL.Count == 0) { response.code = "500"; response.message = "No Data"; } else { response.code = "200"; response.content = lstPOL; } } return(response); }
/// <summary> /// 新建采购单 /// </summary> /// <param name="po"></param> /// <returns></returns> private string CreatePurchaseOrderHead(PurchaseOrderHead po, string strDateTime, string strDateFormat, ref string headGuid) { headGuid = Guid.NewGuid().ToString(); string orderDescription = po.costCenterCode.Substring(4, 3) + "-PR" + "(" + strDateFormat + ")"; string strSql = " INSERT INTO PURCHASEORDERHEAD " + " ( HEADGUID " + " , ORDERDATE " + " , ORDERDESCRIPTION " + " , ORDERMETHOD " + " , DBCODE " + " , CREATEUSER " + " , CREATEDATE) " + " VALUES " + " ( '{0}' " + " , '{1}' " + " , '{2}' " + " , '{3}' " + " , '{4}' " + " , '{5}' " + " , '{6}' ) "; return(string.Format(strSql, headGuid, strDateFormat, orderDescription, "001" , po.dbCode, po.employee, strDateTime)); }
public Response SavePurchaseOrder(string token, [FromBody] PurchaseOrderHead po) { Response response = new Response(); if (string.IsNullOrEmpty(token) || !token.Equals(_token)) { response.code = "404"; response.message = "Invild token"; } else { int result = PurchaseOrderHelper.SavePurchaseOrder(po); if (result == 0) { response.code = "500"; response.message = "No Data"; } else { response.code = "200"; response.content = result; } } return(response); }
/// <summary> /// 根据HeadGuid取得PurchaseOrderLine集合 /// </summary> /// <param name="headGuid"></param> /// <returns></returns> public List <PurchaseOrderLine> GetPurchaseOrderLine(PurchaseOrderHead param) { /*** 取得PurchaseOrderLine部分 ***/ string strSqlLine = " SELECT H.HEADGUID " + " , L.LINEGUID " + " , CONVERT(VARCHAR(10), H.ORDERDATE, 23) AS ORDERDATE " + " , H.ORDERDESCRIPTION " + " , H.ORDERMETHOD " + " , H.DBCODE " + " , L.LINENUMBER " + " , L.SUPPLIERCODE " + " , L.WAREHOUSECODE " + " , L.ITEMDESCRIPTION " + " , L.ITEMCODE " + " , L.UNIT " + " , L.COSTCENTER " + " , L.REMARK " + " , L.ERPNUMBER " + " , D.COSTUNIT " + " , D.QTY " + " , D.PRICE " + " , D.TOTALPRICE " + " FROM PURCHASEORDERLINE AS L (NOLOCK) " + " INNER JOIN PURCHASEORDERHEAD AS H (NOLOCK) " + " ON L.HEADGUID = H.HEADGUID " + " INNER JOIN PURCHASEORDERLINEDETAIL AS D (NOLOCK) " + " ON L.LINEGUID = D.LINEGUID " + " AND D.DELETEUSER IS NULL " + " WHERE H.ORDERDATE >= '{0}' " + " AND H.DBCODE = '{1}' " + " AND L.COSTCENTER = '{2}' "; List <PurchaseOrderLine> lstPOL = SqlServerHelper.GetEntityList <PurchaseOrderLine>(SqlServerHelper.purchaseorderconn(), string.Format(strSqlLine, param.orderDate, param.dbCode, param.costCenterCode)); return(lstPOL); }
/// <summary> /// 读取采购订单 /// </summary> /// <param name="param">Purchase Order</param> /// <returns></returns> public static List <PurchaseOrderLine> GetPurchaseOrderLine(PurchaseOrderHead param) { try { if (param == null) { throw new Exception("param is null"); } List <PurchaseOrderLine> lstPOL = factory.GetPurchaseOrderLine(param); if (lstPOL == null) { throw new Exception("DAL.Purchase.PurchaseOrderFactory.GetPurchaseOrderLine()==null"); } return(lstPOL); } catch (Exception ex) { LogHelper.WriteLog(new Log() { message = ex.Message }, "GetPurchaseOrderLine"); return(null); } }
/// <summary> /// 保存采购订单 /// </summary> /// <param name="param">Purchase Order</param> /// <returns></returns> public static int SavePurchaseOrder(PurchaseOrderHead po) { try { if (po == null) { throw new Exception("PurchaseOrder is null"); } int result = factory.SavePurchaseOrder(po); if (result == 0) { throw new Exception("DAL.Purchase.PurchaseOrderFactory.SavePurchaseOrder()==0"); } return(result); } catch (Exception ex) { LogHelper.WriteLog(new Log() { message = ex.Message }, "SavePurchaseOrder"); return(0); } }
/// <summary> /// 根据成本中心、日期导入采购单临时表 /// </summary> /// <param name="param"></param> /// <returns></returns> public int ImportToPurchaseOrder(MenuOrderHead param) { PurchaseOrderHead po = new PurchaseOrderHead(); // 根据日期取得所有的采购单行项 List <PurchaseOrderLine> lstAllLines = GetAllPurchaseOrderLine(param); PurchaseOrderHead poParam = new PurchaseOrderHead(); // 取得DBCODE string strSqlDBCode = " SELECT TOP 1 " + " DBNAME " + " , WAREHOUSECODE " + " FROM CCMAST (NOLOCK)" + " WHERE COSTCENTERCODE = '{0}' "; CCWhs ccwhs = SqlServerHelper.GetEntity <CCWhs>(SqlServerHelper.salesorderConn(), string.Format(strSqlDBCode, param.costCenterCode)); // 根据条件取得当天已存在的采购单行项(待比对) poParam.orderDate = param.requiredDate; poParam.costCenterCode = param.costCenterCode; poParam.warehouseCode = ccwhs.warehouseCode; poParam.dbCode = ccwhs.dbName; List <PurchaseOrderLine> lstCurLines = new List <PurchaseOrderLine>(); // 取得既有的采购订单数据 lstCurLines = GetPurchaseOrderLine(poParam); // 取得所有修改和新增的记录 if (lstCurLines != null && lstCurLines.Count > 0) { // Menu连接PO取得需要新增和修改的部分 po.lstPurchaseOrderLine = lstAllLines.GroupJoin(lstCurLines, all => new { all.supplierCode, all.orderDate }, //供应商和订单日期匹配 cur => new { cur.supplierCode, cur.orderDate }, (all, cur) => new { all, cur }) .Where(q => q.cur == null || !q.cur.Any() || !q.cur.Where(p => p.itemCode == q.all.itemCode && (p.remark == null ? string.Empty : p.remark) == (q.all.remark == null ? string.Empty : q.all.remark) && p.qty == q.all.qty).Any()) .Select(q => { if (q.cur == null || !q.cur.Any()) { return(q.all); } // headGuid q.all.headGuid = q.cur.FirstOrDefault().headGuid; var tmp = q.cur.Where(p => p.itemCode == q.all.itemCode); if (tmp.Any()) //供应商,订单日,item 匹配 // lineGuid { q.all.lineGuid = tmp.FirstOrDefault().lineGuid; } return(q.all); }).ToList(); // PO连接Menu取得需要删除的部分 po.lstPurchaseOrderLine = po.lstPurchaseOrderLine.Concat(lstCurLines.GroupJoin(lstAllLines, cur => new { cur.supplierCode, cur.orderDate }, all => new { all.supplierCode, all.orderDate }, (cur, all) => new { cur, all }) .Where(q => q.all == null || !q.all.Any() || !q.all.Where(p => p.itemCode == q.cur.itemCode).Any()) .Select(q => { // Head DelFlag if (q.all == null || !q.all.Any()) { q.cur.delFlag = true; return(q.cur); } // Line DelFlag var tmp = q.all.Where(r => r.itemCode == q.cur.itemCode); if (!tmp.Any()) { q.cur.delFlag = true; } return(q.cur); }).Where(r => r.delFlag)).ToList(); } else { po.lstPurchaseOrderLine = lstAllLines; } po.dbCode = poParam.dbCode; po.costCenterCode = poParam.costCenterCode; po.warehouseCode = poParam.warehouseCode; po.userId = param.createUser; po.employee = "1";// (new SOAccount.AccountFactory()).getEmployeeId(param.createUser); return(SavePurchaseOrder(po)); }
/// <summary> /// 保存采购订单 /// </summary> /// <param name="param">Purchase Order</param> /// <returns></returns> public int SavePurchaseOrder(PurchaseOrderHead po) { int result = 0; StringBuilder sbSqlFinal = new StringBuilder(); string strDateTimeNow = DateTime.Now.ToString(); string strSqlUptRemark = " UPDATE PURCHASEORDERLINE " + " SET REMARK = '{0}' " + " WHERE LINEGUID = '{1}' " + " AND ISNULL(REMARK, '') <> '{0}' "; string strSqlDelDetail = " UPDATE PURCHASEORDERLINEDETAIL " + " SET DELETEUSER = '******' " + " , DELETEDATE = '{1}' " + " WHERE LINEGUID = '{2}' " + " AND DELETEUSER IS NULL "; string strSqlIstDetail = " INSERT INTO PURCHASEORDERLINEDETAIL " + " ( LINEGUID " + " , COSTUNIT " + " , QTY " + " , PRICE " + " , CREATEUSER " + " , CREATEDATE " + " , TOTALPRICE ) " + " VALUES " + " ( '{0}' " + " , '{1}' " + " , {2} " + " , {3} " + " , '{4}' " + " , '{5}' " + " , '{6}' ) "; if (po.lstPurchaseOrderLine == null || po.lstPurchaseOrderLine.Count == 0) { return(0); } // 按日期、公司、成本中心、供应商分组 var lqPO = (from p in po.lstPurchaseOrderLine group p by new { // 日期 orderDate = p.orderDate, // 成本中心 costCenter = p.costCenter, // 供应商 supplierCode = p.supplierCode, }); // PO表头Guid string headGuid = string.Empty; foreach (var gPO in lqPO) { // 检查Head是否存在 headGuid = gPO.OrderByDescending(r => r.headGuid).FirstOrDefault().headGuid; // 检查HeadGuid是否已经存在,没有则新建Head if (string.IsNullOrWhiteSpace(headGuid)) { sbSqlFinal.Append(CreatePurchaseOrderHead(po, strDateTimeNow, gPO.Key.orderDate, ref headGuid)); } foreach (PurchaseOrderLine pol in gPO.ToList()) { // 1.LineGuid不为空或删除标志为true时删除LineDetail if (!string.IsNullOrWhiteSpace(pol.lineGuid) || pol.delFlag) { sbSqlFinal.Append(string.Format(strSqlDelDetail, po.userId, strDateTimeNow, pol.lineGuid)); // 删除标志为true时进入下一次循环 if (pol.delFlag) { continue; } } // LineGuid不为空 if (!string.IsNullOrWhiteSpace(pol.lineGuid)) { // 更新Remark(Sql中判断是否更新) sbSqlFinal.Append(string.Format(strSqlUptRemark, pol.remark, pol.lineGuid)); } else { // 创建Line记录 sbSqlFinal.Append(CreatePurchaseOrderLine(po.costCenterCode, po.warehouseCode, pol, strDateTimeNow, headGuid, 0)); } // 新增LineDetail记录 sbSqlFinal.Append(string.Format(strSqlIstDetail, pol.lineGuid, pol.costUnit, pol.qty, pol.price, po.userId, strDateTimeNow, pol.totalPrice)); } } result = SqlServerHelper.Execute(SqlServerHelper.purchaseorderconn(), sbSqlFinal.ToString()); return(result); }