using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ICS.WCF.Base
{
    public class Appconfig
    {
        public static string GetU9ConnStr()
        {
            //return ConfigurationManager.AppSettings["MainConnectionString"].ToString();
            return ConfigurationManager.AppSettings["ERPConnStr"].ToString();


        }

        public static string GetDefaultUser()
        {
            //return ConfigurationManager.AppSettings["MainConnectionString"].ToString();
            return ConfigurationManager.AppSettings["DefaultUser"].ToString();


        }


        public static string GetMoRptType()
        {
            return ConfigurationManager.AppSettings["MoRptType"].ToString();
        }

        public static string GetRtnMoIssueDocType()
        {
            return ConfigurationManager.AppSettings["RtnMoIssueDocType"].ToString();


        }


        public static string GetMESStr()
        {
            return ConfigurationManager.AppSettings["MESConnectionString"].ToString();
        }

        public static string GetPLMStr()
        {
            return ConfigurationManager.AppSettings["PLMConnectionString"].ToString();
        }
        public static string GetBPMESStr()
        {
            return ConfigurationManager.AppSettings["SQSMESConnectionString"].ToString();
        }
        public static string GetBPERPStr()
        {
            return ConfigurationManager.AppSettings["ERPDB"].ToString();
        }
        //

        public static DataTable GetPRDocID(string PRDocCode, int PRDocLineCode)
        {
            string sql = @" select  A.[ID] as [PRID], 
 A1.[ID] as [PRLineID]
 from  PR_PR as A  
 inner join [PR_PRLine] as A1 on (A.[ID] = A1.[PR])
 where A.[DocNo]='" + PRDocCode + "' and A1.[DocLineNo]='" + PRDocLineCode + "'";
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());
            return dt;
        }


        public static DataTable GetSOData(string SODocCode)
        {
            string sql = @" select  A.IsAchievementAllotation,
                            A.IsFillDoc,
                            A.IsPriceIncludeTax,
                            A.IsSaleDiscount,
                            A.IsTransOut,
                            A.IsAllowBatch,
                            A.IsAllowTransfer,
                            A.IsRollBack,
                            A.AutoCreateSOM,
                            A.SOPriority,
                            A.DemandType
                             from  SM_SO as A  
                             where A.[DocNo]='" + SODocCode + "' ";
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());
            return dt;
        }

        public static int GetSOLineNum(string SODocCode)
        {
            string sql = @"select A1.*  from  SM_SO as A  
 left join SM_SOLine A1 on A.ID=A1.SO
 where A.[DocNo]='{0}'  ";
            sql = string.Format(sql, SODocCode);
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());

            return dt.Rows.Count;
        }

        public static int GetSOLineData(string SODocCode, int DocLineNo)
        {
            string sql = @"select A1.* 
 from  SM_SO as A  
 left join SM_SOLine A1 on A.ID=A1.SO
 where A.[DocNo]='{0}' and A1.DocLineNo={1}";
            sql = string.Format(sql, SODocCode, DocLineNo);
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());

            return dt.Rows.Count;
        }

        public static string GetSOLineID(string SODocCode, int DocLineNo)
        {
            string sql = @"select A2.ID
 from  SM_SO as A  
 left join SM_SOLine A1 on A.ID=A1.SO
 left join SM_SOShipline A2 on A2.SOLine=A1.ID
 where A.[DocNo]='{0}' and A1.DocLineNo={1}";
            sql = string.Format(sql, SODocCode, DocLineNo);
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());

            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["id"].ToString();
        }

        public static string GetShipPlanID(string ShipPlanLineID)
        {
            string sql = @"select ID from SM_ShipPlanLine
                           where ID='{0}'";
            sql = string.Format(sql, ShipPlanLineID);
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());

            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["id"].ToString();
        }

        public static DataTable GetSO(string SODocCode)
        {
            string sql = @"select a.DescFlexField_PubDescSeg2,a.DescFlexField_PubDescSeg3
 from  SM_SO as A  
 where A.[DocNo]='{0}'";
            sql = string.Format(sql, SODocCode);
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());

            if (dt == null || dt.Rows.Count == 0) return dt;
            else return dt;
        }


        public static string GetOrgID(string SODocCode, int DocLineNo)
        {
            string sql = @"select a.org ID
 from  SM_SO as A  
 left join SM_SOLine A1 on A.ID=A1.SO
 where A.[DocNo]='{0}' and A1.DocLineNo={1}";
            sql = string.Format(sql, SODocCode, DocLineNo);
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());

            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["id"].ToString();
        }

        public static string Getpri(string SODocCode, int DocLineNo)
        {
            string sql = @"   select a.org ID,DocNo,DocLineNo,B.CODE,B.DescFlexField_PrivateDescSeg1
 from  SM_SO as A  
 left join SM_SOLine A1 on A.ID=A1.SO
left join CBO_ItemMaster B on A1.ItemInfo_ITEMID =B.ID AND  A1.ORG=B.ORG
 where A.[DocNo]='{0}' and A1.DocLineNo={1}";
            sql = string.Format(sql, SODocCode, DocLineNo);
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());

            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["DescFlexField_PrivateDescSeg1"].ToString();
        }


        public static string GetWhIDByCode(string WHCode, string org)
        {
            string sql = @"SELECT a.ID FROM CBO_Wh a
                        where a.Code='" + WHCode + "' AND a.Org='" + org + "' ";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["id"].ToString();
        }
        public static bool GetWhLotControl(string WHCode, string org)
        {
            string sql = @"SELECT a.IsLot FROM CBO_Wh a
                           where a.Code='" + WHCode + "' AND a.Org='" + org + "' ";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0)
            {
                return false;
            }
            else
            {
                return Convert.ToBoolean(dt.Rows[0]["IsLot"].ToString());
            }
        }
        public static string GetSmshipTYPE(string WHCode, string org)
        {
            string sql = @"SELECT a.ID FROM SM_ShipDocType a
                        where a.Code='" + WHCode + "' AND a.Org='" + org + "' ";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["id"].ToString();
        }



        public static DataTable GetItemRequestList(string LOTNO, string BIN, string ITEM, string WH, string QTY, string ORG, string ERPConStr)
        {
            string sql = @" SELECT   
orgt.name 组织名称,
ww.iteminfo_itemcode 存货编码 ,
ww.iteminfo_itemname 存货名称 ,
wh.code   仓库编码,
wht.name 仓库名称,
ww.BinInfo_Code,
ww.LotInfo_LotCode,
isnull(SUM ( ((((ww.[StoreQty]-ww.[ResvStQty])-ww.[ResvOccupyStQty])-
CASE WHEN ((((ww.[IsProdCancel]=1) OR (ww.[MO_EntityID] !=0)) OR ww.[ProductDate] IS NOT NULL) OR (ww.[WP_EntityID] !=0)) 
THEN ww.[StoreQty] 
ELSE CONVERT (DECIMAL (24,9),0) END)+
ww.[SupplyQtySU])-ww.[DemandQtySU] ),0)-(" + QTY + @") 可用量 
FROM [dbo].InvTrans_WhQoh ww WITH (nolock) 
LEFT JOIN [dbo].cbo_wh wh WITH (nolock) ON wh.id=ww.wh 
LEFT JOIN [dbo].cbo_wh_trl wht WITH (nolock) ON wh.id=wht.id AND wht.sysmlflag='zh-CN' 
LEFT JOIN [dbo].Base_Organization org WITH (nolock) ON org.id=wh.org 
LEFT JOIN  [dbo].Base_Organization_Trl orgt ON orgt.id=wh.org AND orgt.sysmlflag='zh-CN' 
WHERE 1=1  and 
ww.LotInfo_LotCode='" + LOTNO + @"' and ww.BinInfo_Code='" + BIN + @"' AND ww.iteminfo_itemcode='" + ITEM + "' AND org.Code='" + ORG + @"' and wh.code='" + WH + @"'
GROUP BY orgt.name,ww.iteminfo_itemcode ,ww.iteminfo_itemname,wh.code,wht.name,ww.BinInfo_Code,
ww.LotInfo_LotCode ";


            DataTable dt = DBhlper.Query(sql, ERPConStr);

            return dt;
        }
        public static string uomid(string Mark)
        {
            string sql = @"select a.id,code,name from Base_UOM a
left join Base_UOM_Trl b on a.ID=b.ID
where Code='" + Mark + "'";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["id"].ToString();
        }
        public static string GetBysupid(string BinCode, string org)
        {
            string sql = @"select b.id  from CBO_Supplier  b  where b.code='" + BinCode + "' and b.org='" + org + "'";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["id"].ToString();
        }
        public static string GetByBinid(string BinCode, string org)
        {
            string sql = @"select b.id  from CBO_Bin  b  where b.code='" + BinCode + "' and b.org='" + org + "'";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["id"].ToString();
        }
        public static string GetWhCodeByBinid(string BinCode, string org)
        {
            string sql = @"select w.id from cbo_wh w

            where w.code='" + BinCode + "' and w.org='" + org + "'";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["id"].ToString();
        }
        public static string GetLotNOByBinid(string LotNO, string org, string itemid)
        {
            string sql = @"select w.id from Lot_LotMaster w
            
            where w.LotCode='" + LotNO + "' and w.DataOwnerOrg='" + org + "'  and Item=" + itemid + "";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["id"].ToString();
        }
        public static string GetProjectid(string BinCode, string org)
        {
            string sql = @"select w.id from CBO_Project w

            where w.code='" + BinCode + "' and w.org='" + org + "'";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["id"].ToString();
        }
        public static string Getusername(string Mark)
        {
            string sql = @"select a.code,b.displayname from Base_User a
left join Base_User_Trl b on a.id=b.id where b.displayname='" + Mark + "'";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["displayname"].ToString();
        }
        public static DataTable GetU9UserNameByCode(string UserCode)
        {
            string sql = @"select ID,Name AS UserName from Base_User
                           where Code='" + UserCode + "'";
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt;
        }
        public static string GetPOID(string Mark, string line)
        {
            string sql = @"select b.ID from PM_PurchaseOrder a
inner join PM_POLine b on a.ID=b.PurchaseOrder
where DocNo='" + Mark + "' and DocLineNo='" + line + "'";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["ID"].ToString();
        }
        public static string GetMarkid(string Mark)
        {
            string sql = @"select id from CBO_ItemTradeMark where trademark='" + Mark + "'";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["id"].ToString();
        }

        public static string ITEMMATERID(string Mark, string org)
        {
            string sql = @"select ID from CBO_ItemMaster where code='" + Mark + "' and Org='" + org + "'";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["ID"].ToString();
        }

        public static string GetITEMMATERID(string ItemCode, string OrgCode)
        {
            string sql = @"select a.ID from CBO_ItemMaster a INNER JOIN Base_Organization b ON a.Org=b.ID
                             where a.Code='" + ItemCode + "' AND b.Code='" + OrgCode + "'";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["ID"].ToString();
        }

        public static string GetORGID(string OrgCode)
        {
            string sql = @"select ID from Base_Organization
                             where Code='" + OrgCode + "'";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["ID"].ToString();
        }

        public static string GetDeptID(string Dept)
        {
            string sql = @"select ID from CBO_Department
                             where Code='" + Dept + "'";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["ID"].ToString();
        }

        public static string GetDocTypeID(string code, string org)
        {
            string sql = @"select a.id ID,code,org,name from PM_IssueDocType a left join  PM_IssueDocType_trl b on a.id=b.id where org ='" + org + "' and code='" + code + "' and sysmlflag='zh-CN'";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["ID"].ToString();
        }

        public static string GetSupplierID(string Supplier, string OrgCode)
        {
            string sql = @"select a.ID from CBO_Supplier a
                            INNER JOIN Base_Organization b ON a.Org=b.ID
                             where a.Code='" + Supplier + "' AND b.Code='" + OrgCode + "'";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["ID"].ToString();
        }

        public static long GetWWID(string Supplier)
        {
            string sql = @"select ID from PM_PurchaseOrder 
                             where DocNo='" + Supplier + "'";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt != null && dt.Rows.Count > 0)
            {
                return Convert.ToInt64(dt.Rows[0][0].ToString());
            }
            return 0;
        }


        public static long GetWWBLID(string Supplier, string OrgCode)
        {
            string sql = @"select b.ID,b.DocLineNo from PM_PurchaseOrder a 
left join PM_POLine b on a.id=b.PurchaseOrder
                             where DocNo='" + Supplier + "' AND DocLineNo='" + OrgCode + "'";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt != null && dt.Rows.Count > 0)
            {
                return Convert.ToInt64(dt.Rows[0][0].ToString());
            }
            return 0;
        }


        public static string IsExistsCode(string Code, string TableName)
        {
            string sql = @"    select  id from  " + TableName + @"  where code='" + Code + @"' ";
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            return "";
        }
        public static long GetLotID(string LotCode, string INVCode, string OrgID)
        {
            string sql = @" select A.ID from Lot_LotMaster A
                            LEFT JOIN CBO_ItemMaster B ON B.ID=A.Item
                            where LotCode='" + LotCode + "' and B.Code='" + INVCode + "' AND B.Org='" + OrgID + "'";
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());
            if (dt != null && dt.Rows.Count > 0)
            {
                return long.Parse(dt.Rows[0][0].ToString());
            }
            return 0;
        }


        public static string GetRcvType(string Code, string ORG)
        {
            string sql = @"   select id,code from PM_RcvDocType 
WHERE code='" + Code + "' AND ORG='" + ORG + "' ";
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            return "";
        }


        public static string GetPMIssueDocType(string Code, string ORG)
        {
            string sql = @"   select id,code from PM_IssueDocType 
WHERE code='" + Code + "' AND ORG='" + ORG + "' ";
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            return "";
        }
        public static string GetRCVDocType(string Code, string ORG)
        {
            string sql = @"    
SELECT a.ID FROM PM_RcvDocType a
WHERE code='" + Code + "' AND ORG='" + ORG + "' ";
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            return "";
        }

        public static string GetRtnKeyByCode(string DocNO, string Org)
        {
            string sql = @"    
                            SELECT a.ID FROM PM_Rtn a
                            WHERE DocNO='" + DocNO + "' AND Org='" + Org + "' ";
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            return "";
        }




        public static DataTable GetITB(string Code)
        {
            string sql = @"
                     select a.org ORGID,oo.code ORGCode from MO_MO a
                    LEFT JOIN Base_organization oo ON a.ORG=oo.id
                    where DocNo='" + Code + @"'";
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            return dt;
        }

        public class OperationResult
        {
            /// <summary>
            /// 结果状态,默认值false
            /// </summary>
            public bool Success { get; set; }

            /// <summary>
            ///处理消息
            /// </summary>
            public string Message { get; set; }


        }

        public class DocTypeModel
        {
            public string TypeName;
            public string OrgCode;//信息
        }

        public static DataTable GetDocType(string TypeName, string OrgCode)
        {
            string sql = @" SELECT   '' Type, '' Code, '' Name
       UNION ALL
       SELECT* FROM(SELECT DISTINCT  '杂发' Type, b.Code, bb.Name
        FROM InvDoc_MiscShipDocType b
        LEFT JOIN  InvDoc_MiscShipDocType_Trl bb   ON bb.ID= b.ID
        LEFT JOIN Base_Organization c ON b.Org= c.ID
        WHERE c.Code=  '{0}' AND b.Effective_IsEffective='1' AND b.Effective_EffectiveDate <= GETDATE() AND b.Effective_DisableDate >  GETDATE()
        UNION ALL
        SELECT DISTINCT  '杂收' Type, b.Code, bb.Name
         FROM   InvDoc_MiscRcvDocType b
        LEFT JOIN InvDoc_MiscRcvDocType_Trl bb ON b.ID= bb.ID
        LEFT JOIN Base_Organization c ON b.Org= c.ID
        WHERE c.Code= '{0}' AND b.Effective_IsEffective='1' AND b.Effective_EffectiveDate <= GETDATE() AND b.Effective_DisableDate >  GETDATE()
        UNION ALL
        SELECT DISTINCT  '标准出货' Type, b.Code, bb.Name
         FROM   SM_ShipDocType b
        LEFT JOIN SM_ShipDocType_Trl bb ON b.ID= bb.ID
        LEFT JOIN Base_Organization c ON b.Org= c.ID
        WHERE c.Code= '{0}' AND b.Effective_IsEffective='1' AND b.Effective_EffectiveDate <= GETDATE() AND b.Effective_DisableDate >  GETDATE()
        UNION ALL
        SELECT DISTINCT  '部门' Type, a.Code, b.Name
        FROM CBO_Department a
        LEFT JOIN CBO_Department_Trl b ON a.ID= b.ID
        LEFT JOIN Base_Organization c ON a.Org= c.ID
        WHERE c.Code= '{0}' and a.DescFlexField_PrivateDescSeg1='10' AND a.Effective_IsEffective='1' AND a.Effective_EffectiveDate <= GETDATE() AND a.Effective_DisableDate >  GETDATE()
        UNION ALL
        SELECT DISTINCT  '项目' Type, a.Code, b.Name
         FROM CBO_Project a
        LEFT JOIN CBO_Project_Trl b ON a.ID= b.ID
        LEFT JOIN Base_Organization c ON a.Org= c.ID
        WHERE c.Code=  '{0}' AND a.Effective_IsEffective='1' AND a.Effective_EffectiveDate <= GETDATE() AND a.Effective_DisableDate >  GETDATE()
        UNION ALL
        SELECT DISTINCT  '客户' Type, a.Code, b.Name
        FROM CBO_Customer a
        LEFT JOIN CBO_Customer_Trl b ON a.ID= b.ID AND b.SysMLFlag = 'zh-CN'
        LEFT JOIN Base_Organization c ON a.Org= c.ID
        WHERE c.Code= '{0}' AND a.Effective_IsEffective='1' AND a.Effective_EffectiveDate <= GETDATE() AND a.Effective_DisableDate >  GETDATE()
         UNION ALL
       SELECT '超额领料' Type,
					aa.Code  ,b.Name
				FROM MO_IssueDocType aa  
				LEFT JOIN MO_IssueDocType_Trl b ON aa.ID = b.ID
				AND b.SysMLFlag = 'zh-CN'
				LEFT JOIN Base_Organization c ON aa.Org= c.ID
				WHERE
					c.Code= '{0}'  AND aa.Code NOT IN ('2','3','6','7','10','11','14','15','57','58')  AND aa.Effective_IsEffective='1' AND aa.Effective_EffectiveDate <= GETDATE() AND aa.Effective_DisableDate >  GETDATE()
            )AA WHERE AA.Type='" + TypeName + "'";
            sql = string.Format(sql, OrgCode);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            return dt;
        }

        public static DataTable GetU9Information(string TypeName, string OrgCode)
        {
            string sql = @"SELECT   '' Type, '' Code, '' Name
         UNION ALL  
       SELECT* FROM(
        SELECT DISTINCT  '杂发' Type, b.Code, bb.Name
        FROM InvDoc_MiscShipDocType b
        LEFT JOIN  InvDoc_MiscShipDocType_Trl bb   ON bb.ID= b.ID
        LEFT JOIN Base_Organization c ON b.Org= c.ID
        WHERE c.Code=  '{0}' AND b.Effective_IsEffective='1' AND b.Effective_EffectiveDate <= GETDATE() AND b.Effective_DisableDate >  GETDATE()
        UNION ALL
        SELECT DISTINCT  '杂收' Type, b.Code, bb.Name
         FROM   InvDoc_MiscRcvDocType b
        LEFT JOIN InvDoc_MiscRcvDocType_Trl bb ON b.ID= bb.ID
        LEFT JOIN Base_Organization c ON b.Org= c.ID
        WHERE c.Code= '{0}' AND b.Effective_IsEffective='1' AND b.Effective_EffectiveDate <= GETDATE() AND b.Effective_DisableDate >  GETDATE()
        UNION ALL
        SELECT DISTINCT  '标准出货' Type, b.Code, bb.Name
         FROM   SM_ShipDocType b
        LEFT JOIN SM_ShipDocType_Trl bb ON b.ID= bb.ID
        LEFT JOIN Base_Organization c ON b.Org= c.ID
        WHERE c.Code= '{0}' AND b.Effective_IsEffective='1' AND b.Effective_EffectiveDate <= GETDATE() AND b.Effective_DisableDate >  GETDATE()
        UNION ALL
        SELECT DISTINCT  '部门' Type, a.Code, b.Name
        FROM CBO_Department a
        LEFT JOIN CBO_Department_Trl b ON a.ID= b.ID
        LEFT JOIN Base_Organization c ON a.Org= c.ID
        WHERE c.Code= '{0}'  AND a.Effective_IsEffective='1' AND a.Effective_EffectiveDate <= GETDATE() AND a.Effective_DisableDate >  GETDATE()
        UNION ALL
        SELECT DISTINCT  '项目' Type, a.Code, b.Name
         FROM CBO_Project a
        LEFT JOIN CBO_Project_Trl b ON a.ID= b.ID
        LEFT JOIN Base_Organization c ON a.Org= c.ID
        WHERE c.Code=  '{0}' AND a.Effective_IsEffective='1' AND a.Effective_EffectiveDate <= GETDATE() AND a.Effective_DisableDate >  GETDATE()
        UNION ALL
        SELECT DISTINCT  '客户' Type, a.Code, b.Name
        FROM CBO_Customer a
        LEFT JOIN CBO_Customer_Trl b ON a.ID= b.ID AND b.SysMLFlag = 'zh-CN'
        LEFT JOIN Base_Organization c ON a.Org= c.ID
        WHERE c.Code= '{0}' AND a.Effective_IsEffective='1' AND a.Effective_EffectiveDate <= GETDATE() AND a.Effective_DisableDate >  GETDATE()
         UNION ALL
       SELECT '超额领料' Type,
					aa.Code  ,b.Name
				FROM MO_IssueDocType aa  
				LEFT JOIN MO_IssueDocType_Trl b ON aa.ID = b.ID
				AND b.SysMLFlag = 'zh-CN'
				LEFT JOIN Base_Organization c ON aa.Org= c.ID
				WHERE
					c.Code= '{0}'  AND aa.Code NOT IN ('2','3','6','7','10','11','14','15','57','58')  AND aa.Effective_IsEffective='1' AND aa.Effective_EffectiveDate <= GETDATE() AND aa.Effective_DisableDate >  GETDATE()
            )AA WHERE AA.Type='" + TypeName + "'";
            sql = string.Format(sql, OrgCode);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            return dt;
        }


        public class ReturnSupperEntity
        {
            //成功的话code=2000
            //失败的话code=9999
            public string code;
            public string supplierCode;//信息
            public string msg;//信息
        }

        public static DataTable GetUOMCode(string UOMName)
        {
            string sql = @" select A5.Code from [Base_UOM] as A5 
left join [Base_UOM_Trl] as A6 on (A6.SysMlFlag = 'zh-CN') and A5.ID=A6.ID
 where (A6.[Name] = '" + UOMName + "')";
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());
            return dt;
        }

        public static string GetUOMCodeByID(string ID)
        {
            string sql = @" select A5.Code from [Base_UOM] as A5 
left join [Base_UOM_Trl] as A6 on (A6.SysMlFlag = 'zh-CN') and A5.ID=A6.ID
 where (A5.ID = '" + ID + "')";
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            else
            {
                return null;
            }
        }



        public static string GetPRSupplierCode(string PRCode)
        {
            string sql = @"";
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            else
            {
                return null;
            }
        }

        public static string GetTCCodeByID(string ID)
        {
            string sql = @" select Code from [Base_Currency]
                           where (ID = '" + ID + "')";
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            else
            {
                return null;
            }
        }

        public static string GetProjectCode(long ProID)
        {
            string sql = @" select Code from CBO_Project where ID='" + ProID.ToString() + "'";
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            return null;
        }

        public static string GetProjectID(string ProCode)
        {
            string sql = @" select ID from CBO_Project where Code='" + ProCode.ToString() + "'";
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            return null;
        }

        public static DateTime TimestampToDateTime(long timestamp)
        {
            DateTime dateTimeStart = TimeZone.CurrentTimeZone.ToLocalTime(new DateTime(1970, 1, 1));
            long lTime = timestamp * 10000000;
            TimeSpan nowTimeSpan = new TimeSpan(lTime);
            DateTime resultDateTime = dateTimeStart.Add(nowTimeSpan);
            return resultDateTime;
        }
        public static long ConvertDateTimeToInt(System.DateTime time)
        {
            return long.Parse((time - new DateTime(1970, 1, 1).ToLocalTime()).TotalSeconds.ToString());
        }
        public static string getDBTime()
        {
            DataTable dt = DBhlper.Query("select getdate()", Appconfig.GetU9ConnStr());
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            else
            { return null; }
        }

        public static DataTable getPRProject(long DocID, long DocLineID)
        {
            string sql = @"select A1.Project from PR_PR A 
left join PR_PRLine A1 on A.ID=A1.PR 
where A.ID='{0}' and A1.ID={1}";
            sql = string.Format(sql, DocID, DocLineID);
            DataTable d = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (d != null && d.Rows.Count > 0 && !string.IsNullOrEmpty(d.Rows[0][0].ToString()))
                return d;
            return null;
        }

        public static DataTable getPRProject(string DocNo, int DocLineNo)
        {
            string sql = @"select A1.Project from PR_PR A 
left join PR_PRLine A1 on A.ID=A1.PR 
where A.DocNo='{0}' and A1.DocLineNo={1}";
            sql = string.Format(sql, DocNo, DocLineNo);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt;
            }
            else
            { return null; }
        }
        public static DataTable getPRProject11(string DocNo, int DocLineNo)
        {
            string sql = @"select A1.DescFlexSegments_PubDescSeg11 from PR_PR A 
left join PR_PRLine A1 on A.ID=A1.PR 
where A.DocNo='{0}' and A1.DocLineNo={1}";
            sql = string.Format(sql, DocNo, DocLineNo);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt;
            }
            else
            { return null; }
        }
        public static bool setTotalMny(string DocNo, int DocLineNo)
        {
            try
            {
                string sql = @"update PM_POLine set DescFlexSegments_PrivateDescSeg15=cast((case A.DescFlexSegments_PrivateDescSeg14 when '' then 0 else A.DescFlexSegments_PrivateDescSeg14 end) as decimal(18,2) )+A.TotalMnyAC
                            from PM_POLine A
                            left join PM_PurchaseOrder A1 on A.PurchaseOrder=A1.ID
                            where  A1.DocNo='{0}' and A.DocLineNo={1}";
                sql = string.Format(sql, DocNo, DocLineNo);
                DBhlper.ReaerEXecute(sql, Appconfig.GetU9ConnStr());//修改加工费合计
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }


        public static string GetTaxSchedule(string Code)
        {
            try
            {
                string TaxRuleID = "";
                string taxsql = @"select ID from CBO_TaxSchedule
                                  where Code='{0}'";
                taxsql = string.Format(taxsql, Code);
                DataTable taxdt = DBhlper.Query(taxsql, Appconfig.GetU9ConnStr());
                if (taxdt.Rows.Count != 0)
                {
                    TaxRuleID = taxdt.Rows[0]["ID"].ToString();
                }
                else
                {
                    throw new Exception("未获取到税组合编码：" + Code + "对应ID");
                }
                return TaxRuleID;
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
        }


        public static long getTaxSchedule(string Customer, string OrgID)
        {
            try
            {
                string sql = "select TaxSchedule from CBO_Customer where Code='{0}' AND Org='{1}'";
                sql = string.Format(sql, Customer, OrgID);
                DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());//查询U9客户税组合
                if (dt != null && dt.Rows.Count > 0 && !string.IsNullOrEmpty(dt.Rows[0][0].ToString()))
                {
                    return long.Parse(dt.Rows[0][0].ToString());
                }
                return 0;
            }
            catch (Exception ex)
            {
                return 0;
            }
        }

        public static bool setPOProject(string DocNo, int DocLineNo, long project)
        {
            string sql = @"update PM_POLine set Project={0} 
where ID=(select A.ID from PM_POLine A left join PM_PurchaseOrder A1 on A1.ID=A.PurchaseOrder where A1.DocNo='{1}' and A.DocLineNo={2})";
            sql = string.Format(sql, project, DocNo, DocLineNo);

            string sql1 = @"update PM_POShipLine set project={0} 
            where ID=(select A2.ID from PM_PurchaseOrder A 
            left join PM_POLine A1 on A.ID=A1.PurchaseOrder 
             left join PM_POShipLine A2 on A2.POLine=A1.ID 
             where A.DocNo='{1}' and A1.DocLineNo={2})";
            sql1 = string.Format(sql1, project, DocNo, DocLineNo);
            bool m = DBhlper.ReaerEXecute(sql, Appconfig.GetU9ConnStr());//修改行项目号
            try
            {
                DBhlper.EXecute(sql1, Appconfig.GetU9ConnStr());//修改计划行项目号
            }
            catch { }
            return m;
        }

        public static bool setPOShipProject(string DocNo, int DocLineNo, long project)
        {
            try
            {
                string sql = @"update PM_POShipLine set Project={0}  SrcDocPRLine 
where ID=(select A.ID from PM_POLine A left join PM_PurchaseOrder A1 on A1.ID=A.PurchaseOrder where A1.DocNo='{1}' and A.DocLineNo={2})";
                sql = string.Format(sql, project, DocNo, DocLineNo);
                DBhlper.EXecute(sql, Appconfig.GetU9ConnStr());
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
            
        }

        public static string getOperCodeByName(string name)
        {
            string sql = @"select a.ID,a.Code,Name from CBO_Operators a
left join CBO_Operators_Trl b on a.ID=b.ID
left join CBO_Department c on a.Dept=c.ID
where  c.code='02' and a.OperatorType=1 and name='{0}' ";//销售部人员
            sql = string.Format(sql, name);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["ID"].ToString();
        }


        public static string getistaxprice(string name, string org)
        {
            string sql = @"select (case when IsTaxPrice=1 then '1'else'2'end) IsTaxPrice from CBO_Customer where code='{0}' ";//销售部人员
            sql = string.Format(sql, name);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["IsTaxPrice"].ToString();
        }

        public static string getValueCodeByName(string name, long value)
        {
            string sql = @"select  A.[ID], A.[Code], A1.[Name], A.[DependantCode] from  Base_DefineValue as A  
left join [Base_DefineValue_Trl] as A1 on (A1.SysMlFlag = 'zh-CN') and (A.[ID] = A1.[ID]) 
where  (A.[ValueSetDef] = {1}) and A1.[Name]='{0}'";//销售部人员
            sql = string.Format(sql, name, value);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["Code"].ToString();
        }



        public static void WriteLogFile_bak20191021(string input, string txtName)
        {
            try
            {
                string logAdress = ConfigurationManager.AppSettings["logAdress"].ToString();
                /**/
                //string filename = logAdress + "\\Log\\";
                //FileInfo finfo1 = new FileInfo(filename);

                //if (!finfo1.Exists)
                //{
                //    FileStream fs;
                //    fs = File.Create(filename);
                //    fs.Close();
                //}




                ///指定日志文件的目录
                string fname = logAdress + "\\Log\\" + txtName + ".txt";
                /**/
                ///定义文件信息对象

                FileInfo finfo = new FileInfo(fname);

                if (!finfo.Exists)
                {
                    FileStream fs;
                    fs = File.Create(fname);
                    fs.Close();
                    finfo = new FileInfo(fname);
                }

                /**/
                ///判断文件是否存在以及是否大于2K
                if (finfo.Length > 1024 * 1024 * 10)
                {
                    /**/
                    ///文件超过10MB则重命名
                    File.Move(logAdress + "\\Log\\" + txtName + ".txt", Directory.GetCurrentDirectory() + DateTime.Now.TimeOfDay + "\\Log\\" + txtName + ".txt");
                    /**/
                    ///删除该文件
                    //finfo.Delete();
                }
                //finfo.AppendText();
                /**/
                ///创建只写文件流

                using (FileStream fs = finfo.OpenWrite())
                {
                    /**/
                    ///根据上面创建的文件流创建写数据流
                    StreamWriter w = new StreamWriter(fs);

                    /**/
                    ///设置写数据流的起始位置为文件流的末尾
                    w.BaseStream.Seek(0, SeekOrigin.End);

                    w.Write("\n\r" + " " + "\n\r");
                    w.Write("\n\r" + "*****************Start*****************" + "\n\r", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                    /**/
                    ///写入当前系统时间并换行
                    w.Write("\n\r" + "{0}" + "\n\r", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                    /**/
                    ///写入日志内容并换行
                    w.Write("\n\r" + input + "\n\r");

                    /**/
                    ///写入------------------------------------“并换行
                    w.Write("\n\r" + "------------------END------------------------" + "\n\r");

                    /**/
                    ///清空缓冲区内容，并把缓冲区内容写入基础流
                    w.Flush();

                    /**/
                    ///关闭写数据流
                    w.Close();
                }
            }
            catch (Exception ex)
            { throw ex; }

        }


        public static void WriteLogFile(string input, string txtName)
        {
            try
            {
                string logAdress = ConfigurationManager.AppSettings["logAdress"].ToString() + "\\Log\\";
                if (!System.IO.Directory.Exists(logAdress))
                {
                    System.IO.Directory.CreateDirectory(logAdress);//不存在就创建目录   
                }

                string adress = logAdress + txtName;
                if (!System.IO.Directory.Exists(adress))
                {
                    System.IO.Directory.CreateDirectory(adress);//不存在就创建目录   
                }


                // string logAdress = ConfigurationManager.AppSettings["logAdress"].ToString();
                /**/
                ///指定日志文件的目录
                string fname = adress + "\\" + "log" + DateTime.Now.ToString("yy-MM-dd") + ".txt";
                /**/
                ///定义文件信息对象

                FileInfo finfo = new FileInfo(fname);

                if (!finfo.Exists)
                {
                    FileStream fs;
                    fs = File.Create(fname);
                    fs.Close();
                    finfo = new FileInfo(fname);
                }

                /**/
                ///判断文件是否存在以及是否大于2K
                if (finfo.Length > 1024 * 1024 * 10)
                {
                    /**/
                    ///文件超过10MB则重命名
                    File.Move(fname, adress + "\\" + "log" + DateTime.Now.ToString("yy-MM-dd") + DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second + ".txt");
                    /**/
                    ///删除该文件
                    //finfo.Delete();
                }
                //finfo.AppendText();
                /**/
                ///创建只写文件流

                using (FileStream fs = finfo.OpenWrite())
                {
                    /**/
                    ///根据上面创建的文件流创建写数据流
                    StreamWriter w = new StreamWriter(fs);

                    /**/
                    ///设置写数据流的起始位置为文件流的末尾
                    w.BaseStream.Seek(0, SeekOrigin.End);

                    w.WriteLine("*****************Start*****************");
                    w.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    /**/
                    ///写入当前系统时间并换行

                    /**/
                    ///写入日志内容并换行
                    w.WriteLine(input);

                    /**/
                    ///写入------------------------------------“并换行
                    w.WriteLine("------------------END------------------------");

                    /**/
                    ///清空缓冲区内容，并把缓冲区内容写入基础流
                    w.Flush();

                    /**/
                    ///关闭写数据流
                    w.Close();
                }
            }
            catch (Exception ex)
            { throw ex; }

        }



        public static string Serialize(DescData data)
        {
            return JsonConvert.SerializeObject(data);
        }
        public static DescData DeSerialize(string str)
        {
            return JsonConvert.DeserializeObject<DescData>(str); ;
        }


        /// <summary>
        /// 验证料号是否存在
        /// </summary>
        /// <param name="itemCode"></param>
        /// <returns></returns>
        public static int IsExistItem(string itemCode)
        {
            string sql = @"select count(*) from CBO_ItemMaster where code='" + itemCode + "' ";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());

            return dt.Rows.Count;
        }

        /// <summary>
        /// 验证库位是否存在
        /// </summary>
        /// <param name="cPos"></param>
        /// <returns></returns>
        public static int IsExistcPos(string BinCode)
        {
            string sql = @"select count(*) from CBO_Bin where code='" + BinCode + "'";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());

            return dt.Rows.Count;
        }
        /// <summary>
        /// 获取仓库编码(根据库位编码)
        /// </summary>
        /// <param name="BinCode"></param>
        /// <returns></returns>
        public static string GetWhCodeByBin(string BinCode)
        {
            string sql = @"select w.code as Code from CBO_Bin  b left join cbo_wh w on b.warehouse=w.id where b.code='" + BinCode + "'";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["Code"].ToString();
        }
        /// <summary>
        /// 获取单位ID
        /// </summary>
        /// <param name="itemCode"></param>
        /// <returns></returns>
        public static string GetMaterialUOM(string ItemCode)
        {
            string sql = @"select MaterialOutUOM from CBO_ItemMaster where code='" + ItemCode + "'";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["MaterialOutUOM"].ToString();
        }
        /// <summary>
        /// 获取mOPick ID
        /// </summary>
        /// <param name="itemCode"></param>
        /// <returns></returns>
        public static string GetMoPickListID(string MoCode, string ItemCode, string OrgID)
        {
            string sql = @"select top 1 id from MO_MOPickList where mo=(select top 1 id from mo_mo where docno='" + MoCode + "' and Org='" + OrgID + @"') 
                           and ItemMaster=(select top 1 id from CBO_ItemMaster where code='" + ItemCode + "' and Org='" + OrgID + @"')";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["id"].ToString();
        }

        /// <summary>
        /// 获取mOPick ID(存在相同备料的情况)
        /// </summary>
        /// <param name="itemCode"></param>
        /// <returns></returns>
        public static string GetMoPickListIDWithLine(string MoCode, string ItemCode, string SubLineNo, string OrgID)
        {
            string sql = @"select top 1 id from MO_MOPickList where mo=(select top 1 id from mo_mo where docno='" + MoCode + "' and Org='" + OrgID + @"') 
                           and ItemMaster=(select top 1 id from CBO_ItemMaster where code='" + ItemCode + "' and Org='" + OrgID + @"')
                           and DocLineNO='" + SubLineNo + "'";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["id"].ToString();
        }

        /// <summary>
        /// 根据母料获取子料集合
        /// </summary>
        /// <param name="MCode"></param>
        /// <returns></returns>
        public static DataTable GetCcodeListByMcode(string MCode)
        {
            string sql = @"SELECT
	                            A.[UsageQty] AS useQty,
	                            A3.[Code] AS ccode,
	                            A10.Code AS WhCode,
	                            A7.Code AS UnitCode,
	                            A12.Code AS BinCode
                            FROM
	                            CBO_BOMComponent AS A (NOLOCK)
                            LEFT JOIN [CBO_BOMMaster] AS A1 (NOLOCK) ON (A.[BOMMaster] = A1.[ID])
                            LEFT JOIN [CBO_ItemMaster] AS A2 (NOLOCK) ON (A1.[ItemMaster] = A2.[ID])
                            LEFT JOIN [CBO_ItemMaster] AS A3 (NOLOCK) ON (A.[ItemMaster] = A3.[ID])
                            LEFT JOIN [Base_UOM] AS A7 (NOLOCK) ON (A3.[CostUOM] = A7.[ID])
                            LEFT JOIN [CBO_InventoryInfo] AS A9 (NOLOCK) ON (A3.[InventoryInfo] = A9.[ID])
                            LEFT JOIN [CBO_Wh] AS A10 (NOLOCK) ON (A9.[Warehouse] = A10.[ID])
                            LEFT JOIN [CBO_Bin] AS A12 (NOLOCK) ON (A9.[Bin] = A12.[ID])
                            WHERE
	                            1 = 1
                            AND IsEffective = 1
                            AND A2.[Code] = '" + MCode + "' ";

            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());

            return dt;
        }


        /// <summary>
        /// 工单备料表获取-已发放数量-领料未发数量
        /// </summary>
        /// <param name="MCode"></param>
        /// <returns></returns>
        public static DataTable GetQtyBymOPickList(string MoCode, string ccode)
        {
            string sql = @" SELECT
	                            C.ActualReqQty
                            FROM
	                            MO_MOPickList C
                            LEFT JOIN MO_IssueDoc a ON A.SourceDoc_SrcDocID = C.MO
                            LEFT JOIN MO_IssueDocLine b ON b.IssueDoc = a.ID
                            LEFT JOIN MO_MO D ON D.ID = c.MO
LEFT JOIN CBO_ItemMaster e ON c.ItemMaster=e.ID
                            WHERE
	                            d.DocNo = '" + MoCode + "'    AND e.Code='" + ccode + "'   ";

            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());

            return dt;
        }




        /// <summary>
        /// 根据单据
        /// </summary>
        /// <param name="DocCode"></param>
        /// <returns></returns>
        public static string GetBusinessType(string DocCode)
        {
            string sql = @"SELECT   BusinessType   from MO_IssueDocType WHERE Code='" + DocCode + "' ";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["BusinessType"].ToString();
        }


        public static string GetMOQty(string DocCode)
        {
            string sql = @"SELECT  a.ProductQty FROM  MO_MO a WHERE DocNo='" + DocCode + "' ";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0) return null;
            else return dt.Rows[0]["ProductQty"].ToString();
        }
        public static DataTable GetMOQtyAndID(string DocCode, string Org)
        {
            string sql = @"SELECT  a.ProductQty,ID FROM  MO_MO a WHERE DocNo='" + DocCode + "' and Org ='" + Org + "'";
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            return dt;
        }


        public static Decimal GetMOSendQty(string MOCode)
        {

            //            string sql = @"SELECT
            //	                             SUM( b.ApplySetQty) as SendQty
            //                            FROM
            //	                            MO_IssueDoc a
            //                            LEFT JOIN MO_IssueDocLine b ON b.IssueDoc = a.ID  
            //                            WHERE
            //	                            a.SourceDoc_SrcDocNo = '" + MOCode + "'   ";
            string sql = @"SELECT
	                             SUM(b.IssuedQty) as SendQty
                            FROM
	                            MO_IssueDoc a
                            LEFT JOIN MO_IssueDocLine b ON b.IssueDoc = a.ID  
                            WHERE
	                            a.SourceDoc_SrcDocNo = '" + MOCode + "'   "; //AND a.DocState=2 
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt == null || dt.Rows.Count == 0)
            {
                return 0;
            }
            else
            {
                if (!string.IsNullOrWhiteSpace(dt.Rows[0]["SendQty"].ToString()))
                {
                    return Decimal.Parse(dt.Rows[0]["SendQty"].ToString());
                }
                else
                    return 0;
            }
        }


        /// <summary>
        /// 验证库工单中备料表中数据是否存在
        /// </summary>
        /// <param name="cPos"></param>
        /// <returns></returns>
        public static int IsExistcPickList(string MoCode)
        {
            string sql = @"SELECT  COUNT (*)   FROM   MO_MOPickList
                        WHERE      mo = (  SELECT   TOP 1 id  FROM      mo_mo     WHERE   docno = '" + MoCode + "' ) ";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());

            return dt.Rows.Count;
        }

        /// <summary>
        /// 只取11 和 12物料
        /// </summary>
        /// <param name="DocCode"></param>
        /// <returns></returns>
        public static DataTable GetPickList(string MoCode)
        {
            string sql = @"SELECT
	                            b.Code ccode,
	                            a.QPA useQty,
	                            A7.Code UnitCode
                            FROM
	                            MO_MOPickList a
                            LEFT JOIN CBO_ItemMaster b ON a.ItemMaster = b.ID
                            LEFT JOIN [Base_UOM] AS A7 (NOLOCK) ON (A.[CostUOM] = A7.[ID])
                            WHERE
1=1 
 AND  (b.Code LIKE '11%' OR b.Code LIKE   '12%' ) 
and 

	                            mo = (
		                            SELECT
			                            TOP 1 id
		                            FROM
			                            mo_mo
		                            WHERE
			                            docno = '" + MoCode + "'  ) ";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            return dt;
        }


        /// <summary>
        /// 只取11 和 12物料
        /// </summary>
        /// <param name="DocCode"></param>
        /// <returns></returns>
        public static DataTable GetPickListNew(string MoCode, string Ccode)
        {
            string sql = @"SELECT
	                            b.Code ccode,
	                            a.QPA useQty,
	                            A7.Code UnitCode
                            FROM
	                            MO_MOPickList a
                            LEFT JOIN CBO_ItemMaster b ON a.ItemMaster = b.ID
                            LEFT JOIN [Base_UOM] AS A7 (NOLOCK) ON (A.[CostUOM] = A7.[ID])
                            WHERE
                                1=1 
 AND  (b.Code='" + Ccode + "' ) and    mo = ( SELECT      TOP 1 id   FROM      mo_mo  WHERE   docno = '" + MoCode + "'  ) ";
            sql = string.Format(sql);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            return dt;
        }



        public static string GetItemBin(string itemCode)
        {
            string sql = @" select  distinct top 1  bininfo_code 
                              from invtrans_whqoh 
                             where iteminfo_itemcode ='" + itemCode + @"' 
                               --and storeqty>0
                               and storeqty = (select distinct min(storeqty) 
                                                from invtrans_whqoh 
          where iteminfo_itemcode ='" + itemCode + "' and storeqty > 0  )";
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());

            //DataSet ds = DBhlper.ExecuteDataset(Appconfig.GetU9ConnStr(), CommandType.Text, sql);

            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            else
            {
                sql = @" select distinct top 1 h.bininfo_code
                                   from invtrans_whqoh z inner join invtrans_translinebin h on z.BizKey=h.BizKey 
                                  inner join invtrans_transline p on p.id=h.transline 
                                 where z.iteminfo_itemcode ='" + itemCode + @"'
                                  --and storeqty=0
                                   and p.createdon = (select distinct max(createdon) 
                                                       from invtrans_transline 
            where iteminfo_itemcode='" + itemCode + "')";
                //ds = DBHelper.ExecuteDataset(u9ConnectString, CommandType.Text, sql);
                dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());

                if (dt.Rows.Count > 0)
                {
                    return dt.Rows[0][0].ToString();
                }
                else
                {
                    return "";
                }
            }
        }

        public static string GetItemWh(string BinCode)
        {
            string sql = @"SELECT DISTINCT b.Code FROM CBO_Bin a
LEFT JOIN CBO_Wh b ON a.Warehouse=b.ID
LEFT JOIN InvTrans_WhQoh c ON c.BinInfo_Code=a.Code AND c.Wh=b.ID
 WHERE a.Code='" + BinCode + "' AND c.StoreQty>0";
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());


            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            else
            {
                return "";
            }
        }


        public static string GetItemWhID(string BinCode)
        {
            string sql = @"SELECT DISTINCT b.ID FROM CBO_Bin a
LEFT JOIN CBO_Wh b ON a.Warehouse=b.ID
LEFT JOIN InvTrans_WhQoh c ON c.BinInfo_Code=a.Code AND c.Wh=b.ID
 WHERE a.ID='" + BinCode + "' AND c.StoreQty>0";
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());


            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            else
            {
                return "";
            }
        }

        public static bool WHLotCheckState(string WHCode, string OrgCode)
        {
            string sql = @"select IsLot from CBO_WH A
                           LEFT JOIN Base_Organization B
                           ON B.ID=A.ORG
                           where A.Code='{0}' AND B.CODE='{1}'";
            sql = string.Format(sql, WHCode, OrgCode);
            DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
            if (dt != null && dt.Rows.Count > 0)
            {
                if (dt.Rows[0]["IsLot"].ToString() == "True")
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }

        public static void UpdateDocApproveMan(string DocNo, string OrgID, string UserCode, string PrimaryTableName, string SecondaryTableName)
        {
            try
            {
                string usersql = @"select ID,Name from Base_User
                                       where Code='WMS'";
                string docsql = @"select ID from {2}
                                  WHERE DocNo='{0}' AND Org='{1}'";
                docsql = string.Format(docsql, DocNo, OrgID, PrimaryTableName);
                DataTable userdt = DBhlper.Query(usersql, Appconfig.GetU9ConnStr());
                DataTable docdt = DBhlper.Query(docsql, Appconfig.GetU9ConnStr());
                if (userdt.Rows.Count != 0 && docdt.Rows.Count != 0)
                {
                    string updatesql = @"UPDATE {2} set ModifiedBy='{0}',ApproveMan='{0}'
                                         where ID='{1}'
                                         UPDATE {3} set ModifiedBy='{0}',ApproveMan='{0}'
                                         where {4}='{1}'";
                    if (PrimaryTableName == "PM_IssueDoc")
                    {
                        updatesql = string.Format(updatesql, userdt.Rows[0]["Name"].ToString(), docdt.Rows[0]["ID"].ToString(),
                                                                     PrimaryTableName, SecondaryTableName
                                                                     , PrimaryTableName.Split('_')[0].ToString() + PrimaryTableName.Split('_')[1].ToString());
                    }
                    else
                    {
                        updatesql = string.Format(updatesql, userdt.Rows[0]["Name"].ToString(), docdt.Rows[0]["ID"].ToString(),
                                                  PrimaryTableName, SecondaryTableName, PrimaryTableName.Split('_')[1].ToString());
                    }
                    DBhlper.EXecute(updatesql, Appconfig.GetU9ConnStr());
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


        //public static string GetPRID(string PRCode)
        //{
        //    string sql = @"select ID,a.Status from PR_PR a where   DocNo='{0}' ";
        //    sql = string.Format(sql, PRCode);
        //    DataTable dt = DBhlper.Query(sql, GetU9ConnStr());
        //    if (dt != null && dt.Rows.Count > 0)
        //    { return dt.Rows[0][0].ToString(); }
        //    return null;
        //}

        public static DataTable GetPRID(string PRCode)
        {
            string sql = @"select ID,a.Status from PR_PR a where   DocNo='{0}' ";
            sql = string.Format(sql, PRCode);
            DataTable dt = DBhlper.Query(sql, GetU9ConnStr());
            return dt;
        }


    }
    public class DescData
    {
        public string WsCrack_ZJ { get; set; }//无损探伤(铸件)
        public string WsCrack_DJ { get; set; }//无损探伤(锻件)
        public string Delivery_Nature { get; set; }//交期性质
        public bool Ispecial_Paint { get; set; }//是否特殊油漆
        public string Special_Ask { get; set; }//其他特殊要求
        public bool IsDegreasing { get; set; }//是否脱油脱脂
        public string Tap_Model { get; set; }//阀门形式
        public string Sy_Medium { get; set; }//试压介质
        public string Sy_Standard { get; set; }//试压标准
        public string Handle_Press { get; set; }//操作压力
        public string Switch_Time { get; set; }//开关时间
    }

    public class OperationResult_PRState
    {
        /// <summary>
        /// 结果状态,默认值false
        /// </summary>
        public bool IsSuccess { get; set; }

        /// <summary>
        ///处理消息
        /// </summary>
        public string MESSAGE { get; set; }

        /// <summary>
        /// 输出ZPList
        /// </summary>
        public string PRCode { get; set; }

    }

    public class CommonReturnEntity
    {
        //成功的话code=2000
        //失败的话code=9999
        //public string code;
        public bool Success;


        public string DocNO;

        public string Message;//信息

        public string Data;
    }
}
