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 }
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 }
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 }