예제 #1
0
        static void addBodyOne(Dictionary <object, object> SQLString, SGMRCC model)
        {
            StringBuilder strSql = new StringBuilder( );

            strSql.Append("INSERT INTO SGMRCC (");
            strSql.Append("RCC001,RCC002,RCC003,RCC004,RCC005,RCC006,RCC007,RCC008,RCC010,RCC011,RCC022,RCC028) ");
            strSql.Append("VALUES (");
            strSql.Append("@RCC001,@RCC002,@RCC003,@RCC004,@RCC005,@RCC006,@RCC007,@RCC008,@RCC010,@RCC011,@RCC022,@RCC028) ");
            SqlParameter [] parameter =
            {
                new SqlParameter("@RCC001", SqlDbType.VarChar),
                new SqlParameter("@RCC002", SqlDbType.VarChar),
                new SqlParameter("@RCC003", SqlDbType.VarChar),
                new SqlParameter("@RCC004", SqlDbType.VarChar),
                new SqlParameter("@RCC005", SqlDbType.VarChar),
                new SqlParameter("@RCC006", SqlDbType.Decimal),
                new SqlParameter("@RCC007", SqlDbType.Decimal),
                new SqlParameter("@RCC008", SqlDbType.VarChar),
                new SqlParameter("@RCC010", SqlDbType.VarChar),
                new SqlParameter("@RCC011", SqlDbType.VarChar),
                new SqlParameter("@RCC022", SqlDbType.VarChar),
                new SqlParameter("@RCC028", SqlDbType.VarChar)
            };
            parameter [0].Value  = model.RCC001;
            parameter [1].Value  = model.RCC002;
            parameter [2].Value  = model.RCC003;
            parameter [3].Value  = model.RCC004;
            parameter [4].Value  = model.RCC005;
            parameter [5].Value  = model.RCC006;
            parameter [6].Value  = model.RCC007;
            parameter [7].Value  = model.RCC008;
            parameter [8].Value  = model.RCC010;
            parameter [9].Value  = model.RCC011;
            parameter [10].Value = model.RCC022;
            parameter [11].Value = model.RCC028;

            SQLString.Add(strSql, parameter);
        }
