protected void GridViewSuppl_Load(object sender, EventArgs e)
{
gridbind_suppl();
if (Request.QueryString["readonly"] != null)
{
ModuleSupport.DisableControls(this, allowViewState);
}
}
private void LoadModule()
{
object[] pargrp = new object[1] {
Session["GroupID"]
};
connESecurity.ExecReader(Q_LISTMODULE, pargrp, dbtimeout);
int loopcounter = 0;
while (connESecurity.hasRow())
{
loopcounter++;
if (loopcounter == 1)
{
if (!IsPostBack)
{
try
{
Label1.Text = connESecurity.GetFieldValue(2);
}
catch (Exception ex1)
{
Response.Write("<!-- " + ex1.Message.Replace("-->", "--)") + " -->\n");
ModuleSupport.LogError(this, ex1);
}
}
}
LinkButton lb = new LinkButton();
lb.Text = connESecurity.GetFieldValue(3); //modulename
lb.Font.Bold = true;
lb.Font.Name = "verdana";
lb.Font.Size = FontUnit.Point(8);
lb.ForeColor = Color.RoyalBlue;
if (connESecurity.GetFieldValue(2) == ConfigurationSettings.AppSettings["ModuleID"])
{
lb.ForeColor = Color.BlueViolet;
lb.Attributes.Add("onclick", "return false;");
}
else
{
lb.Click += new EventHandler(lb_Click);
}
PlaceHolder1.Controls.Add(lb);
PlaceHolder1.Controls.Add(new LiteralControl(" "));
}
if (loopcounter <= 1)
{
PlaceHolder1.Visible = false;
}
}
public void rundec(bool rerun)
{
LBL_MSG.Text = "";
string retmsg = "";
if (Request.QueryString["readonly"] != null)
{
return;
}
object[] param = new object[] { regno, Stage, rerun };
DataTable dtRunDecSys = conn.GetDataTable(
"EXEC SP_APPDECSYSRUN @1,@2,@3", param, dbtimeout);
for (int i = 0; i < dtRunDecSys.Rows.Count; i++)
{
try
{
DecSystem DecSys = new DecSystem(conn);
string DecId = dtRunDecSys.Rows[i]["DEC_ID"].ToString();
param = new object[] { regno, Stage };
string ResultID = DecSys.execute(
"AND [APP].[AP_REGNO]=@1", param,
DecId, Stage, hashFieldFWsend);
param = new object[] { regno, DecId, Stage, ResultID };
conn.ExecuteNonQuery(
"EXEC SP_APPDECSYSRES @1,@2,@3,@4", param, dbtimeout);
}
catch (Exception ex)
{
ModuleSupport.LogError(this.Page, ex);
string errDecId = dtRunDecSys.Rows[i]["DEC_ID"].ToString();
retmsg += errDecId + ", ";
}
}
if (retmsg != "")
{
LBL_MSG.Text = "<font color=Red> Execution error on engine: " + retmsg.Substring(0, retmsg.Length - 2) + "</font>";
}
}
private void write_read_to_template()
{
string Q_TEMPLATEPROC = "exec USP_EXPORTEXCEL_LISTPROCEDURE @1 ";
string Q_TEMPLATEDETAIL = "exec USP_EXPORTEXCEL_LISTDETAIL @1, @2 ";
string xtpl = "CALC";
string appid = reffnumber.Text.Trim();
string resultPath = Server.MapPath("../Templates/Download");
string resultFilename = appid + ".xls";
string resultFullpath = resultPath + "/" + resultFilename;
string tplFile = Server.MapPath("../Templates/Master/LoanCalculator.xlt");
if (!Directory.Exists(resultPath))
{
Directory.CreateDirectory(resultPath);
}
if (File.Exists(resultFullpath))
{
File.Delete(resultFullpath);
}
xExcel objExcel = new xExcel();
Microsoft.Office.Interop.Excel.Range objCell;
objExcel.OpenFile(tplFile, "");
#region Fill Data
object[] parproc = new object[1] {
xtpl
};
DataTable dt_proc = conn.GetDataTable(Q_TEMPLATEPROC, parproc, dbtimeout);
for (int k = 0; k < dt_proc.Rows.Count; k++)
{
int procseq = (int)dt_proc.Rows[k]["proc_seq"];
string procname = dt_proc.Rows[k]["proc_name"].ToString();
object[] pardet = new object[2] {
xtpl, procseq
};
DataTable dt_field = conn.GetDataTable(Q_TEMPLATEDETAIL, pardet, dbtimeout);
DataTable dt_data = conn.GetDataTable("EXEC " + procname + " '" + appid + "'", null, dbtimeout);
if (dt_data.Rows.Count > 0)
{
for (int i = 0; i < dt_field.Rows.Count; i++)
{
try
{
string SheetNm = dt_field.Rows[i]["sheet"].ToString();
int col = (int)dt_field.Rows[i]["col"];
int row = (int)dt_field.Rows[i]["row"];
string Field = dt_field.Rows[i]["field"].ToString();
object objValue = dt_data.Rows[0][Field].ToString();
string strObject = objValue.ToString();
objExcel.excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)objExcel.excelSheets[SheetNm];
objCell = (Microsoft.Office.Interop.Excel.Range)objExcel.excelWorksheet.Cells[row, col];
objCell.Value2 = objValue;
}
catch (Exception e2)
{
ModuleSupport.LogError(this.Page, e2);
}
}
}
}
#endregion
#region write lending data
write_lending_toexcel(reffnumber.Text.Trim(), "CC", 15, objExcel);
write_lending_toexcel(reffnumber.Text.Trim(), "UL", 48, objExcel);
write_lending_toexcel(reffnumber.Text.Trim(), "SL", 66, objExcel);
write_lending_toexcel(reffnumber.Text.Trim(), "MKI", 89, objExcel);
write_lending_toexcel(reffnumber.Text.Trim(), "INV", 107, objExcel);
write_lending_toexcel(reffnumber.Text.Trim(), "PRK", 120, objExcel);
#endregion
#region read from excel
double iue, tue, tot_monthly_installment, mue1, mue2, income_multiplier1, income_multiplier2, max_dbr1, max_dbr2, dbr_actual, proposal_limit, installment, max_installment;
string estimated_cost, final_recommendation, incomeexpense_result;
NameValueCollection key = new NameValueCollection();
staticFramework.saveNVC(key, reffnumber);
NameValueCollection Fields = new NameValueCollection();
iue = read_excel_cell_numeric(133, 3, objExcel); staticFramework.saveNVC(Fields, "iue", iue);
tue = read_excel_cell_numeric(133, 14, objExcel); staticFramework.saveNVC(Fields, "tue", tue);
tot_monthly_installment = read_excel_cell_numeric(133, 20, objExcel); staticFramework.saveNVC(Fields, "tot_monthly_installment", tot_monthly_installment);
//mue1 = read_excel_cell_numeric(168, 14, objExcel); staticFramework.saveNVC(Fields, "mue1", mue1);
//mue2 = read_excel_cell_numeric(168, 17, objExcel); staticFramework.saveNVC(Fields, "mue2", mue2);
//income_multiplier1 = read_excel_cell_numeric(169, 14, objExcel); staticFramework.saveNVC(Fields, "income_multiplier1", income_multiplier1);
//income_multiplier2 = read_excel_cell_numeric(169, 17, objExcel); staticFramework.saveNVC(Fields, "income_multiplier2", income_multiplier2);
max_dbr1 = read_excel_cell_numeric(180, 8, objExcel); staticFramework.saveNVC(Fields, "max_dbr1", max_dbr1);
//max_dbr2 = read_excel_cell_numeric(170, 17, objExcel); staticFramework.saveNVC(Fields, "max_dbr2", max_dbr2);
dbr_actual = read_excel_cell_numeric(180, 17, objExcel); staticFramework.saveNVC(Fields, "dbr_actual", dbr_actual);
proposal_limit = read_excel_cell_numeric(176, 22, objExcel); staticFramework.saveNVC(Fields, "proposal_limit", proposal_limit);
max_installment = read_excel_cell_numeric(177, 22, objExcel); staticFramework.saveNVC(Fields, "max_installment", max_installment);
installment = read_excel_cell_numeric(177, 17, objExcel); staticFramework.saveNVC(Fields, "installment", installment);
estimated_cost = read_excel_cell_string(179, 8, objExcel); staticFramework.saveNVC(Fields, "estimated_cost", estimated_cost);
try
{
estimated_cost = read_excel_cell_numeric(179, 8, objExcel).ToString(); staticFramework.saveNVC(Fields, "estimated_cost", estimated_cost);
} catch {}
incomeexpense_result = read_excel_cell_string(180, 22, objExcel); staticFramework.saveNVC(Fields, "incomeexpense_result", incomeexpense_result);
final_recommendation = read_excel_cell_string(183, 8, objExcel); staticFramework.saveNVC(Fields, "final_recommendation", final_recommendation);
staticFramework.save(Fields, key, "appfincal_summary", conn);
double highest_limitcc_mob06, highest_limitcc_mob12, lowest_limitcc_mob06, lowest_limitcc_mob12;
string bic_result_temp;
Fields.Clear();
highest_limitcc_mob06 = read_excel_cell_numeric(163, 14, objExcel); staticFramework.saveNVC(Fields, "highest_limitcc_mob06", highest_limitcc_mob06);
highest_limitcc_mob12 = read_excel_cell_numeric(163, 22, objExcel); staticFramework.saveNVC(Fields, "highest_limitcc_mob12", highest_limitcc_mob12);
lowest_limitcc_mob06 = read_excel_cell_numeric(164, 14, objExcel); staticFramework.saveNVC(Fields, "lowest_limitcc_mob06", lowest_limitcc_mob06);
lowest_limitcc_mob12 = read_excel_cell_numeric(164, 22, objExcel); staticFramework.saveNVC(Fields, "lowest_limitcc_mob12", lowest_limitcc_mob12);
bic_result_temp = read_excel_cell_string(166, 14, objExcel); staticFramework.saveNVC(Fields, "bic_result_temp", bic_result_temp);
staticFramework.save(Fields, key, "appfincal_bicsummary", conn);
try
{
objExcel.CloseFile(resultFullpath);
objExcel.stopExcel();
}
catch (Exception e3)
{
ModuleSupport.LogError(this.Page, e3);
try
{
objExcel.CloseFile();
objExcel.stopExcel();
}
catch { }
}
#endregion
}
private string CreateWord(string wtpl, string regno, string var_user)
{
string mStatus = "Template parameter not set!";
bool bSukses = true;
int postrace = 0;
/// Mengambil application root
string path_in = SvrTplPhysic;
string path_out = SvrPathPhysic;
string file_dot = wtpl + ".DOT";
string fileNm = wtpl + "-" + regno + ".DOC";
/// Cek apakah file templatenya (input) ada atau tidak
if (!File.Exists(path_in + file_dot))
{
throw new Exception("File Template tidak ada!");
}
/// Cek direktori untuk menyimpan file hasil export (output)
if (!Directory.Exists(path_out))
{
Directory.CreateDirectory(path_out);
}
/// Cek file untuk menyimpan file hasil export (output)
if (File.Exists(path_out + fileNm))
{
File.Delete(path_out + fileNm);
}
// Word object variables
object objFileIn = path_in + file_dot;
object objFileOut = path_out + fileNm;
object oMissing = System.Reflection.Missing.Value;
object oTrue = true;
object oFalse = false;
Microsoft.Office.Interop.Word.Application wordApp = null;
Microsoft.Office.Interop.Word.Document wordDoc = null;
//Array of running winword process id in Taskbar
ArrayList orgId = new ArrayList();
ArrayList newId = new ArrayList();
//Collecting Existing Winword in Taskbar
Process[] oldProcess = Process.GetProcessesByName("WINWORD");
foreach (Process thisProcess in oldProcess)
{
orgId.Add(thisProcess);
}
try
{
postrace = 11;
wordApp = new Microsoft.Office.Interop.Word.Application();
wordApp.Visible = false;
/// Collecting Existing Winword in Taskbar
postrace = 12;
Process[] newProcess = Process.GetProcessesByName("WINWORD");
foreach (Process thisProcess in newProcess)
{
newId.Add(thisProcess);
}
//SupportTools.saveProcessWord(wordApp, newId, orgId, conn);
//wordDoc = wordApp.Documents.Open(ref objFileIn, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
// ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing);
postrace = 13;
wordDoc = wordApp.Documents.Add(ref objFileIn, ref oMissing, ref oMissing, ref oMissing);
postrace = 14;
wordDoc.Activate();
}
catch (Exception e1)
{
string myedata = "CreateWord(" + wtpl + ", " + regno + ", " + var_user + ") at position: " + postrace.ToString() +
" objFileIn set to: " + objFileIn.ToString();
ModuleSupport.LogError(myedata, this.Page, e1);
try
{
if (wordDoc != null)
{
wordDoc.Close(ref oFalse, ref oMissing, ref oMissing);
wordDoc = null;
}
if (wordApp != null)
{
wordApp.Quit(ref oMissing, ref oMissing, ref oMissing);
wordApp = null;
// Killing Proses after Export
for (int x = 0; x < newId.Count; x++)
{
Process xnewId = (Process)newId[x];
bool bSameId = false;
for (int z = 0; z < orgId.Count; z++)
{
Process xoldId = (Process)orgId[z];
if (xnewId.Id == xoldId.Id)
{
bSameId = true;
break;
}
}
if (!bSameId)
{
xnewId.Kill();
break;
}
}
}
}
catch (Exception e2)
{
ModuleSupport.LogError(this.Page, e2);
}
throw e1;
}
Microsoft.Office.Interop.Word.Bookmarks wordBookMark = wordDoc.Bookmarks;
Microsoft.Office.Interop.Word.Bookmark oBook;
#region Fill Data
int iItem = 0;
object[] parproc = new object[1] {
wtpl
};
DataTable dt_proc = conn.GetDataTable(Q_TEMPLATEPROC, parproc, dbtimeout);
for (int k = 0; k < dt_proc.Rows.Count; k++)
{
int procseq = (int)dt_proc.Rows[k]["PROC_SEQ"];
string procname = dt_proc.Rows[k]["PROC_NAME"].ToString();
object[] pardet = new object[2] {
wtpl, procseq
};
DataTable dt_field = conn.GetDataTable(Q_TEMPLATEDETAIL, pardet, dbtimeout);
DataTable dt_data = conn.GetDataTable("EXEC " + procname + " '" + regno + "'", null, dbtimeout);
for (int j = 0; j < dt_data.Rows.Count; j++)
{
for (int i = 0; i < dt_field.Rows.Count; i++)
{
try
{
postrace = 21;
object Bookmark = dt_field.Rows[i]["BOOKMARK"];
string Field = dt_field.Rows[i]["FIELD"].ToString();
object objValue = dt_data.Rows[j][Field].ToString();
string strObject = objValue.ToString();
if (dt_field.Rows[i]["WORDDET_TYPE"].ToString().ToUpper() == "TABLE")
{
strObject = strObject + "\n";
}
if (wordBookMark.Exists(Bookmark.ToString()))
{
postrace = 22;
//oBook = wordBookMark.Item(ref Bookmark);
oBook = wordBookMark.get_Item(ref Bookmark);
oBook.Select();
postrace = 23;
oBook.Range.Text = strObject;
}
iItem++;
}
catch (Exception e2)
{
string myedata = "CreateWord at filling data seq (k:" + k.ToString() + ", j:" + j.ToString() + "i:" + i.ToString() + ") at position: " + postrace.ToString();
ModuleSupport.LogError(myedata, this.Page, e2);
}
}
}
}
#endregion
/// simpan hasil export
if (iItem > 0)
{
//wordDoc.SaveAs(ref objFileOut, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
// ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing);
wordDoc.SaveAs(ref objFileOut, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing);
System.Threading.Thread.Sleep(TimeSpan.FromSeconds(3));
bSukses = true;
mStatus = "Pembuatan File Berhasil";
}
else
{
bSukses = false;
mStatus = "Tidak ada data utk membuat File";
}
try
{
if (wordDoc != null)
{
wordDoc.Close(ref oFalse, ref oMissing, ref oMissing);
wordDoc = null;
}
if (wordApp != null)
{
wordApp.Quit(ref oMissing, ref oMissing, ref oMissing);
wordApp = null;
// Killing Proses after Export
for (int x = 0; x < newId.Count; x++)
{
Process xnewId = (Process)newId[x];
bool bSameId = false;
for (int z = 0; z < orgId.Count; z++)
{
Process xoldId = (Process)orgId[z];
if (xnewId.Id == xoldId.Id)
{
bSameId = true;
break;
}
}
if (!bSameId)
{
xnewId.Kill();
break;
}
}
}
}
catch (Exception e3)
{
ModuleSupport.LogError(this.Page, e3);
}
return(mStatus);
}
private string CreateExcel(string xtpl, string regno, string var_user)
{
string mStatus = "Template parameter not set!";
bool bSukses = true;
/// Mengambil application root
string path_in = SvrTplPhysic;
string path_out = SvrPathPhysic;
string file_xlt = xtpl + ".XLT";
string fileNm = xtpl + "-" + regno + ".XLS";
/// Cek apakah file templatenya (input) ada atau tidak
if (!File.Exists(path_in + file_xlt))
{
throw new Exception("File Template tidak ada!");
}
/// Cek direktori untuk menyimpan file hasil export (output)
if (!Directory.Exists(path_out))
{
Directory.CreateDirectory(path_out);
}
/// Cek file untuk menyimpan file hasil export (output)
if (File.Exists(path_out + fileNm))
{
File.Delete(path_out + fileNm);
}
// rony's excel object variables
xExcel objExcel = new xExcel();
objExcel.OpenFile(path_in + file_xlt, "");
#region Fill Data
object[] parproc = new object[1] {
xtpl
};
DataTable dt_proc = conn.GetDataTable(Q_TEMPLATEPROC, parproc, dbtimeout);
for (int k = 0; k < dt_proc.Rows.Count; k++)
{
int procseq = (int)dt_proc.Rows[k]["PROC_SEQ"];
string procname = dt_proc.Rows[k]["PROC_NAME"].ToString();
object[] pardet = new object[2] {
xtpl, procseq
};
DataTable dt_field = conn.GetDataTable(Q_TEMPLATEDETAIL, pardet, dbtimeout);
DataTable dt_data = conn.GetDataTable("EXEC " + procname + " '" + regno + "'", null, dbtimeout);
if (dt_data.Rows.Count > 0)
{
for (int i = 0; i < dt_field.Rows.Count; i++)
{
try
{
string SheetNm = dt_field.Rows[i]["SHEET"].ToString();
int col = (int)dt_field.Rows[i]["COL"];
int row = (int)dt_field.Rows[i]["ROW"];
string Field = dt_field.Rows[i]["FIELD"].ToString();
object objValue = dt_data.Rows[0][Field].ToString();
string strObject = objValue.ToString();
objExcel.excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)objExcel.excelSheets[0];
Microsoft.Office.Interop.Excel.Range objCell = (Microsoft.Office.Interop.Excel.Range)objExcel.excelWorksheet.Cells[row, col];
objCell.Value2 = objValue;
}
catch (Exception e2)
{
mStatus = "Terjadi error saat mengisi data";
ModuleSupport.LogError(this.Page, e2);
}
}
}
}
#endregion
try
{
objExcel.CloseFile(path_out + fileNm);
objExcel.stopExcel();
mStatus = "Pembuatan File Berhasil";
}
catch (Exception e3)
{
ModuleSupport.LogError(this.Page, e3);
mStatus = "Pembuatan File Gagal";
try
{
objExcel.CloseFile();
objExcel.stopExcel();
}
catch { }
}
return(mStatus);
}
protected void Grid_Load(object sender, EventArgs e)
{
if (!Grid.IsCallback)
{
return;
}
ddl = (USC_SearchCascade)ModuleSupport.FindControlByClientID(this.Page, oCascade.Value);
string QueryString = ddl.QueryString;
object[] QueryParam = ddl.QueryParam;
QueryString = QueryString.ToUpper().Replace("SELECT ", "SELECT TOP 0 ");
dt = ddl.conn.GetDataTable(QueryString, QueryParam, ddl.DbTimeOut);
Grid.AutoGenerateColumns = false;
GridViewCommandColumn cmd = new GridViewCommandColumn();
cmd.ClearFilterButton.Visible = true;
cmd.ShowSelectCheckbox = false;
Grid.Columns.Add(cmd);
string FieldNames = "";
string strfilter = "";
for (int i = 0; i < dt.Columns.Count; i++)
{
//if (dt.Columns[i].DataType.ToString() == "System.DateTime")
//{
// GridViewDataDateColumn gvddate = new GridViewDataDateColumn();
// gvddate.FieldName = dt.Columns[i].ColumnName;
// gvddate.Settings.FilterMode = ColumnFilterMode.Value;
// gvddate.Settings.SortMode = DevExpress.XtraGrid.ColumnSortMode.Value;
// Grid.Columns.Add(gvddate);
// if (gvddate.FilterExpression != "")
// strfilter += " AND " + gvddate.FilterExpression;
// FieldNames += ";" + dt.Columns[i].ColumnName;
//}
//else
//{
GridViewDataTextColumn c = new GridViewDataTextColumn();
c.PropertiesTextEdit.EncodeHtml = false;
c.FieldName = dt.Columns[i].ColumnName;
c.Settings.FilterMode = ColumnFilterMode.DisplayText;
c.Settings.AutoFilterCondition = AutoFilterCondition.Contains;
c.Settings.SortMode = DevExpress.XtraGrid.ColumnSortMode.Value;
Grid.Columns.Add(c);
if (c.FilterExpression != "")
{
strfilter += " AND " + c.FilterExpression;
}
FieldNames += ";" + dt.Columns[i].ColumnName;
//}
}
Grid.JSProperties["cp_FieldNames"] = FieldNames.Substring(1);
for (int i = 0; i < Grid.Columns.Count; i++)
{
if (Grid.Columns[i] is GridViewDataTextColumn)
{
GridViewDataTextColumn c = (GridViewDataTextColumn)Grid.Columns[i];
if (c.FieldName != "")
{
switch (dt.Columns[c.FieldName].DataType.ToString())
{
case "System.Decimal":
case "System.Double":
c.PropertiesTextEdit.DisplayFormatString = "###,##0.00";
c.CellStyle.Wrap = DevExpress.Utils.DefaultBoolean.False;
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.int":
c.PropertiesTextEdit.DisplayFormatString = "###,##0";
c.CellStyle.Wrap = DevExpress.Utils.DefaultBoolean.False;
break;
case "System.DateTime":
c.PropertiesTextEdit.DisplayFormatString = "dd MMMM yyyy";
c.CellStyle.Wrap = DevExpress.Utils.DefaultBoolean.False;
break;
case "System.String":
if (dt.Columns[0].MaxLength <= 50)
{
c.CellStyle.Wrap = DevExpress.Utils.DefaultBoolean.False;
}
break;
}
}
}
}
}
