// Global Settings
public string getParamValue(string comp_code, string id, string columnName)
{
try
{
sql = "select " + columnName + " from param where rec_company_code = '" + comp_code + "' and param_pkid = '" + id + "'";
Con_Oracle = new DBConnection();
DataTable Dt_test = new DataTable();
Dt_test = Con_Oracle.ExecuteQuery(sql);
Con_Oracle.CloseConnection();
if (Dt_test.Rows.Count > 0)
{
return(Dt_test.Rows[0][columnName].ToString());
}
else
{
return("");
}
}
catch (Exception Ex)
{
if (Con_Oracle != null)
{
Con_Oracle.CloseConnection();
}
throw Ex;
}
}
public Boolean SendEmail(Dictionary <string, object> SearchData, out string ErrorMessage)
{
Boolean bRet = false;
string Report_File_Name = "";
string Report_File_DispName = "";
//string Auto_Bcc_Email_ID_Branch = "";
string sql = "";
string ReadReceipt = "NO";
string DeliveryReceipt = "NO";
string TO_IDS = "";
string CC_IDS = "";
string BCC_IDS = "";
string Subject = "";
string Message = "";
string User_Pkid = "";
Boolean isCommonId = true;
bool canftp = false;
try
{
ErrorMessage = "";
// physicalPath = AppDomain.CurrentDomain.BaseDirectory;
// SPATH = Filter["PATH"].ToString();
// BRANCH_CODE = userInfo["REC_BRANCH_CODE"].ToString();
//Boolean Is_Auto_Bcc = false;
string[] IDS = null;
if (SearchData.ContainsKey("to_ids"))
{
TO_IDS = SearchData["to_ids"].ToString().Replace(";", ",").ToLower();
}
if (SearchData.ContainsKey("cc_ids"))
{
CC_IDS = SearchData["cc_ids"].ToString().Replace(";", ",").ToLower();
}
if (SearchData.ContainsKey("bcc_ids"))
{
BCC_IDS = SearchData["bcc_ids"].ToString().Replace(";", ",").ToLower();
}
if (SearchData.ContainsKey("subject"))
{
Subject = SearchData["subject"].ToString();
}
if (SearchData.ContainsKey("message"))
{
Message = SearchData["message"].ToString().Replace("\n", "<br>");
}
if (SearchData.ContainsKey("filedisplayname"))
{
Report_File_DispName = SearchData["filedisplayname"].ToString();
}
if (SearchData.ContainsKey("filename"))
{
Report_File_Name = SearchData["filename"].ToString();
}
if (SearchData.ContainsKey("user_pkid"))
{
User_Pkid = SearchData["user_pkid"].ToString();
}
if (SearchData.ContainsKey("canftp"))
{
canftp = SearchData["canftp"].ToString() == "Y" ? true : false;
}
if (SearchData.ContainsKey("iscommonid"))
{
isCommonId = (Boolean)SearchData["iscommonid"];
}
if (canftp)
{
Message += "<br>** EDI Files have been uploaded.";
Message += "<br>** This is a system generated email.";
}
if (SearchData.ContainsKey("read_receipt"))
{
ReadReceipt = SearchData["read_receipt"].ToString();
}
if (SearchData.ContainsKey("delivery-receipt"))
{
DeliveryReceipt = SearchData["delivery_receipt"].ToString();
}
string Email_Display_Name = "";
string SMTP_SERVER = "";
int SMTP_PORT = 587;
string EmailID = "";
string EmailPwd = "";
Boolean SMTP_SSL = true;
Boolean SMTP_Authentication = true;
//Boolean SMTP_SPA = false;
//DataTable Dt_Temp = new DataTable();
//sql = " select param_name3 from mast_param where param_type ='BRANCH SETTINGS' and param_name1 = 'AUTO-BCC-EMAIL-ID' and rec_branch_code = '" + BRANCH_CODE + "'";
//Dt_Temp = DB.ExecuteQuery(sql);
//if (Dt_Temp.Rows.Count > 0)
//{
// Auto_Bcc_Email_ID_Branch = Dt_Temp.Rows[0]["param_name3"].ToString();
//}
DataTable Dt_User = new DataTable();
if (isCommonId)
{
sql = "";
sql += " select mail_pkid, mail_name, mail_smtp_name, mail_smtp_port, mail_is_ssl_required, mail_is_auth_required, mail_is_spa_required, mail_id, mail_pwd from mailserver ";
DBConnection Con_Oracle = new DBConnection();
Dt_User = Con_Oracle.ExecuteQuery(sql);
Con_Oracle.CloseConnection();
foreach (DataRow DrUser in Dt_User.Rows)
{
EmailID = DrUser["mail_id"].ToString().ToLower();
EmailPwd = DrUser["mail_pwd"].ToString();
SMTP_SERVER = DrUser["mail_smtp_name"].ToString();
SMTP_PORT = Lib.Conv2Integer(DrUser["mail_smtp_port"].ToString());
SMTP_SSL = false;
if (DrUser["mail_is_ssl_required"].ToString() == "Y")
{
SMTP_SSL = true;
}
SMTP_Authentication = false;
if (DrUser["mail_is_auth_required"].ToString() == "Y")
{
SMTP_Authentication = true;
}
}
}
else
{
sql = "";
sql += " select user_email,user_email_pwd ";
sql += " from userm a ";
sql += " where user_pkid = '" + User_Pkid + "'";
DBConnection Con_Oracle = new DBConnection();
Dt_User = Con_Oracle.ExecuteQuery(sql);
Con_Oracle.CloseConnection();
foreach (DataRow DrUser in Dt_User.Rows)
{
EmailID = DrUser["USER_EMAIL"].ToString().ToLower();
EmailPwd = DrUser["USER_EMAIL_PWD"].ToString();
//Email_Display_Name = DrUser["USER_EMAIL_DISPLAY_NAME"].ToString();
//if (DrUser["USR_EMAIL_AUTO_BCC"].ToString() == "Y")
// Is_Auto_Bcc = true;
//else
// Is_Auto_Bcc = false;
//SMTP_SERVER = DrUser["mail_smtp_name"].ToString();
//SMTP_PORT = Lib.Conv2Integer(DrUser["mail_smtp_port"].ToString());
//if (DrUser["MAIL_IS_SSL_REQUIRED"].ToString() == "Y")
// SMTP_SSL = true;
//else
// SMTP_SSL = false;
//if (DrUser["MAIL_IS_AUTH_REQUIRED"].ToString() == "Y")
// SMTP_Authentication = true;
//else
// SMTP_Authentication = false;
//if (DrUser["MAIL_IS_SPA_REQUIRED"].ToString() == "Y")
// SMTP_SPA = true;
//else
// SMTP_SPA = false;
break;
}
}
Dt_User.Rows.Clear();
if (EmailID.Trim() == "" || EmailPwd.Trim() == "")
{
bRet = false;
ErrorMessage = "From ID or Password cannot be blank.";
return(bRet);
}
SmtpClient smtpClient = new SmtpClient(SMTP_SERVER, SMTP_PORT);
smtpClient.EnableSsl = SMTP_SSL;
smtpClient.DeliveryMethod = SmtpDeliveryMethod.Network;
smtpClient.UseDefaultCredentials = SMTP_Authentication;
smtpClient.Credentials = new NetworkCredential(EmailID, EmailPwd);
using (MailMessage message = new MailMessage())
{
if (Email_Display_Name == "")
{
message.From = new MailAddress(EmailID);
}
else
{
message.From = new MailAddress(EmailID, Email_Display_Name);
}
message.Subject = Subject;
message.Body = Message;
message.IsBodyHtml = true;
/*
* if (DeliveryReceipt == "YES")
* {
* message.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure |
* DeliveryNotificationOptions.OnSuccess |
* DeliveryNotificationOptions.Delay;
* }
*
* if (ReadReceipt == "YES")
* message.Headers.Add("Disposition-Notification-To", EmailID);
*/
if (TO_IDS.Trim().Length > 0)
{
IDS = TO_IDS.Split(',');
foreach (string str in IDS)
{
if (str != "")
{
message.To.Add(str);
}
}
}
if (CC_IDS.Trim().Length > 0)
{
IDS = CC_IDS.Split(',');
foreach (string str in IDS)
{
if (str != "")
{
message.CC.Add(str);
}
}
}
if (BCC_IDS.Trim().Length > 0)
{
IDS = BCC_IDS.Split(',');
foreach (string str in IDS)
{
if (str != "")
{
message.Bcc.Add(str);
}
}
}
//if (Is_Auto_Bcc)
// message.Bcc.Add(EmailID);
//if (Auto_Bcc_Email_ID_Branch != "")
// message.Bcc.Add(Auto_Bcc_Email_ID_Branch);
if (Report_File_Name != "")
{
if (Report_File_Name.Contains(","))
{
string[] Fname = Report_File_Name.Split(',');
string[] Fname2 = null;
for (int i = 0; i < Fname.Length; i++)
{
Fname2 = Fname[i].Split('~');
Report_File_Name = Fname2[0].Trim();
Report_File_DispName = Fname2[1].Trim();
if (Report_File_Name != "")
{
if (Report_File_DispName == "")
{
Report_File_DispName = Report_File_Name;
}
Attachment attachment = new Attachment(Report_File_Name, MediaTypeNames.Application.Octet);
ContentDisposition disposition = attachment.ContentDisposition;
disposition.FileName = Report_File_DispName;
disposition.DispositionType = DispositionTypeNames.Attachment;
message.Attachments.Add(attachment);
}
}
}
else
{
if (Report_File_DispName == "")
{
Report_File_DispName = Report_File_Name;
}
Attachment attachment = new Attachment(Report_File_Name, MediaTypeNames.Application.Octet);
ContentDisposition disposition = attachment.ContentDisposition;
disposition.FileName = Report_File_DispName;
disposition.DispositionType = DispositionTypeNames.Attachment;
message.Attachments.Add(attachment);
}
}
smtpClient.Send(message);
bRet = true;
}
}
catch (Exception Ex)
{
bRet = false;
ErrorMessage = Ex.Message.ToString();
}
/*
* DeleteFile(physicalPath + SPATH + "MAIL.XML");
* DeleteFile(physicalPath + SPATH + Report_File_Name);
* DeleteFolder(physicalPath + SPATH);
*/
return(bRet);
}
// This is for searching records
public IDictionary <string, object> SearchRecord(Dictionary <string, object> SearchData)
{
Dictionary <string, object> RetData = new Dictionary <string, object>();
string Table = "";
string [] _str;
string _code = "";
int _year = 0;
Boolean isOldYear = false;
if (SearchData.ContainsKey("table"))
{
Table = SearchData["table"].ToString().ToUpper();
}
DataTable Dt_List = new DataTable();
Con_Oracle = new DBConnection();
try
{
if (Table == "PARAM")
{
sql = "select param_pkid, param_type, param_code, param_name,param_id1 from param a ";
sql += " where 1=1 ";
if (SearchData.ContainsKey("comp_code"))
{
sql += " and a.rec_company_code = '" + SearchData["comp_code"].ToString() + "'";
}
if (SearchData.ContainsKey("param_pkid"))
{
sql += " and a.param_pkid = '" + SearchData["param_pkid"].ToString() + "'";
}
if (SearchData.ContainsKey("param_type"))
{
sql += " and a.param_type = '" + SearchData["param_type"].ToString() + "'";
}
if (SearchData.ContainsKey("param_code"))
{
sql += " and a.param_code = '" + SearchData["param_code"].ToString() + "'";
}
if (SearchData.ContainsKey("param_name"))
{
sql += " and a.param_name = '" + SearchData["param_name"].ToString() + "'";
}
sql += " order by a.param_name ";
List <Param> ParamList = new List <Param>();
Param paramRow;
Dt_List = Con_Oracle.ExecuteQuery(sql);
foreach (DataRow Dr in Dt_List.Rows)
{
paramRow = new Param();
paramRow.param_pkid = Dr["param_pkid"].ToString();
paramRow.param_type = Dr["param_type"].ToString();
paramRow.param_code = Dr["param_code"].ToString();
paramRow.param_name = Dr["param_name"].ToString();
paramRow.param_id1 = Dr["param_id1"].ToString();
ParamList.Add(paramRow);
}
RetData.Add(Table.ToLower(), ParamList);
}
if (Table == "HISTORY")
{
List <Auditlog> aList = new List <Auditlog>();
string pkid = SearchData["pkid"].ToString().Trim();
if (pkid != "")
{
sql = " select to_char(audit_date,'DD/MM/YYYY HH24:MI:SS') as auditdate,audit_action,audit_user_code";
sql += " ,audit_type,audit_refno,audit_remarks ";
sql += " from auditlog ";
sql += " where audit_pkey = '" + pkid + "'";
sql += " order by audit_date ";
DataTable dt_audit = new DataTable();
dt_audit = Con_Oracle.ExecuteQuery(sql);
Auditlog _aRec;
foreach (DataRow Dr in dt_audit.Rows)
{
_aRec = new Auditlog();
_aRec.audit_date = Dr["auditdate"].ToString();
_aRec.audit_action = Dr["audit_action"].ToString();
_aRec.audit_user_code = Dr["audit_user_code"].ToString();
_aRec.audit_type = Dr["audit_type"].ToString();
_aRec.audit_refno = Dr["audit_refno"].ToString();
_aRec.audit_remarks = Dr["audit_remarks"].ToString();
aList.Add(_aRec);
}
}
RetData.Add("list", aList);
}
if (Table == "PASTEDATA")
{
char schar = '\t';
decimal nAmt = 0;
string[] srow;
string[] sdata;
string stype = "";
string snos = "";
string comp_code = "";
string branch_code = "";
string year_code = "";
string isoldyear = "N";
decimal nTotal = 0;
List <LovTable> mList = new List <LovTable>();
LovTable _Rec = null;
DataTable dt_job = new DataTable();
stype = SearchData["type"].ToString().ToUpper();
comp_code = SearchData["comp_code"].ToString().ToUpper();
branch_code = SearchData["branch_code"].ToString().ToUpper();
year_code = SearchData["year_code"].ToString();
if (SearchData.ContainsKey("isoldyear"))
{
isoldyear = SearchData["isoldyear"].ToString();
}
if (SearchData["cbdata"].ToString().Contains(","))
{
schar = ',';
}
srow = SearchData["cbdata"].ToString().Split('\n');
foreach (string str in srow)
{
if (str.Length > 0)
{
sdata = str.Split(schar);
if (sdata.Length > 0)
{
if (snos.Length > 0)
{
snos += ",";
}
if (stype == "JOB SEA EXPORT" || stype == "JOB AIR EXPORT" || stype == "SI SEA IMPORT" || stype == "SI AIR IMPORT" || stype == "SI SEA EXPORT" || stype == "SI AIR EXPORT" || stype == "GENERAL JOB")
{
snos += sdata[0].ToString();
}
else
{
snos += "'" + sdata[0].ToString() + "'";
}
}
}
}
if (stype == "JOB SEA EXPORT" || stype == "JOB AIR EXPORT")
{
sql = " select job_pkid as id, job_docno as code, job_docno as name, job_year as year, 0 as amt from jobm where ";
sql += " rec_branch_code = '" + branch_code + "'";
sql += " and job_year = " + year_code;
if (stype == "JOB SEA EXPORT")
{
sql += " and rec_category ='SEA EXPORT' ";
}
if (stype == "JOB AIR EXPORT")
{
sql += " and rec_category ='AIR EXPORT' ";
}
sql += " and job_docno in (" + snos + ")";
sql += " order by job_docno ";
}
if (stype == "SI SEA IMPORT" || stype == "SI AIR IMPORT" || stype == "SI SEA EXPORT" || stype == "SI AIR EXPORT")
{
sql = " select hbl_pkid as id, hbl_no as code, hbl_no as name, hbl_year as year, 0 as amt from hblm where ";
sql += " rec_branch_code = '" + branch_code + "'";
sql += " and hbl_year = " + year_code;
if (stype == "SI SEA EXPORT")
{
sql += " and hbl_type ='HBL-SE' ";
}
if (stype == "SI AIR EXPORT")
{
sql += " and hbl_type ='HBL-AE' ";
}
if (stype == "SI SEA IMPORT")
{
sql += " and hbl_type ='HBL-SI' ";
}
if (stype == "SI AIR IMPORT")
{
sql += " and hbl_type ='HBL-AI' ";
}
sql += " and hbl_no in (" + snos + ")";
sql += " order by hbl_no ";
}
if (stype == "GENERAL JOB")
{
sql = " select hbl_pkid as id, hbl_no as code, hbl_prefix as name, hbl_year as year, 0 as amt from hblm where ";
sql += " rec_branch_code = '" + branch_code + "'";
sql += " and hbl_year = " + year_code;
sql += " and hbl_type ='JOB-GN' ";
sql += " and hbl_no in (" + snos + ")";
sql += " order by hbl_no ";
}
if (stype == "CNTR SEA EXPORT")
{
sql = " select cntr_pkid as id,cntr_no as code, cntr_no as name ,cntr_year as year, 0 as amt from containerm ";
sql += " where rec_branch_code = '" + branch_code + "'";
sql += " and cntr_year = " + year_code;
sql += " and cntr_no in (" + snos + ")";
sql += " order by cntr_no ";
}
if (stype == "EMPLOYEE")
{
sql = " select cc_pkid as id , cc_code as code , cc_name as name, 0 as year, 0 as amt ";
sql += " from costcenterm where rec_company_code = '" + comp_code + "' and cc_type = 'EMPLOYEE' ";
sql += " and cc_code in (" + snos + ")";
sql += " order by cc_code ";
}
dt_job = new DataTable();
dt_job = Con_Oracle.ExecuteQuery(sql);
foreach (string str in srow)
{
if (str.Length > 0)
{
sdata = str.Split(schar);
if (sdata.Length > 0)
{
if (snos.Length > 0)
{
foreach (DataRow Dr in dt_job.Rows)
{
if (stype == "JOB SEA EXPORT" || stype == "JOB AIR EXPORT" || stype == "SI SEA EXPORT" || stype == "SI AIR EXPORT" || stype == "SI SEA IMPORT" || stype == "SI AIR IMPORT" || stype == "GENERAL JOB")
{
if (Lib.Conv2Integer(sdata[0].ToString()) == Lib.Conv2Integer(Dr["code"].ToString()))
{
nAmt = Lib.Conv2Decimal(Dr["amt"].ToString());
Dr["amt"] = nAmt + Lib.Conv2Decimal(sdata[1].ToString());
nTotal += Lib.Conv2Decimal(sdata[1].ToString());
break;
}
}
if (stype == "CNTR SEA EXPORT" || stype == "EMPLOYEE")
{
if (sdata[0].ToString().Trim() == Dr["code"].ToString())
{
nAmt = Lib.Conv2Decimal(Dr["amt"].ToString());
Dr["amt"] = nAmt + Lib.Conv2Decimal(sdata[1].ToString());
nTotal += Lib.Conv2Decimal(sdata[1].ToString());
break;
}
}
}
}
}
}
}
foreach (DataRow Dr in dt_job.Rows)
{
_Rec = new LovTable();
_Rec.id = Dr["id"].ToString();
_Rec.type = stype;
_Rec.code = Dr["code"].ToString();
if (isoldyear == "Y")
{
_Rec.code = Dr["code"].ToString() + "/" + year_code;
}
_Rec.name = Dr["name"].ToString();
_Rec.rate = Lib.Convert2Decimal(Dr["amt"].ToString());
mList.Add(_Rec);
}
dt_job.Rows.Clear();
RetData.Add("total", nTotal.ToString());
RetData.Add(Table.ToLower(), mList);
}
Con_Oracle.CloseConnection();
}
catch (Exception Ex)
{
if (Con_Oracle != null)
{
Con_Oracle.CloseConnection();
}
throw Ex;
}
return(RetData);
}
// This is used by AutoComplete Search Box
public IDictionary <string, object> List(Dictionary <string, object> SearchData)
{
Dictionary <string, object> RetData = new Dictionary <string, object>();
int rows_starting_number = 1;
int rows_ending_number = 10;
int rows_total = 0;
if (SearchData.ContainsKey("rows_starting_number"))
{
rows_starting_number = Lib.Conv2Integer(SearchData["rows_starting_number"].ToString());
}
if (SearchData.ContainsKey("rows_ending_number"))
{
rows_ending_number = Lib.Conv2Integer(SearchData["rows_ending_number"].ToString());
}
string Type = "";
if (SearchData.ContainsKey("type"))
{
Type = SearchData["type"].ToString();
}
string SubType = "";
if (SearchData.ContainsKey("subtype"))
{
SubType = SearchData["subtype"].ToString();
}
string SearchString = "";
if (SearchData.ContainsKey("searchstring"))
{
SearchString = SearchData["searchstring"].ToString();
}
string ParentId = "";
if (SearchData.ContainsKey("parentid"))
{
ParentId = SearchData["parentid"].ToString();
}
string comp_code = "";
if (SearchData.ContainsKey("comp_code"))
{
comp_code = SearchData["comp_code"].ToString();
}
string branch_code = "";
if (SearchData.ContainsKey("branch_code"))
{
branch_code = SearchData["branch_code"].ToString();
}
string where = "";
if (SearchData.ContainsKey("where"))
{
where = SearchData["where"].ToString();
}
Boolean isAdmin = false;
if (SearchData.ContainsKey("user_admin"))
{
isAdmin = (Boolean)SearchData["user_admin"];
}
Boolean parentmandatory = false;
if (SearchData.ContainsKey("parentmandatory"))
{
parentmandatory = (Boolean)SearchData["parentmandatory"];
}
if (SearchString == null)
{
SearchString = "";
}
if (ParentId == null)
{
ParentId = "";
}
List <LovTable> mList = new List <LovTable>();
LovTable mRow;
DataTable Dt_List = new DataTable();
Con_Oracle = new DBConnection();
try
{
if (Type == "COMPANY")
{
sql = " select * from ( ";
sql += " select comp_id as id, comp_code as code, comp_name as name ";
sql += " ,count(*) over() rowscount,row_number() over(order by comp_name) rn ";
sql += " from companym a";
sql += " where comp_type = 'C'";
sql += " and (comp_name like '%" + SearchString.ToUpper() + "%')";
sql += " ) a where rn between {START} and {END} order by name ";
sql = sql.Replace("{START}", rows_starting_number.ToString());
sql = sql.Replace("{END}", rows_ending_number.ToString());
}
else if (Type == "BRANCH")
{
sql = " select * from ( ";
sql += " select comp_pkid as id , comp_code as code, comp_name as name ";
sql += " ,count(*) over() rowscount,row_number() over(order by comp_name) rn ";
sql += " from companym a";
sql += " where comp_type = 'B' and rec_company_code ='" + comp_code + "' ";
sql += " and ( ";
sql += " comp_code like '%" + SearchString.ToUpper() + "%'";
sql += " or comp_name like '" + SearchString.ToUpper() + "%'";
sql += ")";
//sql += " order by a.comp_name ";
sql += " ) a where rn between {START} and {END} order by name ";
sql = sql.Replace("{START}", rows_starting_number.ToString());
sql = sql.Replace("{END}", rows_ending_number.ToString());
}
else if (Type == "STORE")
{
sql = " select * from ( ";
sql += " select comp_pkid as id , comp_code as code, comp_name as name, comp_tel, comp_ptc, ";
sql += " count(*) over() rowscount,row_number() over(order by comp_name) rn ";
sql += " from companym a";
if (!isAdmin && parentmandatory)
{
sql += " inner join userd b on a.comp_pkid = b.user_branch_id and b.user_id = '" + ParentId + "'";
}
sql += " where a.rec_company_code ='" + comp_code + "' and comp_type = 'S' ";
if (where != "")
{
sql += " and " + where;
}
sql += " and ( ";
sql += " comp_code like '%" + SearchString.ToUpper() + "%'";
sql += " or comp_name like '" + SearchString.ToUpper() + "%'";
sql += ")";
sql += " ) a where rn between {START} and {END} order by name ";
sql = sql.Replace("{START}", rows_starting_number.ToString());
sql = sql.Replace("{END}", rows_ending_number.ToString());
}
else if (Type == "VENDOR")
{
sql = " select * from ( ";
sql += " select comp_pkid as id , comp_code as code, comp_name as name ";
sql += " ,count(*) over() rowscount,row_number() over(order by comp_name) rn ";
sql += " from companym a";
sql += " where a.rec_company_code ='" + comp_code + "' and comp_type = 'V' ";
if (where != "")
{
sql += " and " + where;
}
sql += " and ( ";
sql += " comp_code like '%" + SearchString.ToUpper() + "%'";
sql += " or comp_name like '" + SearchString.ToUpper() + "%'";
sql += ")";
sql += " ) a where rn between {START} and {END} order by name ";
sql = sql.Replace("{START}", rows_starting_number.ToString());
sql = sql.Replace("{END}", rows_ending_number.ToString());
}
else if (Type == "USER")
{
sql = " select * from ( ";
sql += " select user_pkid as id ,user_code as code, user_name as name ";
sql += " ,count(*) over() rowscount,row_number() over(order by user_name) rn ";
sql += " from userm a where a.user_code <> 'ADMIN' and a.user_islocked= 'N' ";
sql += " and a.rec_company_code = '" + comp_code + "'";
sql += " and user_name like '%" + SearchString.ToUpper() + "%'";
//sql += " order by a.user_name ";
sql += " ) a where rn between {START} and {END} order by name ";
sql = sql.Replace("{START}", rows_starting_number.ToString());
sql = sql.Replace("{END}", rows_ending_number.ToString());
}
else if (Type == "RECCE USER")
{
sql = " select * from ( ";
sql += " select user_pkid as id ,user_code as code, user_name as name ";
sql += " ,count(*) over() rowscount,row_number() over(order by user_name) rn ";
sql += " from userm a ";
sql += " inner join param role on a.user_role_id = role.param_pkid and role.param_type ='ROLES' and role.param_name='RECCE USER' ";
sql += " where a.user_code <> 'ADMIN' and a.user_islocked= 'N' ";
if (where != "")
{
sql += " and " + where;
}
sql += " and a.rec_company_code = '" + comp_code + "'";
sql += " and user_name like '%" + SearchString.ToUpper() + "%'";
//sql += " order by a.user_name ";
sql += " ) a where rn between {START} and {END} order by name ";
sql = sql.Replace("{START}", rows_starting_number.ToString());
sql = sql.Replace("{END}", rows_ending_number.ToString());
}
else if (Type == "PIM-GROUP")
{
sql = " select * from ( ";
sql += " select grp_pkid as id ,grp_level_name as code, grp_level_name as name ";
sql += " ,count(*) over() rowscount,row_number() over(order by grp_level_name) rn ";
sql += " from pim_groupm a ";
sql += " where a.rec_company_code = '" + comp_code + "'";
sql += " and a.grp_table_name = '" + SubType + "'";
sql += " and grp_level_name like '%" + SearchString.ToUpper() + "%'";
sql += " ) a where rn between {START} and {END} order by name ";
sql = sql.Replace("{START}", rows_starting_number.ToString());
sql = sql.Replace("{END}", rows_ending_number.ToString());
}
else if (Type == "TABLECOLUMNS")
{
sql += " select * from ( ";
sql += " select a.*, count(*) over() rowscount,row_number() over(order by code) rn from ( ";
sql += " select 'STORE_NAME' as id, 'STORE_NAME' as code, 'STORE_NAME' as name ";
sql += " union all ";
sql += " select 'STORE_APPROVER' as id, 'STORE_APPROVER' as code, 'STORE_APPROVER' as name ";
sql += " union all ";
sql += " select 'STORE_RECEIVER' as id, 'STORE_RECEIVER' as code, 'STORE_RECEIVER' as name ";
sql += " union all ";
sql += " select 'PRODUCT' as id, 'PRODUCT' as code, 'PRODUCT' as name ";
sql += " union all ";
sql += " select 'LOGO_DEFAULT' as id, 'LOGO_DEFAULT' as code, 'LOGO_DEFAULT' as name ";
sql += " union all ";
sql += " select tabd_col_name as id, tabd_col_name as code, tabd_col_name as name ";
sql += " from tablesm a inner ";
sql += " join tablesd b on a.tab_pkid = b.tabd_parent_id ";
sql += " where a.rec_company_code = '" + comp_code + "' and a.tab_pkid = '" + SubType + "'";
sql += " ) a where code like '%" + SearchString.ToUpper() + "%'";
sql += " ) a where rn between {START} and {END} order by name ";
sql = sql.Replace("{START}", rows_starting_number.ToString());
sql = sql.Replace("{END}", rows_ending_number.ToString());
}
else if (Type == "TABLESM")
{
sql = " select * from ( ";
sql += " select tab_pkid as id ,tab_name as code, tab_name as name ";
sql += " ,count(*) over() rowscount,row_number() over(order by tab_name) rn ";
sql += " from tablesm a ";
sql += " where a.rec_company_code = '" + comp_code + "'";
sql += " and tab_name like '%" + SearchString.ToUpper() + "%'";
sql += " ) a where rn between {START} and {END} order by code ";
sql = sql.Replace("{START}", rows_starting_number.ToString());
sql = sql.Replace("{END}", rows_ending_number.ToString());
}
else if (Type == "COUNTRY" || Type == "STATE" || Type == "GENERAL JOB TYPES" || Type == "UNIT" || Type == "SHIPMENT TYPE" || Type == "INCOTERM" || Type == "CONTAINER TYPE" || Type == "SEA PORT" || Type == "AIR PORT" || Type == "SEA CARRIER" || Type == "AIR CARRIER" || Type == "VESSEL" || Type == "CONTACT TYPE" || Type == "SALESMAN" || Type == "LOCATION" || Type == "PRE CARRIAGE" || Type == "COMMODITY" || Type == "CITY" || Type == "SCHEME CODE" || Type == "END USE" || Type == "SAC" || Type == "CHALIC" || Type == "PAN" || Type == "TAN" || Type == "PORT" || Type == "COURIER COMPANY" || Type == "ESANCHITDOC" || Type == "ACCOUNTS MAIN CODE" || Type == "SERVICE CONTRACT" || Type == "TABLES")
{
sql = " select * from (";
if (Type == "SCHEME CODE" || Type == "END USE" || Type == "SAC" || Type == "PAN" || Type == "TAN")
{
sql += "select param_pkid as id , param_code as code, param_code ||'-' || param_name as name ";
}
else
{
sql += "select param_pkid as id , param_code as code, param_name as name ";
}
sql += " ,count(*) over() rowscount,row_number() over(order by param_name) rn ";
sql += " from param a ";
sql += " where a.rec_company_code = '" + comp_code + "'";
if (Type == "PORT")
{
sql += " and param_type in ('SEA PORT','AIR PORT')";
}
else
{
sql += " and param_type ='" + Type + "'";
}
sql += " and (";
sql += " param_code like '%" + SearchString.ToUpper() + "%'";
sql += " or param_name like '%" + SearchString.ToUpper() + "%'";
sql += " )";
if (where != "")
{
sql += " and (" + where + ")";
}
//sql += " order by a.param_name ";
sql += " ) a where rn between {START} and {END} order by name ";
sql = sql.Replace("{START}", rows_starting_number.ToString());
sql = sql.Replace("{END}", rows_ending_number.ToString());
}
else if (Type == "CURRENCY")
{
sql = " select * from ( ";
sql += "select param_pkid as id , param_code as code, param_name as name, param_rate as rate ,param_id1 as col1 ";
sql += " ,count(*) over() rowscount,row_number() over(order by param_name) rn ";
sql += " from param a ";
sql += " where a.rec_company_code = '" + comp_code + "'";
if (Type == "CURRENCY")
{
sql += " and param_type ='CURRENCY'";
}
sql += " and ( ";
sql += " param_code like '%" + SearchString.ToUpper() + "%'";
sql += " or param_name like '%" + SearchString.ToUpper() + "%'";
sql += " )";
//sql += " order by a.param_name ";
sql += " ) a where rn between {START} and {END} order by name ";
sql = sql.Replace("{START}", rows_starting_number.ToString());
sql = sql.Replace("{END}", rows_ending_number.ToString());
}
else if (Type == "PARAM")
{
sql = " select * from ( ";
sql += "select param_pkid as id , param_code as code, param_name as name, param_rate as rate ,param_id1 as col1 ";
sql += " ,count(*) over() rowscount,row_number() over(order by param_name) rn ";
sql += " from param a ";
sql += " where a.rec_company_code = '" + comp_code + "'";
sql += " and param_type ='" + SubType + "'";
if (where != "")
{
sql += " and " + where;
}
sql += " and ( ";
sql += " param_code like '%" + SearchString.ToUpper() + "%'";
sql += " or param_name like '%" + SearchString.ToUpper() + "%'";
sql += " )";
//sql += " order by a.param_name ";
sql += " ) a where rn between {START} and {END} order by name ";
sql = sql.Replace("{START}", rows_starting_number.ToString());
sql = sql.Replace("{END}", rows_ending_number.ToString());
}
Dt_List = Con_Oracle.ExecuteQuery(sql);
Con_Oracle.CloseConnection();
if (Dt_List.Rows.Count > 0)
{
rows_total = Lib.Conv2Integer(Dt_List.Rows[0]["rowscount"].ToString());
}
foreach (DataRow Dr in Dt_List.Rows)
{
mRow = new LovTable();
mRow.id = Dr["id"].ToString();
mRow.code = Dr["code"].ToString();
mRow.name = Dr["name"].ToString();
mRow.rate = 0;
mRow.col1 = "";
mRow.col2 = "";
mRow.col3 = "";
mRow.col4 = "";
mRow.col5 = "";
mRow.col6 = "";
mRow.col7 = "";
if (Type == "CURRENCY")
{
mRow.rate = Lib.Conv2Decimal(Dr["rate"].ToString()); //Fwd Rate
mRow.col1 = Dr["col1"].ToString(); //Clr Rate
}
if (Type == "STRREFUNDM")
{
mRow.rate = Lib.Conv2Decimal(Dr["rate"].ToString());
}
if (Type == "ACCTM")
{
mRow.col1 = Dr["col1"].ToString();
mRow.col2 = Dr["col2"].ToString();
mRow.col3 = Dr["col3"].ToString();
mRow.col4 = Dr["col4"].ToString();
mRow.col5 = Dr["col5"].ToString();
mRow.col6 = Dr["col6"].ToString();
}
if (Type == "CUSTOMERADDRESS")
{
mRow.col1 = Dr["add_gstin"].ToString();
mRow.col2 = Dr["add_state_id"].ToString();
mRow.col3 = Dr["state_code"].ToString();
mRow.col4 = Dr["state_code"].ToString() + "-" + Dr["state_name"].ToString();
mRow.col5 = "N";
if (Dr["add_sepz_unit"].ToString() == "Y")
{
mRow.col5 = "Y";
}
mRow.col6 = Dr["add_email"].ToString();
mRow.col7 = "N";
if (Dr["add_is_export"].ToString() == "Y")
{
mRow.col7 = "Y";
}
}
if (Type == "EMPLOYEE")
{
mRow.col1 = Dr["col1"].ToString();//emp status
}
if (Type == "STORE")
{
mRow.col1 = Dr["comp_tel"].ToString();
mRow.col2 = Dr["comp_ptc"].ToString();
}
mList.Add(mRow);
}
}
catch (Exception Ex)
{
if (Con_Oracle != null)
{
Con_Oracle.CloseConnection();
}
throw Ex;
}
RetData.Add("list", mList);
RetData.Add("rows_total", rows_total);
return(RetData);
}
// This is for Combo Loading
public IDictionary <string, object> Lov(Dictionary <string, object> SearchData)
{
Dictionary <string, object> RetData = new Dictionary <string, object>();
string Table = "";
string pkid = "";
string comp_code = "";
string branch_code = "";
string subtype = "";
if (SearchData.ContainsKey("table"))
{
Table = SearchData["table"].ToString().ToUpper();
}
if (SearchData.ContainsKey("pkid"))
{
pkid = SearchData["pkid"].ToString().ToUpper();
}
if (SearchData.ContainsKey("comp_code"))
{
comp_code = SearchData["comp_code"].ToString();
}
if (SearchData.ContainsKey("branch_code"))
{
branch_code = SearchData["branch_code"].ToString();
}
if (SearchData.ContainsKey("subtype"))
{
subtype = SearchData["subtype"].ToString();
}
DataTable Dt_List = new DataTable();
Con_Oracle = new DBConnection();
try
{
if (Table == "BRANCH")
{
sql = "select comp_pkid ,comp_code, comp_name from companym a";
sql += " where a.rec_company_code = '" + comp_code + "'";
sql += " and a.comp_type = 'B'";
sql += " order by a.comp_name ";
List <Companym> mList = new List <Companym>();
Companym Row;
Dt_List = Con_Oracle.ExecuteQuery(sql);
foreach (DataRow Dr in Dt_List.Rows)
{
Row = new Companym();
Row.comp_pkid = Dr["comp_pkid"].ToString();
Row.comp_code = Dr["comp_code"].ToString();
Row.comp_name = Dr["comp_name"].ToString();
mList.Add(Row);
}
RetData.Add(Table.ToLower(), mList);
}
if (Table == "PARAM")
{
sql = "select param_pkid, param_code, param_name from param a ";
sql += " where a.rec_company_code = '" + comp_code + "'";
if (SearchData.ContainsKey("param_pkid"))
{
sql += " and a.param_pkid = '" + SearchData["param_pkid"].ToString() + "'";
}
if (SearchData.ContainsKey("param_type"))
{
sql += " and a.param_type = '" + SearchData["param_type"].ToString() + "'";
}
if (SearchData.ContainsKey("param_code"))
{
sql += " and a.param_code = '" + SearchData["param_code"].ToString() + "'";
}
if (SearchData.ContainsKey("param_name"))
{
sql += " and a.param_name = '" + SearchData["param_name"].ToString() + "'";
}
if (SearchData["param_type"].ToString() == "ESANCHITDOC")
{
if (Con_Oracle.DB == "ORACLE")
{
sql += " and nvl(a.rec_locked,'N') = 'N' ";
}
else
{
sql += " and isnull(a.rec_locked,'N') = 'N' ";
}
}
sql += " order by a.param_name ";
List <Param> mList = new List <Param>();
Param Row;
Dt_List = Con_Oracle.ExecuteQuery(sql);
foreach (DataRow Dr in Dt_List.Rows)
{
Row = new Param();
Row.param_pkid = Dr["param_pkid"].ToString();
Row.param_code = Dr["param_code"].ToString();
Row.param_name = Dr["param_name"].ToString();
mList.Add(Row);
}
RetData.Add(Table.ToLower(), mList);
}
Con_Oracle.CloseConnection();
}
catch (Exception Ex)
{
if (Con_Oracle != null)
{
Con_Oracle.CloseConnection();
}
throw Ex;
}
return(RetData);
}