三升：小诸葛中关于金蝶云的查询，由写死改为通过变量的方式可以批量修改

duhe

2025-04-28 dcdd3ba0ee99b039abeac325f9a18ed0f51ef344

三升：小诸葛中关于金蝶云的查询，由写死改为通过变量的方式可以批量修改

 WebAPI/Controllers/Êý¾Ýͬ²½/СÖî¸ðͬ²½/XZG_DataSynchronizationController.cs

@@ -827,10 +827,10 @@
                                ,'CLD-ERP导入' HRemark
                                ,unit.FNAME HUnitName
                                ,convert(varchar(100),a.FMODIFYDATE,20) HUpdateDate
                            from AIS20210811135644..T_BD_MATERIAL a with(nolock)
                            inner join AIS20210811135644..T_BD_MATERIAL_L l with(nolock) on a.FMATERIALID=l.FMATERIALID 
                            inner join AIS20210811135644..T_BD_MATERIALSTOCK s with(nolock) on a.FMATERIALID=s.FMATERIALID 
                            inner join AIS20210811135644..T_BD_Unit_L unit with(nolock) on isnull(s.FSTOREUNITID,0) = unit.FUNITID
                            from " +  erpDatabaseName + @"T_BD_MATERIAL a with(nolock)
                            inner join " +  erpDatabaseName + @"T_BD_MATERIAL_L l with(nolock) on a.FMATERIALID=l.FMATERIALID 
                            inner join " +  erpDatabaseName + @"T_BD_MATERIALSTOCK s with(nolock) on a.FMATERIALID=s.FMATERIALID 
                            inner join " +  erpDatabaseName + @"T_BD_Unit_L unit with(nolock) on isnull(s.FSTOREUNITID,0) = unit.FUNITID
                            Where l.FLOCALEID=2052
                            and unit.FLOCALEID = 2052
                            and a.FFORBIDSTATUS='A'
