public string InsertApprovalQueue(ApprovalQueue approvalQueue) { string environment = ConfigurationManager.AppSettings["dbname"].ToString(); StringBuilder sql = new StringBuilder(); //sql.Append(" IF NOT EXISTS(SELECT Company FROM Ice.UD24 WHERE Company = " + Escape(approvalQueue.CompanyID) + " AND ShortChar02 = " + Escape(approvalQueue.DocumentNo) + " AND Key1 = " + Escape(approvalQueue.ApprovalQueueID) + ") "); sql.Append($" IF NOT EXISTS(SELECT Company FROM {environment}.Ice.UD24 WHERE Company = " + Escape(approvalQueue.CompanyID) + " AND ShortChar02 = " + Escape(approvalQueue.DocumentNo) + " AND Character01 = " + Escape(approvalQueue.Remarks) + ") "); sql.Append(" BEGIN "); sql.Append($" INSERT INTO {environment}.Ice.UD24(Company, Key1, ShortChar01, ShortChar02, ShortChar03, ShortChar04, ShortChar05, Character01, Date01, Date03) "); sql.Append(" VALUES( " + Escape(approvalQueue.CompanyID) + ", "); sql.Append(" " + Escape(approvalQueue.ApprovalQueueID) + ", "); sql.Append(" " + Escape(approvalQueue.ModuleID) + ", "); sql.Append(" " + Escape(approvalQueue.DocumentNo) + ", "); sql.Append(" " + Escape(approvalQueue.ApprovalStatus) + ", "); sql.Append(" " + Escape(approvalQueue.ApprovalStage) + ", "); sql.Append(" " + Escape(approvalQueue.SupplierID) + ", "); sql.Append(" " + Escape(approvalQueue.Remarks) + ", "); sql.Append(" " + Escape(CommonClasses.ConvertDateToYMD(approvalQueue.SubmitDate.ToString())) + ", "); sql.Append(" " + Escape(CommonClasses.ConvertDateToYMD(approvalQueue.ProceedDate.ToString())) + ") "); sql.Append(" END "); SqlClass.Conn = ConfigurationManager.ConnectionStrings["Finsoft"].ToString(); string errMsg = null; int count = (int)SqlClass.ExecuteCmdQry(sql.ToString(), ref errMsg); return(errMsg); }
public EpiResponse CreateSO(int COTOrderNo) { EpiResponse objEpiRes = null; SqlClass.Conn = ConfigurationManager.ConnectionStrings["Finsoft"].ToString(); StringBuilder strBuilder = new StringBuilder(); strBuilder.AppendLine("Select a.OrderHedID as COTOrderNum,b.CompanyName as Company,a.CustNum,a.CustID,c.ShipToNum "); strBuilder.AppendLine(" ,a.[Owner] as SalesRep ,a.OrderComments,a.InternalRemarks,a.PromotionRemarks,a.ManualSO,d.Title as Promotion,a.OrderType as PONum,a.OrderDate,a.ReqShipDate,a.BulkOrder,isnull(a.EpiOrderNum,0) as EpiOrderNum from OrderHed a "); strBuilder.AppendLine(" left join Company b on a.CompanyID=b.CompanyID left join vwShipTo c on a.ShipToSysRowID=c.SysRowID left join Promotion d on a.PromotionID=d.PromotionID "); strBuilder.AppendLine($" Where a.OrderHedID={COTOrderNo} "); string errMsg = string.Empty; SOHd SOHd = SqlClass.SelectQry <SOHd>(strBuilder.ToString(), ref errMsg).FirstOrDefault(); strBuilder.Clear(); strBuilder.AppendLine(" select a.[Description] + ', ' AS 'data()' "); strBuilder.AppendLine(" from Promotion a inner join OrderPromotions b "); strBuilder.AppendLine($" on a.PromotionID=b.PromotionID where b.OrderHedID = {COTOrderNo} FOR XML PATH('') "); object promoRem = SqlClass.ExecutScalarQry(strBuilder.ToString(), ref errMsg); if (promoRem != null) { SOHd.Promotion = promoRem.ToString(); SOHd.Promotion.Trim(' ', ','); } strBuilder.Clear(); strBuilder.AppendLine("Select a.OrderHedID as COTOrderNum,a.OrderDtlID as COTOrderLineNum "); strBuilder.AppendLine(" ,a.PartNum,a.OrderQty,a.FOCQty,a.UOM,a.BasePrice,a.SellingPrice,a.ProposedBasePrice,a.ProposedSellingPrice "); strBuilder.AppendLine($" from OrderDtl a Where a.OrderHedID={COTOrderNo} "); IEnumerable <SODtl> SODtl = SqlClass.SelectQry <SODtl>(strBuilder.ToString(), ref errMsg); foreach (var dtl in SODtl) { strBuilder.Clear(); strBuilder.AppendLine("Select a.OrderDtlID as COTOrderLineNum,a.OrderRelID as COTOrderRelNum,b.ShipToNum "); strBuilder.AppendLine(" ,a.OrderRelQty,a.FOCRelQty,a.ShipByDate "); strBuilder.AppendLine(" from OrderRel a left join vwShipTo b on a.ShipToSysRowID=b.SysRowID "); strBuilder.AppendLine($" Where a.OrderDtlID={dtl.COTOrderLineNum} "); if (SOHd.BulkOrder) { strBuilder.AppendLine($" Order by a.ShipByDate desc "); } IEnumerable <SORel> SORel = SqlClass.SelectQry <SORel>(strBuilder.ToString(), ref errMsg); dtl.SORel = SORel.ToList(); } SOHd.SODtl = SODtl.ToList(); //Call Epicor BO if (SOHd != null) { Update upd = new Update(); objEpiRes = upd.Process(SOHd); // if (string.IsNullOrEmpty(objEpiRes.ErrMsg) && objEpiRes.EpiOrderNum > 0) { int maxCount = GetMaxApprovalQueuesID(); maxCount += 1; ApprovalQueue approvalQueue = new ApprovalQueue(); approvalQueue.CompanyID = SOHd.Company; approvalQueue.ApprovalQueueID = maxCount.ToString(); approvalQueue.ModuleID = "SO"; approvalQueue.DocumentNo = objEpiRes.EpiOrderNum.ToString(); approvalQueue.ApprovalStatus = "Approved"; approvalQueue.ApprovalStage = "Queue"; approvalQueue.SupplierID = ""; approvalQueue.Remarks = "COT Order Approved"; approvalQueue.SubmitDate = DateTime.Now; approvalQueue.ProceedDate = Convert.ToDateTime("01/01/1900"); string ErMsg = InsertApprovalQueue(approvalQueue); if (!string.IsNullOrEmpty(ErMsg)) { objEpiRes.ErrMsg = string.Format("Not Inserted to Approval Queue due to {0}", ErMsg); } } } return(objEpiRes); }