JavaScriptとACCESSでちょこっとやってみたその7
そしてDailyReportDownload.jsです。データベースのデータをCSVで吐き出す部分
IE10でまともに動かなかったので原因調査中。画面描画分のCSVデータダウンロードの
箇所はかなり悩みました。
日付で範囲検索出来ないので、そこは改善したい。これもまたベタベタコード
参照サイト
http://blog.goo.ne.jp/xmldtp/e/bdc416e4985f02f60b0399864d49fd3f
DailyReportDownload.js
IE10でまともに動かなかったので原因調査中。画面描画分のCSVデータダウンロードの
箇所はかなり悩みました。
日付で範囲検索出来ないので、そこは改善したい。これもまたベタベタコード
参照サイト
http://blog.goo.ne.jp/xmldtp/e/bdc416e4985f02f60b0399864d49fd3f
DailyReportDownload.js
var EmpCd=location.search.substring(1); //ログイン画面から担当者CD取得 onload = init; onunload = dbClose; //* //* 初期設定をする関数 //* function init() { //実績日表示 txtReportday = document.getElementById("txtReportday"); var txtdate = new DateFormat("yyyy/MM/dd"); txtdate = txtdate.format(new Date()); txtReportday.value = txtdate; //計画日のテキストボックス制御 txtReportday.onblur = function () { this.style.backgroundColor = "#ffffff"; } document.getElementById("btnDaySearch").onclick = function() { txtDateUpdate(txtReportday); } //画面ダウンロードボタン制御 document.getElementById("btnViewDownload").onclick = function () { ViewDownload(); } //全件ダウンロードボタン制御 document.getElementById("btnAllDownload").onclick = function () { AllDownload() } dbConnect(); //データベース接続 // 担当者をセットして画面の初期表示 EmpCdisplay(EmpCd); DisPlayChecker(); } //* //* 終了処理 //* function OperateEnd() { dbClose(); (window.open('', '_self').opener = window).close(); } //* //* 報告日更新時の処理 //* function txtDateUpdate(obj) { DisPlayChecker(); obj.style.backgroundColor = "#ffffff"; } //* //* 報告日にデータが存在するかを判別して表示する関数 //* function DisPlayChecker() { //実績日チェック var Reportday = document.getElementById('txtReportday').value; var EmpID = EmpCd; if (ChckDate(Reportday)) { } else { return; } var mySql = " SELECT COUNT(WorkplanID) AS WIDCount" + " FROM t_workplan" + " WHERE (PlanDate>='" + Reportday + "') AND (Empid='" + EmpID + "') AND (DeleteFlg = '0' );"; var recordSet = database.Execute(mySql); var wPlanCount = recordSet(0); var mySql = " SELECT COUNT(ReportID) AS RIDCount " + " FROM t_dailyreport " + " WHERE (Reportday>='" + Reportday + "') AND (Empid='" + EmpID + "') AND (DeleteFlg = '0' );"; var recordSet = database.Execute(mySql); var ReportCount = recordSet(0); //日報の登録がなければ画面をクリアする if (ReportCount == 0) { //0件の場合は画面をクリアする。 TableClear(); } //日報の登録があればデータを表示する。 if (ReportCount != 0) { TableClear(); dataDisplay(); } recordSet.Close(); recordSet = null; } //* //* 実績データを表示する関数 //* function dataDisplay() { try { //作業計画登録なし var cWork = "-"; //明細部分 var Reportday = document.getElementById('txtReportday').value; ChckDate(Reportday); var EmpId = EmpCd; var mySql = " SELECT t_dailyreport.ReportID, t_dailyreport.WorkplanID, m_Administration.AdministrationID, m_Administration.AdministrationDetail," + " m_ActionPlan.ActionplanID, m_ActionPlan.ActionPlan, m_Matter.MatterID, m_Matter.MatterName, m_Task.TaskID, m_Task.taskName," + " t_dailyreport.Dailymemo, t_dailyreport.Reference, t_dailyreport.Reportday, t_dailyreport.Taskstart, t_dailyreport.Taskend," + " t_dailyreport.Workinghours, m_Emp.Empid, m_Emp.Empname" + " FROM m_Emp INNER JOIN ((t_dailyreport INNER JOIN (((m_Administration INNER JOIN m_ActionPlan ON m_Administration.AdministrationID = m_ActionPlan.AdministrationID)" + " INNER JOIN m_Matter ON (m_Matter.AdministrationID = m_Administration.AdministrationID) AND (m_ActionPlan.ActionplanID = m_Matter.ActionplanID))" + " INNER JOIN m_Businessyear ON m_Administration.Businessyear = m_Businessyear.BusinessYear) ON (t_dailyreport.MatterID = m_Matter.MatterID)" + " AND (t_dailyreport.ActionplanID = m_ActionPlan.ActionplanID) AND (t_dailyreport.AdministrationID = m_Administration.AdministrationID))" + " INNER JOIN m_Task ON t_dailyreport.TaskID = m_Task.TaskID) ON m_Emp.Empid = t_dailyreport.Empid" + " WHERE (((t_dailyreport.Reportday)>='"+ Reportday +"') AND ((m_Emp.Empid)='"+ EmpId +"')" + " AND ((t_dailyreport.DeleteFlg)='0') AND ((m_Businessyear.ShowFlg)='0')) ORDER BY t_dailyreport.Reportday DESC,t_dailyreport.ReportID;"; var recordSet = database.Execute(mySql); var counter = 1; while (!recordSet.EOF) { var table1 = document.getElementById("table1"); var row1 = table1.insertRow(counter); var outhtml = new Array(19); var cellname = new Array(19); //実績明細表示 for (var i = 0; i < 18; i++) { var classname = String('D' + i); //そのままレコード表示 if (i ==0 || i == 1){ cellname[i] = row1.insertCell(i); cellname[i].setAttribute("class", classname); cellname[i].className = classname; outhtml[i] = '<span id="' + classname + '-' + counter + '">' + recordSet(i) + '</span>'; cellname[i].innerHTML = outhtml[i]; } //計画表示 if (i == 2) { var WPMemo = ""; if (recordSet(1) == cWork || recordSet(1) == null || recordSet(1) == undefined) { cellname[i] = row1.insertCell(i); cellname[i].setAttribute("class", classname); cellname[i].className = classname; WPMemo = cWork; outhtml[i] = '<span id="' + classname + '-' + counter + '">' + WPMemo + '</span>'; } else { mySubSql = " SELECT t_workplan.WorkplanID, t_workplan.Planmemo, t_workplan.Empid, t_workplan.PlanDate, t_dailyreport.Reportday " + " FROM t_dailyreport INNER JOIN t_workplan ON (t_dailyreport.MatterID = t_workplan.MatterID) AND (t_dailyreport.ActionplanID = t_workplan.ActionplanID)" + " AND (t_dailyreport.AdministrationID = t_workplan.AdministrationID) AND (t_dailyreport.WorkplanID = t_workplan.WorkplanID)" + " WHERE (((t_workplan.WorkplanID)='" + recordSet(1) + "') AND ((t_workplan.Empid)='" + EmpId + "') AND ((t_dailyreport.Reportday)='" + recordSet(12) + "'));"; SubrecordSet = database.Execute(mySubSql); cellname[i] = row1.insertCell(i); cellname[i].setAttribute("class", classname); cellname[i].className = classname; var WPMemo = SubrecordSet(1); outhtml[i] = '<span id="' + classname + '-' + counter + '">' + WPMemo + '</span>'; cellname[i].innerHTML = outhtml[i]; SubrecordSet.Close(); SubrecordSet = null; } } //1列ずれるので表示箇所を調整 if (i >= 2) { var k = i + 1; cellname[k] = row1.insertCell(k); cellname[k].setAttribute("class", classname); cellname[k].className = classname; outhtml[k] = '<span id="' + classname + '-' + counter + '">' + recordSet(i) + '</span>'; cellname[k].innerHTML = outhtml[k]; } } recordSet.MoveNext(); counter++; } recordSet.Close(); recordSet = null; } catch(error) { alert(error.number + "\n" + error.description); } } //* //* 登録されている担当者を表示する関数 //* function EmpCdisplay(loginId) { var mySql = "SELECT * FROM m_Emp WHERE ( DeleteFlg Is Null) ORDER BY EmpID"; var recordSet = database.Execute(mySql); document.getElementById("EmpID").innerHTML = ""; var tempHtml = "担当者ID: <select name=\"selectEmpId\" id=\"selectEmpId\">\n"; tempHtml = tempHtml + "\t<option value=\"0\">選択してください。</option>\n"; while (!recordSet.EOF) { var optionval = recordSet(0); //オプション値指定がある場合はそのオプションを選択状態にする。 if (optionval == loginId) { tempHtml = tempHtml + '\t<option value="' + optionval + '" selected >' + recordSet(0) + ":" + recordSet(1) + '<\/option>\n'; } else { tempHtml = tempHtml + '\t<option value="' + optionval + '"\">' + recordSet(0) + ":" + recordSet(1) + '<\/option>\n'; } recordSet.MoveNext(); } tempHtml = tempHtml + "</select>"; document.getElementById("EmpID").innerHTML = tempHtml; selEmpid = document.getElementById("selectEmpId"); selEmpid.onchange = function () { selectEmpIdCdchange(this); } recordSet.Close(); recordSet = null; } //* //* 社員選択時の動作 //* function selectEmpIdCdchange(obj) { var workCd = obj.value; if (obj.selectedIndex == 0) { var selEmp = document.getElementById("EmpIDdisPlay"); alert("担当者を選択してください。"); selEmp.focus(); return; } else { EmpCd = workCd; } } //* //* 画面表示分ダウンロード //* function ViewDownload() { try { //テキストファイルをデスクトップに保存 var fs = new ActiveXObject("Scripting.FileSystemObject"); var outf = fs.CreateTextFile("download_Dailyview.csv", true); var tbl = document.getElementById("table1"); // 行に対するループ var rows = tbl.rows; var outputFile = ""; for (var i = 0, len = rows.length; i < len; i++) { var cols = rows[i].cells.length; var conmactl = cols - 1; // 列に対するループ for (var j=0; j<cols; j++) { var dlitem = rows[i].cells[j].innerText; outputFile = outputFile + "\"" + dlitem + "\""; if (j < conmactl) { outputFile = outputFile + "," } if (j == conmactl) { outputFile = outputFile + "\n"; } } } outf.Write(outputFile); } catch (error) { alert(error.number + "\n" + error.description); } finally { alert("デスクトップにファイルを保存しました。"); outf.Close(); } } //* //* 全件ダウンロード //* function AllDownload() { if (window.confirm('明細の全件データをダウンロードします。\nよろしいですか?')) { try { //テキストファイルをデスクトップに保存 var fs = new ActiveXObject("Scripting.FileSystemObject"); var outf = fs.CreateTextFile("download_DailyAll.csv", true); var outputFile = ""; //作業計画登録なし var cWork = "-"; //タイトル部分 outputFile = outputFile + "\"" + "NO" + "\"" + ","; outputFile = outputFile + "\"" + "計画ID" + "\"" + ","; outputFile = outputFile + "\"" + "作業計画" + "\"" + ","; outputFile = outputFile + "\"" + "方針ID" + "\"" + ","; outputFile = outputFile + "\"" + "方針" + "\"" + ","; outputFile = outputFile + "\"" + "計画ID" + "\"" + ","; outputFile = outputFile + "\"" + "計画" + "\"" + ","; outputFile = outputFile + "\"" + "案件ID" + "\"" + ","; outputFile = outputFile + "\"" + "案件名" + "\"" + ","; outputFile = outputFile + "\"" + "作業ID" + "\"" + ","; outputFile = outputFile + "\"" + "作業名" + "\"" + ","; outputFile = outputFile + "\"" + "実績・補足" + "\"" + ","; outputFile = outputFile + "\"" + "関連資料" + "\"" + ","; outputFile = outputFile + "\"" + "作業日" + "\"" + ","; outputFile = outputFile + "\"" + "作業開始" + "\"" + ","; outputFile = outputFile + "\"" + "作業終了" + "\"" + ","; outputFile = outputFile + "\"" + "作業時間" + "\"" + ","; outputFile = outputFile + "\"" + "担当者CD" + "\"" + ","; outputFile = outputFile + "\"" + "担当者名" + "\"" + "\n"; //実績部分 var Reportday = document.getElementById('txtReportday').value; ChckDate(Reportday); var EmpId = EmpCd; var mySql = " SELECT t_dailyreport.ReportID, t_dailyreport.WorkplanID, m_Administration.AdministrationID, m_Administration.AdministrationDetail," + " m_ActionPlan.ActionplanID, m_ActionPlan.ActionPlan, m_Matter.MatterID, m_Matter.MatterName, m_Task.TaskID, m_Task.taskName," + " t_dailyreport.Dailymemo, t_dailyreport.Reference, t_dailyreport.Reportday, t_dailyreport.Taskstart, t_dailyreport.Taskend," + " t_dailyreport.Workinghours, m_Emp.Empid, m_Emp.Empname" + " FROM m_Emp INNER JOIN ((t_dailyreport INNER JOIN (((m_Administration INNER JOIN m_ActionPlan ON m_Administration.AdministrationID = m_ActionPlan.AdministrationID)" + " INNER JOIN m_Matter ON (m_Matter.AdministrationID = m_Administration.AdministrationID) AND (m_ActionPlan.ActionplanID = m_Matter.ActionplanID))" + " INNER JOIN m_Businessyear ON m_Administration.Businessyear = m_Businessyear.BusinessYear) ON (t_dailyreport.MatterID = m_Matter.MatterID)" + " AND (t_dailyreport.ActionplanID = m_ActionPlan.ActionplanID) AND (t_dailyreport.AdministrationID = m_Administration.AdministrationID))" + " INNER JOIN m_Task ON t_dailyreport.TaskID = m_Task.TaskID) ON m_Emp.Empid = t_dailyreport.Empid" + " WHERE (t_dailyreport.DeleteFlg='0') AND (m_Businessyear.ShowFlg='0') ORDER BY t_dailyreport.Reportday DESC,t_dailyreport.ReportID;"; var recordSet = database.Execute(mySql); var counter = 1; while (!recordSet.EOF) { //実績明細 for (var i = 0; i < 18; i++) { //そのままレコード出力 if (i == 0 || i == 1) { outputFile = outputFile + "\"" + recordSet(i) + "\"" + ","; } //作業計画 if (i == 2) { var WPMemo = ""; if (recordSet(1) == cWork || recordSet(1) == null || recordSet(1) == undefined) { outputFile = outputFile + "\"" + cWork + "\"" + ","; } else { mySubSql = " SELECT t_workplan.WorkplanID, t_workplan.Planmemo, t_workplan.Empid, t_workplan.PlanDate, t_dailyreport.Reportday " + " FROM t_dailyreport INNER JOIN t_workplan ON (t_dailyreport.MatterID = t_workplan.MatterID) AND (t_dailyreport.ActionplanID = t_workplan.ActionplanID)" + " AND (t_dailyreport.AdministrationID = t_workplan.AdministrationID) AND (t_dailyreport.WorkplanID = t_workplan.WorkplanID)" + " WHERE (((t_workplan.WorkplanID)='" + recordSet(1) + "') AND ((t_workplan.Empid)='" + EmpId + "') AND ((t_dailyreport.Reportday)='" + recordSet(12) + "'));"; SubrecordSet = database.Execute(mySubSql); outputFile = outputFile + "\"" + SubrecordSet(1) + "\"" + ","; SubrecordSet.Close(); SubrecordSet = null; } } if (i >= 3) { var k = i - 1; outputFile = outputFile + "\"" + recordSet(k) + "\"" + ","; } //最終カラムの場合は改行 if (i == 17) { outputFile = outputFile + "\"" + recordSet(i) + "\"" + "\n"; } } recordSet.MoveNext(); counter++; } outf.Write(outputFile); } catch (error) { alert(error.number + "\n" + error.description); } finally { recordSet.Close(); recordSet = null; alert("デスクトップにファイルを保存しました。"); outf.Close(); } } else { alert("ダウンロードをキャンセルしました。"); } } //* //* 実績日チェック //* function ChckDate(Reportday) { if (Reportday) { } else { txtReportday = document.getElementById("txtReportday"); alert("実績日は必須です。"); txtReportday.focus(); return false; } return true; } //* //* 担当者コードチェック //* function ChckEmp(Employ) { if (Employ) { } else { var selEmp = document.getElementById('selectEmpId'); alert("担当者を選択してください。"); selEmp.focus(); return false; } return true; } //* //* 案件の文字列分割用 //* function StringSeparator(objString) { var sobj = objString; var separator = "-"; var allayworkCd = sobj.split(separator); var robj = allayworkCd[1]; return robj; } //* //* テーブルの明細部分をクリアする。 //* function TableClear() { var targetTable = document.getElementById('table1'); var allCount = targetTable.rows.length; var headerCount = 1; var footerCount = 0; if (targetTable.tHead) { headerCount = targetTable.tHead.rows.length; } for (var i = headerCount; i < allCount; i++) { targetTable.deleteRow(headerCount); } }
コメント