Example #1
0
 public string GetPatternNameByPatternNO(ArrayList ParameterList)
 {
     #region
     try
     {
         DBO.COM16_DBO dbo = new COM16_DBO(ref USEDB);
         return dbo.doGetPatternNameByPatternNO(ParameterList);
     }
     catch (Exception ex)
     {
         throw ex;
     }
     #endregion
 }
Example #2
0
        public DataTable QueryAreaSale(ArrayList ParameterList)
        {
            #region
            try
            {
                string strDynamicSQL = "";

                string strSQLFixed1_SubTotal = "(select cap.area_code,sum(cap.sales_qty) as TSQ" +
                                               ",(round(sum(cap.sales_qty)/{0},4)*100) as TRA " +
                                               "from vds_cam_area_pattern cap group by cap.area_code order by cap.area_code)A";

                string strSQLFixed2_Pattern = ",(select cap.area_code,cap.pattern_no,sum(cap.sales_qty) as SQ"+
                                              ",round(sum(cap.sales_qty)/{0},4)*100 as RA "+
                                              "from vds_cam_area_pattern cap "+   
                                              "where pattern_no='{1}' "+
                                              "group by cap.area_code,cap.pattern_no order by cap.area_code){2}";

                string strSQLFixed3_Select = ",{0}.PATTERN_NO,{0}.SQ,{0}.RA";
                string strSQLFixed4_Where = "AND A.AREA_CODE={0}.AREA_CODE(+) ";

                string strSQL_Select = " select A.*";
                string strSQL_MainTable = "";
                string strSQL_JoinTable = "";
                string strSQL_Where = "";
                string strSQL_Alias="";

                DBO.COM16_DBO dbo = new COM16_DBO(ref USEDB);
                
                DataTable DtTemp1_SubTotal = dbo.doQueryAreaSale(ParameterList);

                for (Int32 i = 0; i <= DtTemp1_SubTotal.Rows.Count - 1; i++) 
                {
                    if (DtTemp1_SubTotal.Rows[i]["PATTERN_NO"].ToString().Trim() == "0000000")
                    {
                        strSQL_MainTable = string.Format(strSQLFixed1_SubTotal, DtTemp1_SubTotal.Rows[i]["TSQ"].ToString().Trim());
                    }
                    else
                    {
                        strSQL_Alias = string.Format("T{0}", i);
                        strSQL_Select += string.Format(strSQLFixed3_Select, strSQL_Alias);
                        strSQL_JoinTable += string.Format(strSQLFixed2_Pattern, DtTemp1_SubTotal.Rows[i]["TSQ"].ToString().Trim(), DtTemp1_SubTotal.Rows[i]["PATTERN_NO"].ToString().Trim(), strSQL_Alias);
                        strSQL_Where += string.Format(strSQLFixed4_Where, strSQL_Alias);
                    }               
                }

                if (strSQL_Where.Length > 3)
                    strSQL_Where = strSQL_Where.Substring(3);
                
                strDynamicSQL = string.Format("{0} FROM {1} {2} WHERE {3}", strSQL_Select, strSQL_MainTable, strSQL_JoinTable, strSQL_Where);


                ArrayList ParameterList2 = new ArrayList();
                ParameterList2.Clear();

                for (Int32 i = 0; i <= strDynamicSQL.Length - 1; i++)
                {
                    if (strDynamicSQL.Length - i > 4000)
                    {
                        ParameterList2.Add(strDynamicSQL.Substring(i, 4000));
                    }
                    else
                    {
                        ParameterList2.Add(strDynamicSQL.Substring(i, strDynamicSQL.Length - i));
                    }

                    i = i + 3999;

                }

                return dbo.doQueryPivotAreaSaleByDynamicSQL(ParameterList2, strConn);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            #endregion
        }
Example #3
0
 public DataTable QueryAreaSale3(ArrayList ParameterList, out  Int32 i32_TOTAL_PATTERN)
 {
     #region
     try
     {
         DBO.COM16_DBO dbo = new COM16_DBO(ref USEDB);
         return dbo.doQueryAreaSale3(ParameterList, out i32_TOTAL_PATTERN);
     }
     catch (Exception ex)
     {
         throw ex;
     }
     #endregion
 }