@@ -840,7 +840,7 @@
                                     or
                                     convert(varchar(100),a.FMODIFYDATE,23) >= convert(varchar(100),DATEADD(day,-7,getdate()),23)
                                )";
                DataSet ds = oCN.RunProcReturn(sql, "AIS20210811135644..T_BD_MATERIAL");
                DataSet ds = oCN.RunProcReturn(sql, "" +  erpDatabaseName + @"T_BD_MATERIAL");
                if (ds != null && ds.Tables.Count > 0)
                {
                    for(int i = 0; i < ds.Tables[0].Rows.Count; i++)
@@ -1112,11 +1112,11 @@
                                ,a.FBILLNO è®¢å•ç¼–号
                                ,ISNULL(convert(varchar(100),c.FSTARTDATE ,20),'') å¼€å·¥æ—¥æœŸ
                                ,convert(varchar(100),a.FMODIFYDATE,20) ä¿®æ”¹æ—¥æœŸ
                            from AIS20210811135644..T_PRD_MO as a
                            inner join AIS20210811135644..T_PRD_MOENTRY as b with(nolock) on a.FID = b.FID
                            inner join AIS20210811135644..T_PRD_MOENTRY_A as c with(nolock) on b.FID = c.FID and b.FEntryID = c.FEntryID
                            inner join AIS20210811135644..T_BD_MATERIAL as m with(nolock) on b.FMATERIALID = m.FMATERIALID
                            inner join AIS20210811135644..T_BD_MATERIAL_L as m_l with(nolock) on m.FMATERIALID=m_l.FMATERIALID 
                            from " +  erpDatabaseName + @"T_PRD_MO as a
                            inner join " +  erpDatabaseName + @"T_PRD_MOENTRY as b with(nolock) on a.FID = b.FID
                            inner join " +  erpDatabaseName + @"T_PRD_MOENTRY_A as c with(nolock) on b.FID = c.FID and b.FEntryID = c.FEntryID
                            inner join " +  erpDatabaseName + @"T_BD_MATERIAL as m with(nolock) on b.FMATERIALID = m.FMATERIALID
                            inner join " +  erpDatabaseName + @"T_BD_MATERIAL_L as m_l with(nolock) on m.FMATERIALID=m_l.FMATERIALID 
                            where m_l.FLOCALEID=2052
                            and isnull(a.FAPPROVERID,0)<> 0                --已经审核
                            and isnull(c.fstatus,0) in (4)                    --业务状态（1:计划；2:计划确认；3:下达；4:开工；5:完工；6:结案）
@@ -1127,7 +1127,7 @@
                                convert(varchar(100),c.fstartdate ,23) >= convert(varchar(100),dateadd(day,-7,getdate()),23)
                                )
                            --and cast(a.FBILLNO as varchar(100)) + '_' + cast(b.FSEQ as varchar(10)) = 'MO000269_1'";
                DataSet ds = oCN.RunProcReturn(sql, "AIS20210811135644..T_PRD_MO");
                DataSet ds = oCN.RunProcReturn(sql, "" +  erpDatabaseName + @"T_PRD_MO");
                if (ds != null && ds.Tables.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
@@ -1302,8 +1302,8 @@
                {
                    for(int j=0;j< iCMOReportListResponse.data[i].data.Count; j++)
                    {
                        string sql = "select * from AIS20210811135644..T_PRD_MORPT where FBillNo = '" + iCMOReportListResponse.data[i].data[j].produceReportId + "'";
                        DataSet ds_erp = oCN.RunProcReturn(sql, "AIS20210811135644..T_PRD_MORPT");
                        string sql = "select * from " +  erpDatabaseName + @"T_PRD_MORPT where FBillNo = '" + iCMOReportListResponse.data[i].data[j].produceReportId + "'";
                        DataSet ds_erp = oCN.RunProcReturn(sql, "" +  erpDatabaseName + @"T_PRD_MORPT");
                        if(ds_erp!=null&ds_erp.Tables[0].Rows.Count == 0)
                        {
                            iCMOReportList.Add(iCMOReportListResponse.data[i].data[j]);
@@ -1423,13 +1423,13 @@

                    string sql_judgeICMOStatus = @"select distinct 
                                                    mo.FBILLNO 
                                                from AIS20210811135644..T_PRD_MO mo
                                                inner join AIS20210811135644..T_PRD_MOENTRY_A a on mo.FID=a.FID 
                                                from " +  erpDatabaseName + @"T_PRD_MO mo
                                                inner join " +  erpDatabaseName + @"T_PRD_MOENTRY_A a on mo.FID=a.FID 
                                                where a.FSTATUS in (5,6)
                                                and mo.FBILLNO = '" + HICMOBillNo + "' " +
                                                "and a.FENTRYID = " + HICMOEntryID;
                    DataSet Ds2;
                    Ds2 = oCN.RunProcReturn(sql_judgeICMOStatus, "AIS20210811135644..T_PRD_MO");
                    Ds2 = oCN.RunProcReturn(sql_judgeICMOStatus, "" +  erpDatabaseName + @"T_PRD_MO");
                    if (Ds2 != null && Ds2.Tables[0].Rows.Count > 0)
                    {
                        sErrMsg = "生产订单【 " + HICMOBillNo + "】业务状态已完工不允许汇报！";
@@ -1640,12 +1640,12 @@
                                ,isnull(moSub.FSALEORDERENTRYSEQ,0) FREQENTRYSEQ
                                ,isnull(moSub.FSALEORDERENTRYID,0) FREQENTRYID
                                ,isnull(moSubA.FINSTOCKTYPE,'1') FINSTOCKTYPE
                            from AIS20210811135644..T_PRD_MO mo with(nolock)
                            left join AIS20210811135644..T_PRD_MOENTRY moSub with(nolock) on mo.FID=moSub.FID
                            left join AIS20210811135644..T_PRD_MOENTRY_A moSubA with(nolock) on moSubA.FID=moSub.FID and moSubA.FENTRYID=moSub.FENTRYID
                            from " +  erpDatabaseName + @"T_PRD_MO mo with(nolock)
                            left join " +  erpDatabaseName + @"T_PRD_MOENTRY moSub with(nolock) on mo.FID=moSub.FID
                            left join " +  erpDatabaseName + @"T_PRD_MOENTRY_A moSubA with(nolock) on moSubA.FID=moSub.FID and moSubA.FENTRYID=moSub.FENTRYID
                            left join Xt_ORGANIZATIONS org1 with(nolock) on mo.FPRDORGID=org1.HItemID            --生产组织
                            left join xt_ORGANIZATIONS org2 with(nolock) on moSubA.FINSTOCKOWNERID=org2.HItemID        --入库货主
                            left join AIS20210811135644..t_BD_MaterialProduce mp with(nolock) on moSub.FMATERIALID=mp.FMATERIALID    --物料生产信息
                            left join " +  erpDatabaseName + @"t_BD_MaterialProduce mp with(nolock) on moSub.FMATERIALID=mp.FMATERIALID    --物料生产信息
                            left join Gy_Unit as unit with(nolock) on moSub.FUNITID = unit.HItemID
                            left join Gy_Unit as unit1 with(nolock) on moSub.FBASEUNITID = unit1.HItemID
                            left join Gy_Property pr on moSub.FAUXPROPID = pr.HItemID
@@ -1653,7 +1653,7 @@
                            where 
                            mo.FBillNo = '" + HICMOBillNo + "' " +
                                " and moSub.FENTRYID = " + HICMOEntryID;
                DataSet jsonData = oCN.RunProcReturn(sql1, "AIS20210811135644..T_PRD_MO");
                DataSet jsonData = oCN.RunProcReturn(sql1, "" +  erpDatabaseName + @"T_PRD_MO");
                if (jsonData == null || jsonData.Tables[0].Rows.Count == 0)
                {
                    return "";
@@ -1706,7 +1706,7 @@
                //获取生产汇报类型
                string sql2 = @"select top(1) 
                                FNUMBER FREPORTTYPE 
                            from AIS20210811135644..T_PRD_REPORTTYPE with(nolock) 
                            from " +  erpDatabaseName + @"T_PRD_REPORTTYPE with(nolock) 
                            Where FUSEORGID = " + FPRDORGID +
                                " and FISDEFREPTYPE=1";
                DataSet jsonData2 = oCN.RunProcReturn(sql2, "T_PRD_REPORTTYPE");