/// <summary>
/// MKT10報表
/// </summary>
/// <param name="ParameterList">變數清單</param>
/// <returns>回傳查詢結果</returns>
public DataTable MKT13_1(ArrayList ParameterList)
{
MKTModel.MaintainMarketSurveyRecords bco = new MKTModel.MaintainMarketSurveyRecords(ConntionDB);
DataTable Dt = bco.QuerySurveyRec(ParameterList);
if (Dt.Rows.Count == 0)
{
throw new Exception("查無資料");
}
else
{
#region 將上市日期轉為無時間的格式
DataTable dt = Dt.Clone();
dt.Columns["PUBLISH_DATE"].DataType = System.Type.GetType("System.String");
foreach (DataRow dr in Dt.Rows)
{
DataRow DR = dt.NewRow();
for (int i = 0; i < Dt.Columns.Count; i++)
DR[i] = dr[i];
if (DR["PUBLISH_DATE"] != System.DBNull.Value)
DR["PUBLISH_DATE"] = DateTime.Parse(DR["PUBLISH_DATE"].ToString()).ToShortDateString();
dt.Rows.Add(DR);
}
#endregion
return dt;
}
}
protected void Btn_ExportSurvey_Click(object sender, EventArgs e)
{
try
{
ErrorMsgLabel.Text = "";
#region 資料檢查
if (SLP_CodeFile1.Text == "" || SLP_CodeFile2.Text == "")
{
ErrorMsgLabel.Text = "營業所不可選全部";
return;
}
if (string.Compare(SLP_CodeFile1.Text, SLP_CodeFile2.Text) != 0)
{
ErrorMsgLabel.Text = "只能選一個營業所";
return;
}
if (dSurveyDate.Text == "")
{
ErrorMsgLabel.Text = "市調日期不可空白";
return;
}
#endregion
DataTable dt = new DataTable();
#region 查詢市調情報資料
//取得查詢參數: 營業所,市調日期
ParameterList.Clear();
ParameterList.Add(GetValueSetParameter(SLP_CodeFile1.Text, "string")); //營業所
ParameterList.Add(GetValueSetParameter(dSurveyDate.Text, "date")); //市調日期
ParameterList.Add(GetValueSetParameter(txtInvestigator.Text.Trim(), "string")); //市調人員 2010/09/24 modified
ParameterList.Add(GetValueSetParameter("9999", "int"));
//ParameterList.Add(GetValueSetParameter((SLP_CodeFile1.Text != "") ? "" : SLP_CodeFile2.Text, "string")); //營業所2
ParameterList.Add(GetValueSetParameter(SLP_CodeFile2.Text, "string")); //營業所(迄)
BCO.MaintainMarketSurveyRecords bco = new MaintainMarketSurveyRecords(ConnectionDB);
dt = bco.QuerySurveyRec(ParameterList);
#endregion
#region 將上市日期轉為無時間的格式
DataTable Dt = dt.Clone();
if (dt.Rows.Count > 0)
{
Dt.Columns["PUBLISH_DATE"].DataType = System.Type.GetType("System.String");
foreach (DataRow dr in dt.Rows)
{
DataRow DR = Dt.NewRow();
for (int i = 0; i < dt.Columns.Count; i++)
DR[i] = dr[i];
if (DR["PUBLISH_DATE"] != System.DBNull.Value)
DR["PUBLISH_DATE"] = DateTime.Parse(DR["PUBLISH_DATE"].ToString()).ToShortDateString();
Dt.Rows.Add(DR);
}
dt.Dispose();
dt = null;
}
#endregion
if (Dt == null || (Dt != null && Dt.Rows.Count <= 0))
{
//若不存在市調情報資料,無法匯出市調表
ErrorMsgLabel.Text = "查無資料";
}
else
{
//若已存在市調情報資料,匯出市調表
#region 匯出市調表
//設定輸出檔名
string s_FileName = "";
s_FileName = HttpUtility.UrlEncode("市調表.xls", System.Text.Encoding.UTF8);
#region 設定Excel Worksheet
//Excel Worksheet initilize
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet = book[0];
sheet.Name = "市調表";
//設定Worksheet橫印
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.7, 0.5, 0.6);
//設定Worksheet各column 寬度
sheet.Columns(0).Width = 55;
sheet.Columns(1).Width = 130;
sheet.Columns(2).Width = 43;
sheet.Columns(3).Width = 58;
sheet.Columns(4).Width = 30;
sheet.Columns(5).Width = 42;
sheet.Columns(6).Width = 30;
sheet.Columns(7).Width = 30;
sheet.Columns(8).Width = 30;
sheet.Columns(9).Width = 30;
sheet.Columns(10).Width = 42;
sheet.Columns(11).Width = 30;
sheet.Columns(12).Width = 30;
sheet.Columns(13).Width = 30;
sheet.Columns(14).Width = 30;
sheet.Columns(15).Width = 42;
sheet.Columns(16).Width = 30;
sheet.Columns(17).Width = 30;
sheet.Columns(18).Width = 30;
sheet.Columns(19).Width = 30;
//設定外框線
sheet.Border.Sides = BorderSides.All;
sheet.Border.LineStyle = Borderline.Continuous;
#endregion
int iRow = 0; //Worksheet 列數
string strSTORE = "";
ArrayList alTITLE = new ArrayList();//營業所,市調人員ArrayList
ArrayList alSTORE = new ArrayList();//店號,店名ArrayList
int iStoreCount = 0; //總店數
//營業所,市調人員ArrayList
foreach (DataRow dr in Dt.Rows)
{
alTITLE.Add(new string[] { dr["EXPLAIN"].ToString(), dr["INVESTIGATOR"].ToString() });
break; //(營業所,市調人員)都相同, 取第一筆即可
}
int iRowStore = 3;//每行店數
int iStoreCol = 5;//每個店有幾個欄位
foreach (string[] ss in alTITLE)
{
//取得店號,店名,市調人員ArrayList
foreach (DataRow dr in Dt.Select("ITEM='" + Dt.Rows[0]["ITEM"].ToString() + "'"))
{
alSTORE.Add(new string[] { dr["STORE"].ToString(), dr["STORE_NAME"].ToString(), dr["INVESTIGATOR"].ToString() });//20121204 顏筱盈
}
//總店數
iStoreCount = alSTORE.Count;
for (int iStoreArea = 0; iStoreArea < Convert.ToInt32(Math.Ceiling(alSTORE.Count / (decimal)iRowStore)); iStoreArea++)
{
#region 設定Header列資訊
ArrayList alQTY = new ArrayList();
sheet[0, iRow].Value = ss[0]; //營業所
//sheet[1, iRow].Value = ss[1]; //市調人員
sheet[1, iRow].Value = ""; //20121204 顏筱盈
sheet[2, iRow].Value = "期別";
sheet[3, iRow].Value = "上市日期";
sheet[4, iRow].Value = "市調";
new Range(sheet[0, iRow + 1], sheet[3, iRow + 1]).Merge();
sheet[4, iRow + 1].Value = "次數";
for (int iStoreNo = (iRowStore * iStoreArea); iStoreNo < (iRowStore * iStoreArea + iRowStore); iStoreNo++)
{
if (iStoreNo >= alSTORE.Count) break;
string[] ss1 = (string[])alSTORE[iStoreNo];
sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 5, iRow].Value = ss1[0];//店號
//sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 6, iRow].Value = ss1[1];//店名
sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 6, iRow].Value = ss1[1] + " ( " +ss1[2] + " )";//店名,市調人員 20121204 顏筱盈
new Range(sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 6, iRow], sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 9, iRow]).Merge();
sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 5, iRow + 1].Value = "發書";
alQTY.Add((iStoreNo - iStoreArea * iRowStore) * iStoreCol + 5);
sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 6, iRow + 1].Value = "補書";
sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 7, iRow + 1].Value = "退書";
sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 8, iRow + 1].Value = "庫存";
sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 9, iRow + 1].Value = "備註";
}
#endregion
iRow = iRow + 2;
int iItemCount = 0; //商品項數
//取得商品相關資料
foreach (DataRow dr in Dt.Select("STORE='" + ((string[])alSTORE[iRowStore * iStoreArea])[0] + "'", "ITEM,ROUTE,STEP"))
{
sheet[0, iRow].Value = dr["ITEM"].ToString(); //品號
sheet[1, iRow].Value = dr["ITEM_NAME"].ToString(); //品名
sheet[2, iRow].Value = dr["PERIOD"].ToString(); //期別
sheet[3, iRow].Value = dr["PUBLISH_DATE"].ToString(); //上市日期
sheet[4, iRow].Value = dr["SURVEY_TIME"].ToString(); //市調次數
int iStore = 0;
foreach (object o in alQTY)
{
DataRow[] drQty = Dt.Select("STORE='" + ((string[])alSTORE[iRowStore * iStoreArea + iStore])[0] + "'" + " AND ITEM='" + dr["ITEM"].ToString() + "'" + " AND PERIOD='" + dr["PERIOD"].ToString() + "'", "ITEM,ROUTE,STEP");
sheet[Convert.ToInt32(o), iRow].Value = drQty[0]["INCOME_QTY"].ToString(); //發書
sheet[Convert.ToInt32(o) + 1, iRow].Value = drQty[0]["REPLINISHMENT_QTY"].ToString(); //補書
sheet[Convert.ToInt32(o) + 2, iRow].Value = drQty[0]["RETURN_QTY"].ToString(); //退書
iStore++;
}
iRow++;
iItemCount++;
}
iRow++;
//商品總項數設定為20,不足則填入空列數
if (iItemCount != 0 & iItemCount < 20)
{
for (int iAdd = iItemCount; iAdd < 20; iAdd++)
{
iRow++;
}
}
}
}
#region 報表最後一頁特別處理
double dStoreCal = (double)(iStoreCount / (decimal)iRowStore);
int iNewBeginRow = Convert.ToInt32(Math.Ceiling(dStoreCal)) * 23;
//報表最後一頁,最後一列,最後一格 給一空白, 這樣最後一頁印出才會完整
if (Math.Ceiling(dStoreCal) % 2 == 1)
{
sheet[4 + iRowStore * iStoreCol, iNewBeginRow - 1 + 23].Value = " ";
}
else
{
sheet[4 + iRowStore * iStoreCol, iNewBeginRow - 1].Value = " ";
}
#endregion
//設定報表 縮放比例
int iPageCount = Convert.ToInt32(Math.Ceiling(dStoreCal / 2.0));
sheet.PrintOptions.SetFitToPage(1, iPageCount); //Sets excel's fit to page property (int width,int height)
//File download
Response.Charset = "big5";
Response.ContentType = "application/vnd.ms-excel;charset='utf-8'";
Response.AddHeader("content-disposition", "attachment; filename=" + s_FileName);//excel檔名
System.IO.MemoryStream stream = new System.IO.MemoryStream();
book.Export(stream);
string producedExcel = GetStringFromMemoryStream(stream);
stream.Close();
Response.Write(producedExcel);
Response.Flush();
Response.End();
#endregion
}
}
catch (Exception ex)
{
ErrorMsgLabel.Text = ex.Message;
}
}
protected void Btn_ExportResult_Click(object sender, EventArgs e)
{
try
{
#region 資料檢查
if (string.Compare(SLP_CodeFile1.Text, SLP_CodeFile2.Text) > 0)
{
ErrorMsgLabel.Text = "營業所(起)不可大於營業所(迄)";
return;
}
if (dSurveyDate.Text == "")
{
ErrorMsgLabel.Text = "市調日期不可空白";
return;
}
#endregion
DataTable Dt = new DataTable();
#region 查詢市調情報資料
//取得查詢參數: 營業所,市調日期
ParameterList.Clear();
ParameterList.Add(GetValueSetParameter(this.SLP_CodeFile1.Text, "string")); //營業所
ParameterList.Add(GetValueSetParameter(this.dSurveyDate.Text, "date")); //市調日期
ParameterList.Add(GetValueSetParameter(txtInvestigator.Text.Trim(), "string")); //市調人員 2010/09/30 modified
ParameterList.Add(GetValueSetParameter("9999", "int"));
//ParameterList.Add(GetValueSetParameter((SLP_CodeFile1.Text != "") ? "" : SLP_CodeFile2.Text, "string")); //營業所2
ParameterList.Add(GetValueSetParameter(SLP_CodeFile2.Text, "string")); //營業所(迄)
BCO.MaintainMarketSurveyRecords bco = new MaintainMarketSurveyRecords(ConnectionDB);
Dt = bco.QuerySurveyRec(ParameterList);
#endregion
if (Dt == null || (Dt != null && Dt.Rows.Count <= 0))
{
//若不存在市調情報資料,無法匯出市調結果
ErrorMsgLabel.Text = "查無資料";
}
else
{
#region 將上市日期轉為無時間的格式
DataTable dt = Dt.Clone();
dt.Columns["PUBLISH_DATE"].DataType = System.Type.GetType("System.String");
foreach (DataRow dr in Dt.Rows)
{
DataRow DR = dt.NewRow();
for (int i = 0; i < Dt.Columns.Count; i++)
DR[i] = dr[i];
if (DR["PUBLISH_DATE"] != System.DBNull.Value)
DR["PUBLISH_DATE"] = DateTime.Parse(DR["PUBLISH_DATE"].ToString()).ToShortDateString();
dt.Rows.Add(DR);
}
Dt.Dispose();
Dt = null;
#endregion
//若已存在市調情報資料,匯出市調結果
#region 匯出市調結果
string s_FileName = ""; //設定輸出檔名
string s_rptFilePath = ""; //設定報表路徑
s_rptFilePath = Server.MapPath("./REPORT/MKT131/MKT131_Excel.rpt");
s_FileName = HttpUtility.UrlEncode("市調情報資料.xls", System.Text.Encoding.UTF8);
LoadCrystalReport(s_FileName, s_rptFilePath, dt, "EXCEL");
#endregion
}
}
catch (Exception ex)
{
ErrorMsgLabel.Text = ex.Message;
}
}
protected void Btn_Query_Click(object sender, EventArgs e)
{
try
{
#region 資料檢查
if (string.Compare(SLP_CodeFile1.Text, SLP_CodeFile2.Text) > 0)
{
ErrorMsgLabel.Text = "營業所(起)不可大於營業所(迄)";
return;
}
if (dSurveyDate.Text == "")
{
ErrorMsgLabel.Text = "市調日期不可空白";
return;
}
#endregion
DataTable Dt = new DataTable();
#region 處理查詢
BCO.MaintainMarketSurveyRecords bco = new MaintainMarketSurveyRecords(ConnectionDB);
Dt = bco.QuerySurveyRec(this.getParameterList());
#endregion
if (Dt == null || (Dt != null && Dt.Rows.Count <= 0))
ErrorMsgLabel.Text = "查無資料";
else
Session["MKT131_" + PageTimeStamp.Value] = Dt.Copy();
#region 設定查詢結果
this.GridView1.DataSource = Dt;
this.GridView1.PageSize = (TextBoxPagesize.Text == "") ? 10 : (int.Parse(TextBoxPagesize.Text) < 1) ? 10 : int.Parse(TextBoxPagesize.Text);
this.GridView1.PageIndex = 0;
this.GridView1.DataBind();
#endregion
}
catch (Exception ex)
{ this.ErrorMsgLabel.Text = ex.ToString(); }
finally { }
}