예제 #2
0
        /// <summary>
        /// 生成生产入库单
        /// </summary>
        /// <param name="SQLString"></param>
        /// <param name="table"></param>
        /// <param name="code"></param>
        /// <returns></returns>
        public static Dictionary <object, object> GenerateSGM(Dictionary <object, object> SQLString, StringBuilder strSql, string code, string department)
        {
            DataTable table = SqlHelper.ExecuteDataTable(strSql.ToString( ));

            if (table == null || table.Rows.Count < 1)
            {
                return(null);
            }

            SGMRCA header = new SGMRCA( );

            header.RCA001 = cateGory;
            header.RCA002 = code;
            header.RCA004 = UserInfoMation.sysTime.ToString("yyyyMMdd");
            header.RCA005 = "DS";
            header.RCA006 = department;
            header.RCA016 = "F";
            header.RCA019 = "F";
            add(SQLString, header);

            SGMRCC bodyOne = new SGMRCC( );

            bodyOne.RCC001 = cateGory;
            bodyOne.RCC002 = code;
            bodyOne.RCC003 = source;
            bodyOne.RCC007 = 0;
            bodyOne.RCC008 = batchNum;
            bodyOne.RCC011 = "000";

            DataTable tableOne;

            SGMRCB bodyTwo = new SGMRCB( );

            bodyTwo.RCB001 = cateGory;
            bodyTwo.RCB002 = code;
            bodyTwo.RCB019 = batchNum;
            bodyTwo.RCB021 = source;
            bodyTwo.RCB023 = "000";

            int i = 0;

            foreach (DataRow row in table.Rows)
            {
                i++;
                bodyOne.RCC004 = row ["ANN002"].ToString( );
                bodyOne.RCC005 = row ["DDA001"].ToString( );
                bodyOne.RCC006 = string.IsNullOrEmpty(row ["ANN009"].ToString( )) == true ? 0 : Convert.ToDecimal(row ["ANN009"]);
                bodyOne.RCC010 = row ["ANN003"].ToString( );
                bodyOne.RCC022 = i.ToString( ).PadLeft(3, '0');
                bodyOne.RCC028 = row ["ANN005"].ToString( );
                addBodyOne(SQLString, bodyOne);

                bodyTwo.RCB022 = bodyOne.RCC004;
                bodyTwo.RCB024 = bodyOne.RCC010;
                //bodyTwo . RCB008 = bodyOne . RCC006;
                bodyTwo.RCB029 = bodyOne.RCC022;

                strSql = new StringBuilder( );
                //001
                //strSql . AppendFormat ( "SELECT QAB003,DEA002,DEA003,DEA057,DDA001,CONVERT(FLOAT,QAB005/QAB006) QAB FROM SGMQAB INNER JOIN TPADEA ON QAB003=DEA001 INNER JOIN TPADDA ON DEA008=DDA001 WHERE QAB001='{0}'" ,bodyTwo . RCB024 );
                //002
                //strSql . Append ( "WITH CET AS (" );
                //strSql . AppendFormat ( "SELECT QAB003,DEA002,DEA003,DEA057,DDA001 FROM SGMQAB INNER JOIN TPADEA ON QAB003=DEA001 INNER JOIN TPADDA ON DEA008=DDA001 WHERE QAB001='{0}'),CFT AS ( " ,bodyTwo . RCB024 );
                //strSql . AppendFormat ( "SELECT B.RAB003,CASE WHEN RAB=0 THEN 0 ELSE (RAB008-RAB009)/RAB END RAB FROM SGMRAA A INNER JOIN (SELECT RAA001,RAB003,MAX(CASE WHEN RAB007=0 THEN 0 WHEN RAA018=0 THEN 0 ELSE CONVERT(FLOAT,(RAB008-RAB009)/(RAB007/RAA018)) END) RAB FROM SGMRAA A INNER JOIN SGMRAB B ON A.RAA001=B.RAB001 WHERE RAA001='{0}' GROUP BY RAA001,RAB003) B ON A.RAA001=B.RAA001 INNER JOIN SGMRAB C ON B.RAA001=C.RAB001 AND B.RAB003=C.RAB003 WHERE A.RAA001='{0}') SELECT  QAB003,DEA002,DEA003,DEA057,DDA001,RAB QAB FROM CET A INNER JOIN CFT B ON A.QAB003=B.RAB003" ,bodyTwo . RCB022 );
                //003
                strSql.AppendFormat("SELECT B.RAB003 QAB003,DEA002,DEA003,DEA057,DDA001,CASE WHEN RAB=0 THEN 0 ELSE (RAB008-RAB009)/RAB END QAB FROM SGMRAA A INNER JOIN (SELECT RAA001,RAB003,MAX(CASE WHEN RAB007=0 THEN 0 WHEN RAA018=0 THEN 0 ELSE CONVERT(FLOAT,(RAB008-RAB009)/(RAB007/RAA018)) END) RAB FROM SGMRAA A INNER JOIN SGMRAB B ON A.RAA001=B.RAB001 WHERE RAA001='{0}' AND RAA015='{1}' GROUP BY RAA001,RAB003) B ON A.RAA001=B.RAA001 INNER JOIN SGMRAB C ON B.RAA001=C.RAB001 AND B.RAB003=C.RAB003 INNER JOIN TPADEA D ON B.RAB003=D.DEA001 INNER JOIN TPADDA F ON DEA008=DDA001 WHERE A.RAA001='{0}'  AND RAA015='{1}'", bodyTwo.RCB022, bodyTwo.RCB024);

                tableOne = SqlHelper.ExecuteDataTable(strSql.ToString( ));

                if (tableOne != null && tableOne.Rows.Count > 0)
                {
                    int j = 0;
                    foreach (DataRow r in tableOne.Rows)
                    {
                        j++;
                        bodyTwo.RCB003 = j.ToString( ).PadLeft(3, '0');
                        bodyTwo.RCB004 = r ["QAB003"].ToString( );
                        bodyTwo.RCB005 = r ["DEA002"].ToString( );
                        bodyTwo.RCB006 = r ["DEA003"].ToString( );
                        bodyTwo.RCB007 = r ["DDA001"].ToString( );
                        bodyTwo.RCB008 = bodyOne.RCC006 * (string.IsNullOrEmpty(r ["QAB"].ToString( )) == true ? 0 : Convert.ToDecimal(r ["QAB"]));
                        bodyTwo.RCB020 = r ["DEA057"].ToString( );
                        if (bodyTwo.RCB008 > 0)
                        {
                            addBodyTwo(SQLString, bodyTwo);
                        }
                    }
                }
            }

            return(SQLString);
        }