/// <summary>
///
/// </summary>
/// <param name="FilePath">Excel_Template</param>
/// <param name="datetime">em_month.Text</param>
public B30398(Workbook Workbook, string datetime)
{
_workbook = Workbook;
_emMonthText = datetime;
daoAI3 = new AI3();
daoAM2 = new AM2();
}
public IList search(Int32 yearid, Int64 GrNo, DateTime?dtfrom, DateTime?dtto, int FromCity, Int32 SenderIdno)
{
using (TransportMandiEntities db = new TransportMandiEntities(MultipleDBDAL.strDynamicConString()))
{
var lst = (from CH in db.tblChlnAmntPayment_Head
//join cifrom in db.tblCityMasters on CH.BaseCity_Idno equals cifrom.City_Idno
join cito in db.tblCityMasters on CH.Loc_Idno equals cito.City_Idno
join AM in db.AcntMasts on CH.Party_IdNo equals AM.Acnt_Idno
join Am1 in db.AcntMasts on CH.RcptType_Idno equals Am1.Acnt_Idno into AM2
from Am3 in AM2.DefaultIfEmpty()
select new
{
CH.BaseCity_Idno,
CH.Rcpt_date,
CH.Rcpt_No,
CH.Id,
CH.Inst_Dt,
CH.Inst_No,
FromCity = cito.City_Name,
CH.Year_IdNo,
AM.Acnt_Name,
AM.Acnt_Idno,
ReciptName = Am3.Acnt_Name,
CH.Net_Amnt
}).ToList();
if (GrNo > 0)
{
lst = lst.Where(l => l.Rcpt_No == GrNo).ToList();
}
if (dtto != null)
{
lst = lst.Where(l => Convert.ToDateTime(l.Rcpt_date).Date <= Convert.ToDateTime(dtto).Date).ToList();
}
if (dtfrom != null)
{
lst = lst.Where(l => Convert.ToDateTime(l.Rcpt_date).Date >= Convert.ToDateTime(dtfrom).Date).ToList();
}
if (FromCity > 0)
{
lst = lst.Where(l => l.BaseCity_Idno == FromCity).ToList();
}
if (yearid > 0)
{
lst = lst.Where(l => l.Year_IdNo == yearid).ToList();
}
if (SenderIdno > 0)
{
lst = lst.Where(l => l.Acnt_Idno == SenderIdno).ToList();
}
return(lst);
}
}
public W30722(string programID, string programName) : base(programID, programName)
{
daoRPT = new RPT();
daoAM2 = new AM2();
daoAI2 = new AI2();
daoRamm1 = new RAMM1();
InitializeComponent();
this.Text = _ProgramID + "─" + _ProgramName;
txtDate.DateTimeValue = GlobalInfo.OCF_DATE;
ExportShow.Hide();
}
/// <summary>
/// 寫入 Data_30333.30334 sheet
/// </summary>
/// <returns></returns>
public string Wf30333()
{
Workbook workbook = new Workbook();
try {
string SheetName = "Data_30333.30334";
string lsKindID = "GBF";
//切換Sheet
workbook.LoadDocument(_lsFile);
Worksheet worksheet = workbook.Worksheets[SheetName];
DateTime ldtYMD = new DateTime(1900, 1, 1);
//總列數
int rowIndex = 3; //int li_month_cnt=0;
int RowTotal = 12; //Excel的Column預留空白行數12
int sumRowIndex = RowTotal + rowIndex + 1; //小計行數
int addRowCount = 0; //總計寫入的行數
worksheet.Rows[sumRowIndex][1 - 1].Value = $"{PbFunc.Left(_emMonthText, 4).AsInt() - 1911}小計";
string lsYMD = "";
//讀取資料
DataTable dt = new AM2().ListAM2(lsKindID, $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", ""));//月份
foreach (DataRow row in dt.Rows)
{
if (lsYMD != row["AM2_YMD"].AsString())
{
rowIndex = rowIndex + 1;
lsYMD = row["AM2_YMD"].AsString();
//li_month_cnt = li_month_cnt + 1;
addRowCount++;
//月份
worksheet.Rows[rowIndex][1 - 1].Value = $"{PbFunc.Left(lsYMD, 4).AsInt() - 1911}/{PbFunc.Right(lsYMD, 2)}";
}
//判斷買賣欄位位置
int columnIndex = 0;
switch (row["AM2_IDFG_TYPE"].AsString())
{
case "1":
columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 2 : 3) - 1;
break;
case "2":
columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 4 : 5) - 1;
break;
case "3":
columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 6 : 7) - 1;
break;
case "5":
columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 8 : 9) - 1;
break;
case "6":
columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 10 : 11) - 1;
break;
case "8":
columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 12 : 13) - 1;
break;
case "7":
columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 14 : 15) - 1;
break;
}
//columnIndex指定要寫入的買賣欄位
worksheet.Rows[rowIndex][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal();
}
//刪除空白列
if (RowTotal > addRowCount)
{
worksheet.Rows.Remove(rowIndex + 1, RowTotal - addRowCount);
}
worksheet.ScrollTo(0, 0);
}
catch (Exception ex) {
throw ex;
}
finally {
workbook.SaveDocument(_lsFile);
}
return(MessageDisplay.MSG_OK);
}
