/// <summary>
/// CAA20報表
/// </summary>
/// <param name="ParameterList">變數清單</param>
/// <returns>回傳查詢結果</returns>
public DataTable CAA04_1(ArrayList ParameterList)
{
CAAModel.RecordingInvoiceDetail bco = new CAAModel.RecordingInvoiceDetail(ConntionDB);
DataTable Dt = bco.GetInvoiceBatch(ParameterList);
if (Dt.Rows.Count == 0)
{
throw new Exception("查無資料");
}
else
{
return Dt;
}
}
/// <summary>
/// LOAD 資料
/// </summary>
private void Init_Data()
{
hdf_ID_TEMP_AMT.Value = ((TextBox)this.SLP_TEMPAMT.FindControl("TextBoxCode")).ClientID;
hdf_ID_TEMP_REMAIN_AMT.Value = ((TextBox)this.SLP_REMAIN_AMT.FindControl("TextBoxCode")).ClientID;
//設定[來源]欄位
ddlSOURCE_TYPE.Items.Clear();
ddlSOURCE_TYPE.Items.Add(new ListItem("ALL", ""));
CAAModel.MaintainTempRecord BCO = new CAAModel.MaintainTempRecord(ConntionDB);
DataTable dt;
dt = BCO.GetSourceType();
for (int i = 0; i < dt.Rows.Count; i++)
{
ddlSOURCE_TYPE.Items.Add(new ListItem(dt.Rows[i]["ENUMTYPENAME"].ToString(), dt.Rows[i]["ENUMVALUE"].ToString()));
}
ddlSOURCE_TYPE.SelectedIndex = 0;
//設定[轉其他收入]欄位
rblCHG_INCOME_TYPE.Items.Clear();
rblCHG_INCOME_TYPE.Items.Add(new ListItem("是", "1"));
rblCHG_INCOME_TYPE.Items.Add(new ListItem("否", "0"));
rblCHG_INCOME_TYPE.SelectedIndex = 1;
//設定[稅別]欄位
CAAModel.RecordingInvoiceDetail BCO2 = new CAAModel.RecordingInvoiceDetail(ConntionDB);
DataTable dt2;
dt2 = BCO2.GetTaxType();
ddlTaxType.Items.Add(new ListItem("", ""));
for (int i = 0; i < dt2.Rows.Count; i++)
{
ddlTaxType.Items.Add(new ListItem(dt2.Rows[i]["ENUMTYPENAME"].ToString(), dt2.Rows[i]["ENUMVALUE"].ToString()));
}
ddlTaxType.SelectedIndex = 0;
ModeSetLoad();
}
private void Set_Tax_Type(DropDownList ddlTemp)
{
//設定[稅別]欄位
CAAModel.RecordingInvoiceDetail BCO = new CAAModel.RecordingInvoiceDetail(ConntionDB);
DataTable dt;
dt = BCO.GetTaxType();
ddlTemp.Items.Add(new ListItem("ALL", ""));
for (int i = 0; i < dt.Rows.Count; i++)
{
ddlTemp.Items.Add(new ListItem(dt.Rows[i]["ENUMTYPENAME"].ToString(), dt.Rows[i]["ENUMVALUE"].ToString()));
}
ddlTemp.SelectedIndex = 0;
}
private void InitData()
{
rblTaxType.Items.Clear();
rblTaxType.Items.Add(new ListItem("ALL", ""));
CAAModel.RecordingInvoiceDetail BCO = new CAAModel.RecordingInvoiceDetail(ConntionDB);
DataTable dt;
dt = BCO.GetTaxType();
for (int i = 0; i < dt.Rows.Count; i++)
{
rblTaxType.Items.Add(new ListItem(dt.Rows[i]["ENUMTYPENAME"].ToString(), dt.Rows[i]["ENUMVALUE"].ToString()));
}
rblTaxType.SelectedIndex = 0;
//this.SLP_EnumBase1.DropDownListControl.Attributes.Add("onchange", "alert('ddl change');");
btnQuery.OnClientClick = "return checkinput();";
}
/// <summary>
/// CAA02報表
/// </summary>
/// <param name="ParameterList">變數清單</param>
/// <returns>回傳查詢結果</returns>
public DataTable CAA02_1(ArrayList ParameterList)
{
#region 宣告變數
BCO.CAACommon CAAComm = new BCO.CAACommon();
ArrayList arl_ReportService = new ArrayList();
DataTable dt_Return = new DataTable();
#endregion
#region 傳入參數
arl_ReportService.Clear();
arl_ReportService.Add(CAAComm.GetValueSetParameter(ParameterList[0].ToString(), "date", false));//[發票年月]起(0)
arl_ReportService.Add(CAAComm.GetValueSetParameter(ParameterList[1].ToString(), "date", false));//[發票年月]迄(1)
arl_ReportService.Add(CAAComm.GetValueSetParameter(ParameterList[2].ToString(), "int", false));//[稅別](2)
arl_ReportService.Add(CAAComm.GetValueSetParameter(ParameterList[3].ToString(), "int", false));//[作廢列印](3)
arl_ReportService.Add(CAAComm.GetValueSetParameter(ParameterList[4].ToString(), "string", false));//[登入人員](4)
#endregion
#region 連結資料庫
BCO.RecordingInvoiceDetail bco = new BCO.RecordingInvoiceDetail(ConntionDB);
dt_Return = bco.GetInvoiceDetail(arl_ReportService);
#endregion
#region 檢查回傳資料
if (dt_Return.Rows.Count == 0)
{ throw new Exception("查無資料"); }
else
{
#region 處理回傳資料
#region 先將回傳資料做分類，確定發票號碼區間
#region 建立TempTable dt_TempSector_Master，來記錄分類結果
DataTable dt_TempSector_Master = new DataTable();
int i_INV_NO_SECTOR_GROUP = 0;
dt_TempSector_Master.Columns.Add("INV_NO_SECTOR_BEGIN");
dt_TempSector_Master.Columns.Add("INV_NO_SECTOR_GROUP", typeof(int));
#endregion
foreach (DataRow dRow in dt_Return.Rows)
{
#region 宣告變數
string s_INV_NO = string.Empty;
string s_INV_NO_B = string.Empty;
string s_INV_NO_E = string.Empty;
string s_FilterExpression = string.Empty;
#endregion
#region 判斷該筆資料是再那一個區間，並算出該區間的第一筆資料為何
s_INV_NO = dRow["INV_NO"].ToString();//從資料庫抓回之資料
s_INV_NO_B = s_INV_NO.Substring(0, 8);//INV_NO的前8碼
s_INV_NO_E = s_INV_NO.Substring(8, 2);//INV_NO的後2碼
if (int.Parse(s_INV_NO_E) >= 0 && int.Parse(s_INV_NO_E) <= 49)
{ s_INV_NO_E = "00"; }//如果INV_NO的後2碼區間於00~49，則第一筆資料為[INV_NO的前8碼 + "00"]
else if (int.Parse(s_INV_NO_E) >= 50 && int.Parse(s_INV_NO_E) <= 99)
{ s_INV_NO_E = "50"; }//如果INV_NO的後2碼區間於50~99，則第一筆資料為[INV_NO的前8碼 + "50"]
s_INV_NO = s_INV_NO_B + s_INV_NO_E;
#endregion
#region 檢查算出的值是否已存在於 dt_TempSector_Master，不存在則加入
s_FilterExpression = "INV_NO_SECTOR_BEGIN = '" + s_INV_NO + "'";
if (dt_TempSector_Master.Select(s_FilterExpression).Length == 0)
{
DataRow dr_TempSector_Master = dt_TempSector_Master.NewRow();
dr_TempSector_Master["INV_NO_SECTOR_BEGIN"] = s_INV_NO;
dr_TempSector_Master["INV_NO_SECTOR_GROUP"] = i_INV_NO_SECTOR_GROUP;
i_INV_NO_SECTOR_GROUP += 1;
dt_TempSector_Master.Rows.Add(dr_TempSector_Master);
}
#endregion
}
#endregion
#region 利用分類結果，建立每一個區間50筆資料的空TempTable dt_TempSector_Detail
#region 建立TempTable dt_TempSector_Detail
DataTable dt_TempSector_Detail = new DataTable();
dt_TempSector_Detail.Columns.Add("INV_NO_ALL", typeof(string));//完整的發票號碼
dt_TempSector_Detail.Columns.Add("INV_NO", typeof(string));//報表欄位[字號]
dt_TempSector_Detail.Columns.Add("VOUCH_RFNO", typeof(string));//報表欄位[統一編號]
dt_TempSector_Detail.Columns.Add("INV_UAMT", typeof(int));//報表欄位[銷售額]
dt_TempSector_Detail.Columns.Add("ITEM_TAX_NEED", typeof(string));//報表欄位[課稅別，應]
dt_TempSector_Detail.Columns.Add("ITEM_TAX_ZERO", typeof(string));//報表欄位[課稅別，零]
dt_TempSector_Detail.Columns.Add("ITEM_TAX_FREE", typeof(string));//報表欄位[課稅別，免]
dt_TempSector_Detail.Columns.Add("INV_TAX", typeof(int));//報表欄位[稅額]
dt_TempSector_Detail.Columns.Add("MEMO", typeof(string));//報表欄位[備註]
dt_TempSector_Detail.Columns.Add("CANCEL_FLG", typeof(int));//資料庫欄位[作廢註記]
dt_TempSector_Detail.Columns.Add("INV_NO_SECTOR_GROUP", typeof(int));//報表欄位，標註每50筆一個Group代號
dt_TempSector_Detail.Columns.Add("INV_NULL_COUNT", typeof(int));//註記發票是否為空白發票，1=空白發票，0=非空白發票
#endregion
foreach (DataRow dRow in dt_TempSector_Master.Rows)
{
#region 宣告變數
string s_INV_NO = string.Empty;
string s_INV_NO_B = string.Empty;
string s_INV_NO_E = string.Empty;
string s_INV_NO_E_Result = string.Empty;
int i_INV_NO_E = 0;
#endregion
#region 依據 dt_TempSector_Master的資料，建立該區間50筆空資料
s_INV_NO = dRow["INV_NO_SECTOR_BEGIN"].ToString();
s_INV_NO_B = s_INV_NO.Substring(0, 8);//INV_NO_SECTOR_BEGIN的前8碼
s_INV_NO_E = s_INV_NO.Substring(8, 2);//INV_NO_SECTOR_BEGIN的後2碼
for (int i = 0; i < 50; i++)
{
DataRow dr_TempSector_Detail = dt_TempSector_Detail.NewRow();
i_INV_NO_E = int.Parse(s_INV_NO_E);
i_INV_NO_E += i;
s_INV_NO_E_Result = i_INV_NO_E.ToString().PadLeft(2, char.Parse("0"));
dr_TempSector_Detail["INV_NO_ALL"] = s_INV_NO_B + s_INV_NO_E_Result;
dr_TempSector_Detail["INV_NO"] = s_INV_NO_E_Result;
dr_TempSector_Detail["INV_NO_SECTOR_GROUP"] = dRow["INV_NO_SECTOR_GROUP"];
dr_TempSector_Detail["INV_NULL_COUNT"] = 1;//1=空白發票
dt_TempSector_Detail.Rows.Add(dr_TempSector_Detail);
}
#endregion
}
#endregion
#region 把從資料庫查詢出來的資料，寫入TempTable dt_TempSector_Detail
foreach (DataRow dRow in dt_Return.Rows)
{
string s_FilterExpression = "INV_NO_ALL ='" + dRow["INV_NO"].ToString() + "'";
DataRow dRow_Valraible = dt_TempSector_Detail.Select(s_FilterExpression)[0];
dRow_Valraible["VOUCH_RFNO"] = dRow["VOUCH_RFNO"];//統一編號
dRow_Valraible["INV_UAMT"] = dRow["INV_UAMT"];//銷售額
if (dRow["TAX_TYPE"].ToString() == "0")//應稅
{ dRow_Valraible["ITEM_TAX_NEED"] = "V"; }
else if (dRow["TAX_TYPE"].ToString() == "1")//免稅
{ dRow_Valraible["ITEM_TAX_FREE"] = "V"; }
else if (dRow["TAX_TYPE"].ToString() == "2")//零稅
{ dRow_Valraible["ITEM_TAX_ZERO"] = "V"; }
dRow_Valraible["INV_TAX"] = dRow["INV_TAX"];//稅額
dRow_Valraible["MEMO"] = dRow["MEMO"];//備註
dRow_Valraible["CANCEL_FLG"] = dRow["CANCEL_FLG"];//作廢註記
dRow_Valraible["INV_NULL_COUNT"] = 0;//註記發票是否為空白發票，1=空白發票，0=非空白發票
}
#endregion
#region 將TempTable dt_TempSector_Detail組合成報表格式TempTable dt_Result_Detail
#region 建立TempTable dt_Result_Detail
DataTable dt_Result_Detail = new DataTable();
dt_Result_Detail.Columns.Add("INV_NO_ALL", typeof(string));//完整的發票號碼
dt_Result_Detail.Columns.Add("INV_NO", typeof(string));//報表欄位[字號]
dt_Result_Detail.Columns.Add("VOUCH_RFNO", typeof(string));//報表欄位[統一編號]
dt_Result_Detail.Columns.Add("INV_UAMT", typeof(int));//報表欄位[銷售額]
dt_Result_Detail.Columns.Add("ITEM_TAX_NEED", typeof(string));//報表欄位[課稅別，應]
dt_Result_Detail.Columns.Add("ITEM_TAX_ZERO", typeof(string));//報表欄位[課稅別，零]
dt_Result_Detail.Columns.Add("ITEM_TAX_FREE", typeof(string));//報表欄位[課稅別，免]
dt_Result_Detail.Columns.Add("INV_TAX", typeof(int));//報表欄位[稅額]
dt_Result_Detail.Columns.Add("MEMO", typeof(string));//報表欄位[備註]
dt_Result_Detail.Columns.Add("CANCEL_FLG", typeof(int));//資料庫欄位[作廢註記]
dt_Result_Detail.Columns.Add("INV_NULL_COUNT", typeof(int));//註記發票是否為空白發票，1=空白發票，0=非空白發票
dt_Result_Detail.Columns.Add("INV_NO_ALL1", typeof(string));//完整的發票號碼
dt_Result_Detail.Columns.Add("INV_NO1", typeof(string));//報表欄位[字號]
dt_Result_Detail.Columns.Add("VOUCH_RFNO1", typeof(string));//報表欄位[統一編號]
dt_Result_Detail.Columns.Add("INV_UAMT1", typeof(int));//報表欄位[銷售額]
dt_Result_Detail.Columns.Add("ITEM_TAX_NEED1", typeof(string));//報表欄位[課稅別，應]
dt_Result_Detail.Columns.Add("ITEM_TAX_ZERO1", typeof(string));//報表欄位[課稅別，零]
dt_Result_Detail.Columns.Add("ITEM_TAX_FREE1", typeof(string));//報表欄位[課稅別，免]
dt_Result_Detail.Columns.Add("INV_TAX1", typeof(int));//報表欄位[稅額]
dt_Result_Detail.Columns.Add("MEMO1", typeof(string));//報表欄位[備註]
dt_Result_Detail.Columns.Add("CANCEL_FLG1", typeof(int));//資料庫欄位[作廢註記]
dt_Result_Detail.Columns.Add("INV_NULL_COUNT1", typeof(int));//註記發票是否為空白發票，1=空白發票，0=非空白發票
dt_Result_Detail.Columns.Add("INV_NO_SECTOR_GROUP", typeof(string));//報表欄位，標註每50筆一個Group代號
#endregion
foreach (DataRow dRow in dt_TempSector_Detail.Rows)
{
#region 宣告變數
string s_INV_NO = string.Empty;
string s_INV_NO_B = string.Empty;
string s_INV_NO_E = string.Empty;
int i_INV_NO_E = 0;
string s_i_INV_NO_E_Add = string.Empty;
string s_FilterExpression = string.Empty;
#endregion
#region 取得發票號碼
s_INV_NO = dRow["INV_NO_ALL"].ToString();
s_INV_NO_B = s_INV_NO.Substring(0, 8);//INV_NO_ALL的前8碼
s_INV_NO_E = s_INV_NO.Substring(8, 2);//INV_NO_ALL的後2碼
i_INV_NO_E = int.Parse(s_INV_NO_E);
#endregion
#region 如果發票號碼後兩碼是00~24或50~74
if (
(i_INV_NO_E >= 0 && i_INV_NO_E <= 24) ||
(i_INV_NO_E >= 50 && i_INV_NO_E <= 74)
)
{
DataRow dr_Result_Detail = dt_Result_Detail.NewRow();
dr_Result_Detail["INV_NO_ALL"] = dRow["INV_NO_ALL"];//完整的發票號碼
dr_Result_Detail["INV_NO"] = dRow["INV_NO"];//報表欄位[字號]
dr_Result_Detail["VOUCH_RFNO"] = dRow["VOUCH_RFNO"];//報表欄位[統一編號]
dr_Result_Detail["INV_UAMT"] = dRow["INV_UAMT"];//報表欄位[銷售額]
dr_Result_Detail["ITEM_TAX_NEED"] = dRow["ITEM_TAX_NEED"];//報表欄位[課稅別，應]
dr_Result_Detail["ITEM_TAX_ZERO"] = dRow["ITEM_TAX_ZERO"];//報表欄位[課稅別，零]
dr_Result_Detail["ITEM_TAX_FREE"] = dRow["ITEM_TAX_FREE"];//報表欄位[課稅別，免]
dr_Result_Detail["INV_TAX"] = dRow["INV_TAX"];//報表欄位[稅額]
dr_Result_Detail["MEMO"] = dRow["MEMO"];//報表欄位[備註]
dr_Result_Detail["CANCEL_FLG"] = dRow["CANCEL_FLG"];//資料庫欄位[作廢註記]
dr_Result_Detail["INV_NULL_COUNT"] = dRow["INV_NULL_COUNT"];//註記發票是否為空白發票，1=空白發票，0=非空白發票
dr_Result_Detail["INV_NO_SECTOR_GROUP"] = dRow["INV_NO_SECTOR_GROUP"];//報表欄位，標註每50筆一個Group代號
dt_Result_Detail.Rows.Add(dr_Result_Detail);
}
#endregion
#region 如果發票號碼後兩碼是25~49或75~99
else
{
i_INV_NO_E -= 25;
s_i_INV_NO_E_Add = i_INV_NO_E.ToString().PadLeft(2, char.Parse("0"));
s_FilterExpression = "INV_NO_ALL = '" + s_INV_NO_B + s_i_INV_NO_E_Add + "'";
DataRow dr_Result_Detail_Add = dt_Result_Detail.Select(s_FilterExpression)[0];
dr_Result_Detail_Add["INV_NO_ALL1"] = dRow["INV_NO_ALL"];//完整的發票號碼
dr_Result_Detail_Add["INV_NO1"] = dRow["INV_NO"];//報表欄位[字號]
dr_Result_Detail_Add["VOUCH_RFNO1"] = dRow["VOUCH_RFNO"];//報表欄位[統一編號]
dr_Result_Detail_Add["INV_UAMT1"] = dRow["INV_UAMT"];//報表欄位[銷售額]
dr_Result_Detail_Add["ITEM_TAX_NEED1"] = dRow["ITEM_TAX_NEED"];//報表欄位[課稅別，應]
dr_Result_Detail_Add["ITEM_TAX_ZERO1"] = dRow["ITEM_TAX_ZERO"];//報表欄位[課稅別，零]
dr_Result_Detail_Add["ITEM_TAX_FREE1"] = dRow["ITEM_TAX_FREE"];//報表欄位[課稅別，免]
dr_Result_Detail_Add["INV_TAX1"] = dRow["INV_TAX"];//報表欄位[稅額]
dr_Result_Detail_Add["MEMO1"] = dRow["MEMO"];//報表欄位[備註]
dr_Result_Detail_Add["CANCEL_FLG1"] = dRow["CANCEL_FLG"];//資料庫欄位[作廢註記]
dr_Result_Detail_Add["INV_NULL_COUNT1"] = dRow["INV_NULL_COUNT"];//註記發票是否為空白發票，1=空白發票，0=非空白發票
}
#endregion
}
#endregion
#endregion
return dt_Result_Detail;
}
#endregion
}
private void RunReport(ReportType RType)
{
#region 檢查條件
CAAModel.CAACommon CAAComm = new CAAModel.CAACommon();
ArrayList arl_Check_Condition = Check_Condition();
#region 如果檢查有誤，則Return
if (arl_Check_Condition[1].ToString() != string.Empty)
{
#region 錯誤訊息
this.ErrorMsgLabel.Text = arl_Check_Condition[1].ToString();
#endregion
#region Focus欄位
if (arl_Check_Condition[0].ToString() != string.Empty)
{
string s_ScriptManager_Script = CAAComm.ToMakeUp_SetFocus_Script(arl_Check_Condition[0].ToString(), true);
ScriptManager.RegisterStartupScript(this.up_ErrorMsg, typeof(UpdatePanel), "CAA041", s_ScriptManager_Script, true);
}
#endregion
return;
}
#endregion
#endregion
DataTable dt;
string strSourceS = ddlSource_Type_S.SelectedValue;
string strSourceE = ddlSource_Type_E.SelectedValue;
if (int.Parse(strSourceS) > int.Parse(strSourceE))
{
strSourceS = ddlSource_Type_E.SelectedValue;
strSourceE = ddlSource_Type_S.SelectedValue;
}
ParameterList.Clear();
ParameterList.Add(SLP_InvoDateRange.StartDate);
ParameterList.Add(SLP_InvoDateRange.EndDate);
ParameterList.Add(strSourceS);
ParameterList.Add(strSourceE);
ParameterList.Add(rblTaxType.Text);
CAAModel.RecordingInvoiceDetail BCO = new CAAModel.RecordingInvoiceDetail(ConntionDB);
dt = BCO.GetInvoiceBatch(ParameterList);
if (dt.Rows.Count == 0)
{ ErrorMsgLabel.Text = "查無資料"; }
else
{
string s_FileName = HttpUtility.UrlEncode("批次電子發票開立明細表." + ((RType == ReportType.PDF) ? "pdf" : "xls"), System.Text.Encoding.UTF8);
string s_rptFilePath = Server.MapPath("./REPORT/CAA041/CAA04R01.rpt");
LoadCrystalReport(s_FileName, s_rptFilePath, dt, ((RType == ReportType.PDF) ? ExportFormatType.PortableDocFormat : ExportFormatType.Excel));
}
}
private void InitData()
{
Load_Source_Type(ddlSource_Type_S);
Load_Source_Type(ddlSource_Type_E);
rblTaxType.Items.Clear();
rblTaxType.Items.Add(new ListItem("ALL", ""));
CAAModel.RecordingInvoiceDetail BCO = new CAAModel.RecordingInvoiceDetail(ConntionDB);
DataTable dt;
dt = BCO.GetTaxType();
for (int i = 0; i < dt.Rows.Count; i++)
{ rblTaxType.Items.Add(new ListItem(dt.Rows[i]["ENUMTYPENAME"].ToString(), dt.Rows[i]["ENUMVALUE"].ToString())); }
rblTaxType.SelectedIndex = 0;
}