private static AuditDB GetAuditDBInstance()
{
string auditDBPath = new FileUtil().GetAuditDBPath();
if (File.Exists(auditDBPath))
{
if (auditDB == null)
{
auditDB = new AuditDB();
}
auditDB.dbPath = auditDBPath;
return(auditDB);
}
else
{
string auditDBBlankPath = new FileUtil().GetAuditDBBlankPath();
if (File.Exists(auditDBBlankPath))
{
//bool copied = new FileUtil().CopyAuditDBBlank();
bool copied = new FileUtil().ReplaceAuditDB();
if (!copied)
{
return(null);
}
if (File.Exists(auditDBPath))
{
if (auditDB != null)
{
auditDB.dbPath = auditDBPath;
return(auditDB);
}
//auditDB = new AuditDB(auditDBPath);
auditDB = new AuditDB();
auditDB.dbPath = auditDBPath;
return(auditDB);
}
}
}
return(null);
}
private void FillSourceComboBox()
{
objAuditdb = new AuditDB();
DataTable dt = new DataTable();
string[] strDestCode = ((NewCheckboxListItem)(clbDestination.SelectedItem)).Tag.Split('~');
clbSource.Items.Clear();
try
{
dt = objAuditdb.LevelGroupAuditEcodes_Get(Convert.ToInt32(strDestCode[0]), Convert.ToDateTime(dtpDocMonth.Value).ToString("MMMyyyy").ToUpper()).Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow dataRow in dt.Rows)
{
if (dataRow["Mapped"].ToString() == "99999")
{
NewCheckboxListItem oclBox = new NewCheckboxListItem();
oclBox.Tag = dataRow["ECODE"].ToString();
oclBox.Text = dataRow["ENAME"].ToString();
clbSource.Items.Add(oclBox);
oclBox = null;
}
else
{
NewCheckboxListItem oclBox = new NewCheckboxListItem();
oclBox.Tag = dataRow["ECODE"].ToString();
oclBox.Text = dataRow["ENAME"].ToString();
clbSource.Items.Add(oclBox, CheckState.Checked);
oclBox = null;
}
}
}
dt = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
objAuditdb = null;
}
}
private void FillBranchTypes()
{
objAuditdb = new AuditDB();
try
{
cbBranchType.DataSource = objAuditdb.dtBranchType();
cbBranchType.DisplayMember = "name";
cbBranchType.ValueMember = "type";
cbBranchType.SelectedValue = CommonData.BranchType;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SSERP", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
objAuditdb = null;
}
}
private void FillDestinationComboBox(int nLevelId)
{
objAuditdb = new AuditDB();
DataTable dt = new DataTable();
string strLoadedEcode = string.Empty;
clbDestination.Items.Clear();
clbSource.Items.Clear();
if (cbDesig.SelectedIndex > 0)
{
try
{
dt = objAuditdb.GetAuditEcodes(nLevelId).Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow dataRow in dt.Rows)
{
NewCheckboxListItem oclBox = new NewCheckboxListItem();
oclBox.Tag = dataRow["ECODE"].ToString();
oclBox.Text = dataRow["ENAME"].ToString();
clbDestination.Items.Add(oclBox);
oclBox = null;
}
}
dt = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
objSQLdb = null;
}
}
}
private void FillMappList()
{
objAuditdb = new AuditDB();
char cMapped = 'T';
DataTable dt = new DataTable();
lbMapp.DataSource = null;
try
{
if (chkMapp.CheckState == CheckState.Unchecked)
{
cMapped = 'F';
lblMap.Text = "Unmapped Source";
}
else
{
lblMap.Text = "Mapped Source";
}
dt = objAuditdb.GetAuditMaporUnmappedEcodes(Convert.ToDateTime(dtpDocMonth.Value).ToString("MMMyyyy").ToUpper(), cMapped).Tables[0];
if (dt.Rows.Count > 0)
{
lbMapp.DataSource = dt;
lbMapp.DisplayMember = "ENAME";
lbMapp.ValueMember = "ECODE";
}
dt = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
objAuditdb = null;
}
}
public void Add_act(string atype)
{
var db = new AuditDB(_storageConnectionString);
db.CreateActionData(atype, "Action");
}
public Task <List <Audit> > GetActiontype(string atype)
{
var db = new AuditDB(_storageConnectionString);
return(db.GetType(atype, "Action"));
}
public void Add_auth(string atype)
{
var db = new AuditDB(_storageConnectionString);
db.CreateActionData(atype, "Authenticate");
}
public static DynamicModels.Input.Custom_Reports LoadCustomReport(IConnectToDB _Connect, SessionObjects _SessionModel, DynamicModels.Input.Custom_Reports Report, AuditDB AuditCommand = null)
{
_DynamicInputProcedures CoreIH = new _DynamicInputProcedures();
DynamicModels.Input.Custom_Reports thisModel = Report;
if (thisModel != null)
{
thisModel.I_BASE_CUSTOM_REPORT_ID = thisModel.I_BASE_CUSTOM_REPORT_ID == null ? 0 : thisModel.I_BASE_CUSTOM_REPORT_ID;
thisModel.I_PREV_CUSTOM_REPORT_ID = thisModel.I_PREV_CUSTOM_REPORT_ID == null ? 0 : thisModel.I_PREV_CUSTOM_REPORT_ID;
thisModel.I_ENABLED = thisModel.I_ENABLED == null ? 'Y' : thisModel.I_ENABLED;
thisModel.I_IDENTITIES_ID = _SessionModel._IdentityModel.identities_id;
thisModel = CoreIH.InsertCustomReport(_Connect, thisModel, AuditCommand);
if (thisModel.Filters != null && thisModel.O_CUSTOM_REPORT_ID > 0)
{
for (int i = 0; i < thisModel.Filters.Count; i++)
{
thisModel.Filters[i].I_CUSTOM_REPORT_ID = thisModel.O_CUSTOM_REPORT_ID;
thisModel.Filters[i].I_BASE_FILTER_ID = 0;
thisModel.Filters[i].I_PREV_FILTER_ID = 0;
thisModel.Filters[i].I_ENABLED = 'Y';
thisModel.Filters[i].I_IDENTITIES_ID = _SessionModel._IdentityModel.identities_id;
thisModel.Filters[i].I_FILTER_ORDER = i;
thisModel.Filters[i].I_FILTER_ALIAS = String.IsNullOrWhiteSpace(thisModel.Filters[i].I_FILTER_ALIAS) ? thisModel.Filters[i].I_FILTER : thisModel.Filters[i].I_FILTER_ALIAS;
thisModel.Filters[i] = CoreIH.InsertCustomReportFilter(_Connect, thisModel.Filters[i]);
}
}
if (thisModel.Columns != null && thisModel.O_CUSTOM_REPORT_ID > 0)
{
for (int i = 0; i < thisModel.Columns.Count; i++)
{
thisModel.Columns[i].I_CUSTOM_REPORT_ID = thisModel.O_CUSTOM_REPORT_ID;
thisModel.Columns[i].I_BASE_CUSTOM_REPORT_COLUMN_ID = 0;
thisModel.Columns[i].I_PREV_CUSTOM_REPORT_COLUMN_ID = 0;
thisModel.Columns[i].I_ENABLED = 'Y';
thisModel.Columns[i].I_IDENTITIES_ID = _SessionModel._IdentityModel.identities_id;
thisModel.Columns[i] = CoreIH.InsertCustomReportColumns(_Connect, thisModel.Columns[i]);
}
}
else
{
StringBuilder _sqlin = new StringBuilder();
//TODO: Get Columns from DB
if (thisModel.O_CUSTOM_REPORT_ID > 0)
{
_sqlin.AppendLine("BEGIN");
//string _Schema = TemplateName == "SYSTEM" ? "CSA" : TemplateName;
_sqlin.AppendLine("DECLARE @P_ROOT_REPORT_ID bigint = '" + thisModel.I_ROOT_REPORT_ID + "'");
_sqlin.AppendLine("SELECT ORIGINAL_COLUMN, ROOT_REPORT_COLUMN_ID");
_sqlin.AppendLine("FROM [DYNAMIC].[ROOT_REPORTS] a");
_sqlin.AppendLine("INNER JOIN [DYNAMIC].ROOT_REPORT_COLUMNS b on a.ROOT_REPORT_ID = b.ROOT_REPORT_ID");
_sqlin.AppendLine("WHERE a.ROOT_REPORT_ID = @P_ROOT_REPORT_ID");
_sqlin.AppendLine("ORDER BY ROOT_REPORT_COLUMN_ID ASC");
_sqlin.AppendLine("END ");
ER_Query.Parameter_Run SQlin = new ER_Query.Parameter_Run
{
sqlIn = _sqlin.ToString(),
_dbParameters = new List <DBParameters>()
};
DataTable ReportColumnns = ER_Query._RUN_PARAMETER_QUERY(_Connect, SQlin);
DataColumnCollection DCC = ReportColumnns.Columns;
for (int i = 0; i < ReportColumnns.Rows.Count; i++)
{
DynamicModels.Input.Custom_Report_Column thisColumn = new DynamicModels.Input.Custom_Report_Column();
thisColumn.I_CUSTOM_REPORT_ID = thisModel.O_CUSTOM_REPORT_ID;
thisColumn.I_BASE_CUSTOM_REPORT_COLUMN_ID = 0;
thisColumn.I_PREV_CUSTOM_REPORT_COLUMN_ID = 0;
thisColumn.I_ENABLED = 'Y';
thisColumn.I_IDENTITIES_ID = _SessionModel._IdentityModel.identities_id;
thisColumn.I_ORIGINAL_COLUMN = ReportColumnns.Rows[i].Field <string>("ORIGINAL_COLUMN");
thisColumn.I_ALIAS_AREA = ReportColumnns.Rows[i].Field <string>("ORIGINAL_COLUMN");
thisColumn.I_COLUMN_AREA = ReportColumnns.Rows[i].Field <string>("ORIGINAL_COLUMN");
thisColumn = CoreIH.InsertCustomReportColumns(_Connect, thisColumn);
}
}
}
if (Report.V_ROOT_REPORT_NAME != null)
{
//Report.RootReport = CoreHelper.ReportMapper(_Connect, new DynamicModels.ReportDefinitions
//{
// GetStructure = false,
// SearchReport = Report.V_ROOT_REPORT_NAME
//}).theReport;
DynamicModels.Input.Custom_Report_Order_Sets thisOrderSet = new DynamicModels.Input.Custom_Report_Order_Sets();
if (Report.RootReport.ReportColumnOrderSet != null && Report.RootReport.ReportColumnOrderSet.Count > 0)
{
thisOrderSet = new DynamicModels.Input.Custom_Report_Order_Sets
{
I_BASE_CUSTOM_REPORTS_ORDER_SET_ID = 0,
I_PREV_CUSTOM_REPORTS_ORDER_SET_ID = 0,
I_CUSTOM_REPORT_ID = thisModel.O_CUSTOM_REPORT_ID,
I_ENABLED = 'Y',
I_IDENTITIES_ID = _SessionModel._IdentityModel.identities_id
};
thisOrderSet = _DynamicInputProcedures._InsertCustomReportOrderSet(_Connect, thisOrderSet);
if (Report.RootReport.ReportColumnOrderSet != null && thisOrderSet.O_CUSTOM_REPORTS_ORDER_SET_ID > 0)
{
for (int j = 0; j < Report.RootReport.ReportColumnOrderSet.Count; j++)
{
DynamicModels.ReportOrder thisOrder = Report.RootReport.ReportColumnOrderSet[j];
_DynamicInputProcedures._InsertCustomReportOrder(_Connect, new DynamicModels.Input.Custom_Report_Order
{
I_BASE_CUSTOM_REPORTS_ORDER_ID = 0,
I_PREV_CUSTOM_REPORTS_ORDER_ID = 0,
I_ENABLED = 'Y',
I_IDENTITIES_ID = _SessionModel._IdentityModel.identities_id,
I_CUSTOM_REPORTS_ORDER_SET_ID = thisOrderSet.O_CUSTOM_REPORTS_ORDER_SET_ID,
I_SORT_COLUMN = thisOrder.sort_column,
I_SORT_DIRECTION = thisOrder.sort_direction,
I_SORT_ORDER = thisOrder.sort_order
});
}
}
}
}
}
return(Report);
}
public static List <DynamicModels.Input.Custom_Reports> LoadCustomReports(IConnectToDB _Connect, SessionObjects _SessionModel, List <DynamicModels.Input.Custom_Reports> Reports, AuditDB AuditCommand = null)
{
for (int i = 0; i < Reports.Count; i++)
{
//try
//{
Reports[i] = LoadCustomReport(_Connect, _SessionModel, Reports[i], AuditCommand);
// }
//catch (Exception e)
//{
// Reports[i] = Reports[i];
//}
}
return(Reports);
}
private void FillBranches()
{
tvBranches.Nodes.Clear();
GetSelectedBranchTypes();
objAuditDB = new AuditDB();
DataSet ds = new DataSet();
chkComp.Visible = false;
ds = objAuditDB.GetAuditBranchRegions("", "", "", "", CommonData.LogUserId, "PARENT");
TreeNode tNode;
if (strBranTypes.Length >= 2)
{
try
{
if (ds.Tables[0].Rows.Count > 0)
{
chkComp.Visible = true;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
tvBranches.Nodes.Add(ds.Tables[0].Rows[i]["COMPANY_CODE"].ToString(), ds.Tables[0].Rows[i]["COMPANY_NAME"].ToString());
DataSet dschild = new DataSet();
dschild = objAuditDB.GetAuditBranchRegions(ds.Tables[0].Rows[i]["COMPANY_CODE"].ToString(), strBranTypes, "", "", "", "ZONES");
if (dschild.Tables[0].Rows.Count > 0)
{
for (int j = 0; j < dschild.Tables[0].Rows.Count; j++)
{
tvBranches.Nodes[i].Nodes.Add(dschild.Tables[0].Rows[j]["ABM_STATE"].ToString(), dschild.Tables[0].Rows[j]["ABM_STATE"].ToString());
DataSet dschild1 = new DataSet();
dschild1 = objAuditDB.GetAuditBranchRegions(ds.Tables[0].Rows[i]["COMPANY_CODE"].ToString(), strBranTypes, dschild.Tables[0].Rows[j]["ABM_STATE"].ToString(), "", "", "REGIONS");
if (dschild1.Tables[0].Rows.Count > 0)
{
for (int k = 0; k < dschild1.Tables[0].Rows.Count; k++)
{
tvBranches.Nodes[i].Nodes[j].Nodes.Add(dschild1.Tables[0].Rows[k]["ABM_REGION"].ToString(), dschild1.Tables[0].Rows[k]["ABM_REGION"].ToString());
DataSet dschild2 = new DataSet();
dschild2 = objAuditDB.GetAuditBranchRegions(ds.Tables[0].Rows[i]["COMPANY_CODE"].ToString(), strBranTypes, dschild.Tables[0].Rows[j]["ABM_STATE"].ToString(), dschild1.Tables[0].Rows[k]["ABM_REGION"].ToString(), CommonData.LogUserId, "BRANCHES");
if (dschild2.Tables[0].Rows.Count > 0)
{
for (int ivar = 0; ivar < dschild2.Tables[0].Rows.Count; ivar++)
{
tvBranches.Nodes[i].Nodes[j].Nodes[k].Nodes.Add(dschild2.Tables[0].Rows[ivar]["BranchCode"].ToString(), dschild2.Tables[0].Rows[ivar]["BranchName"].ToString());
}
}
}
}
}
}
}
}
for (int i = 0; i < tvBranches.Nodes.Count; i++)
{
for (int j = 0; j < tvBranches.Nodes[i].Nodes.Count; j++)
{
if (tvBranches.Nodes[i].Nodes[j].Nodes.Count > 0)
{
tvBranches.Nodes[i].FirstNode.Expand();
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
private void FillBranchandRegions()
{
tvBranches.Nodes.Clear();
objAuditDB = new AuditDB();
DataSet ds = new DataSet();
ds = objAuditDB.GetAuditBranchRegions("", "", "", "", CommonData.LogUserId, "PARENT");
TreeNode tNode;
try
{
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
tvBranches.Nodes.Add(ds.Tables[0].Rows[i]["COMPANY_CODE"].ToString(), ds.Tables[0].Rows[i]["COMPANY_NAME"].ToString());
DataSet dschild = new DataSet();
dschild = objAuditDB.GetAuditBranchRegions(ds.Tables[0].Rows[i]["COMPANY_CODE"].ToString(), "", "", "", CommonData.LogUserId, "ZONES");
if (dschild.Tables[0].Rows.Count > 0)
{
for (int j = 0; j < dschild.Tables[0].Rows.Count; j++)
{
tvBranches.Nodes[i].Nodes.Add(dschild.Tables[0].Rows[j]["ABM_STATE"].ToString(), dschild.Tables[0].Rows[j]["ABM_STATE"].ToString());
DataSet dschild1 = new DataSet();
dschild1 = objAuditDB.GetAuditBranchRegions(ds.Tables[0].Rows[i]["COMPANY_CODE"].ToString(), "", dschild.Tables[0].Rows[j]["ABM_STATE"].ToString(), "", CommonData.LogUserId, "REGIONS");
if (dschild1.Tables[0].Rows.Count > 0)
{
for (int k = 0; k < dschild1.Tables[0].Rows.Count; k++)
{
tvBranches.Nodes[i].Nodes[j].Nodes.Add(dschild1.Tables[0].Rows[k]["ABM_REGION"].ToString(), dschild1.Tables[0].Rows[k]["ABM_REGION"].ToString());
DataSet dschild2 = new DataSet();
dschild2 = objAuditDB.GetAuditBranchRegions(ds.Tables[0].Rows[i]["COMPANY_CODE"].ToString(), "HO", dschild.Tables[0].Rows[j]["ABM_STATE"].ToString(), dschild1.Tables[0].Rows[k]["ABM_REGION"].ToString(), CommonData.LogUserId, "BRANCHES");
tvBranches.Nodes[i].Nodes[j].Nodes[k].Nodes.Add("HO" + "(" + dschild2.Tables[0].Rows.Count + ")");
if (dschild2.Tables[0].Rows.Count > 0)
{
for (int ivar = 0; ivar < dschild2.Tables[0].Rows.Count; ivar++)
{
tvBranches.Nodes[i].Nodes[j].Nodes[k].Nodes[0].Nodes.Add(dschild2.Tables[0].Rows[ivar]["BranchCode"].ToString(), dschild2.Tables[0].Rows[ivar]["BranchName"].ToString());
}
}
dschild2 = objAuditDB.GetAuditBranchRegions(ds.Tables[0].Rows[i]["COMPANY_CODE"].ToString(), "BR", dschild.Tables[0].Rows[j]["ABM_STATE"].ToString(), dschild1.Tables[0].Rows[k]["ABM_REGION"].ToString(), CommonData.LogUserId, "BRANCHES");
tvBranches.Nodes[i].Nodes[j].Nodes[k].Nodes.Add("BRANCHES" + "(" + dschild2.Tables[0].Rows.Count + ")");
if (dschild2.Tables[0].Rows.Count > 0)
{
for (int ivar = 0; ivar < dschild2.Tables[0].Rows.Count; ivar++)
{
tvBranches.Nodes[i].Nodes[j].Nodes[k].Nodes[1].Nodes.Add(dschild2.Tables[0].Rows[ivar]["BranchCode"].ToString(), dschild2.Tables[0].Rows[ivar]["BranchName"].ToString());
}
}
dschild2 = objAuditDB.GetAuditBranchRegions(ds.Tables[0].Rows[i]["COMPANY_CODE"].ToString(), "SP", dschild.Tables[0].Rows[j]["ABM_STATE"].ToString(), dschild1.Tables[0].Rows[k]["ABM_REGION"].ToString(), CommonData.LogUserId, "BRANCHES");
tvBranches.Nodes[i].Nodes[j].Nodes[k].Nodes.Add("STOCK POINTS" + "(" + dschild2.Tables[0].Rows.Count + ")");
if (dschild2.Tables[0].Rows.Count > 0)
{
for (int ivar = 0; ivar < dschild2.Tables[0].Rows.Count; ivar++)
{
tvBranches.Nodes[i].Nodes[j].Nodes[k].Nodes[2].Nodes.Add(dschild2.Tables[0].Rows[ivar]["BranchCode"].ToString(), dschild2.Tables[0].Rows[ivar]["BranchName"].ToString());
}
}
dschild2 = objAuditDB.GetAuditBranchRegions(ds.Tables[0].Rows[i]["COMPANY_CODE"].ToString(), "PU", dschild.Tables[0].Rows[j]["ABM_STATE"].ToString(), dschild1.Tables[0].Rows[k]["ABM_REGION"].ToString(), CommonData.LogUserId, "BRANCHES");
tvBranches.Nodes[i].Nodes[j].Nodes[k].Nodes.Add("PRODUCTION UNITS" + "(" + dschild2.Tables[0].Rows.Count + ")");
if (dschild2.Tables[0].Rows.Count > 0)
{
for (int ivar = 0; ivar < dschild2.Tables[0].Rows.Count; ivar++)
{
tvBranches.Nodes[i].Nodes[j].Nodes[k].Nodes[3].Nodes.Add(dschild2.Tables[0].Rows[ivar]["BranchCode"].ToString(), dschild2.Tables[0].Rows[ivar]["BranchName"].ToString());
}
}
dschild2 = objAuditDB.GetAuditBranchRegions(ds.Tables[0].Rows[i]["COMPANY_CODE"].ToString(), "TU", dschild.Tables[0].Rows[j]["ABM_STATE"].ToString(), dschild1.Tables[0].Rows[k]["ABM_REGION"].ToString(), CommonData.LogUserId, "BRANCHES");
tvBranches.Nodes[i].Nodes[j].Nodes[k].Nodes.Add("TRANSPORT UNITS" + "(" + dschild2.Tables[0].Rows.Count + ")");
if (dschild2.Tables[0].Rows.Count > 0)
{
for (int ivar = 0; ivar < dschild2.Tables[0].Rows.Count; ivar++)
{
tvBranches.Nodes[i].Nodes[j].Nodes[k].Nodes[4].Nodes.Add(dschild2.Tables[0].Rows[ivar]["BranchCode"].ToString(), dschild2.Tables[0].Rows[ivar]["BranchName"].ToString());
}
}
dschild2 = objAuditDB.GetAuditBranchRegions(ds.Tables[0].Rows[i]["COMPANY_CODE"].ToString(), "OL", dschild.Tables[0].Rows[j]["ABM_STATE"].ToString(), dschild1.Tables[0].Rows[k]["ABM_REGION"].ToString(), CommonData.LogUserId, "BRANCHES");
tvBranches.Nodes[i].Nodes[j].Nodes[k].Nodes.Add("OUTLETS" + "(" + dschild2.Tables[0].Rows.Count + ")");
if (dschild2.Tables[0].Rows.Count > 0)
{
for (int ivar = 0; ivar < dschild2.Tables[0].Rows.Count; ivar++)
{
tvBranches.Nodes[i].Nodes[j].Nodes[k].Nodes[5].Nodes.Add(dschild2.Tables[0].Rows[ivar]["BranchCode"].ToString(), dschild2.Tables[0].Rows[ivar]["BranchName"].ToString());
}
}
}
}
}
}
}
}
for (int i = 0; i < tvBranches.Nodes.Count; i++)
{
for (int j = 0; j < tvBranches.Nodes[i].Nodes.Count; j++)
{
if (tvBranches.Nodes[i].Nodes[j].Nodes.Count > 0)
{
tvBranches.Nodes[i].FirstNode.Expand();
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
