public JsonResult GetInvoices(string COMPANY_ID)
{
string query = "SELECT d.[INVOICE_ID],d.[INVOICE_STATUS],d.[INVOICE_TYPE],d.[COMPANY_NAME] ,d.[COMPANY_ID] ,d.[PROPERTY_NAME] " +
", d.[PROPERTY_CITY],d.[PROPERTY_STATE],d.[BILL_TO_ACCOUNT_MANAGER],d.[INVOICE_TOTAL],d.[INVOICE_TAX], d.[INVOICE_BILL_TO_ID] " +
",CASE WHEN d.[INVOICE_SENT_DATE] = '1900-01-01 00:00:00.000' OR d.[INVOICE_SENT_DATE] IS NULL THEN '' ELSE CONVERT(varchar(10),d.[INVOICE_SENT_DATE],101) END AS [INVOICE_SENT_DATE] " +
",CASE WHEN d.[SENT_TO_MAS_DATE] IS NULL THEN '' ELSE CONVERT(varchar(10),d.[SENT_TO_MAS_DATE],101) END AS [SENT_TO_MAS_DATE] " +
" FROM [TST_DATA].[dbo].[JOB_DETAILS] d " +
" WHERE [COMPANY_ID]='" + COMPANY_ID + "'";
DataAccess.TSTDataAccess da = new DataAccess.TSTDataAccess();
DataTable dt = da.ReadDataTable(query);
var tstData = (from row in dt.AsEnumerable()
select new
{
INVOICE_ID = row.Field <string>("INVOICE_ID").ToString(),
INVOICE_STATUS = row.Field <string>("INVOICE_STATUS").ToString(),
INVOICE_TYPE = row.Field <string>("INVOICE_TYPE").ToString(),
COMPANY_NAME = row.Field <string>("COMPANY_NAME").ToString(),
COMPANY_ID = row.Field <string>("COMPANY_ID").ToString(),
PROPERTY_NAME = row.Field <string>("PROPERTY_NAME").ToString(),
PROPERTY_CITY = row.Field <string>("PROPERTY_CITY").ToString(),
PROPERTY_STATE = row.Field <string>("PROPERTY_STATE").ToString(),
BILL_TO_ACCOUNT_MANAGER = row.Field <string>("BILL_TO_ACCOUNT_MANAGER").ToString(),
INVOICE_TOTAL = row.Field <string>("INVOICE_TOTAL").ToString(),
INVOICE_TAX = row.Field <string>("INVOICE_TAX").ToString(),
INVOICE_BILL_TO_ID = row.Field <string>("INVOICE_BILL_TO_ID").ToString(),
INVOICE_SENT_DATE = row.Field <string>("INVOICE_SENT_DATE").ToString(),
SENT_TO_MAS_DATE = row.Field <string>("SENT_TO_MAS_DATE").ToString()
});
return(Json(tstData, JsonRequestBehavior.AllowGet));
}
public JsonResult GetCompanies()
{
string query = "SELECT COMP_ID,COMPANY_NAME,ACCOUNT_TYPE,COMPANY_ID,ACCOUNT_REP,DATE_CREATED,DATE_MODIFIED " +
"FROM [TST_DATA].[dbo].[TST_Companies_With_Counts] " +
"WHERE (COMPANY_ID NOT IN ('UNKOWN','TRAINING')) " +
"ORDER BY COMPANY_NAME";
DataAccess.TSTDataAccess da = new DataAccess.TSTDataAccess();
DataTable dt = da.ReadDataTable(query);
var tstData = (from row in dt.AsEnumerable()
select new
{
COMP_ID = row.Field <int>("COMP_ID"),
COMPANY_NAME = row.Field <string>("COMPANY_NAME").ToString(),
ACCOUNT_TYPE = row.Field <string>("ACCOUNT_TYPE").ToString(),
COMPANY_ID = row.Field <string>("COMPANY_ID").ToString(),
ACCOUNT_REP = row.Field <string>("ACCOUNT_REP").ToString(),
DATE_CREATED = row.Field <DateTime?>("DATE_CREATED"),
DATE_MODIFIED = row.Field <DateTime?>("DATE_MODIFIED")
});
JsonResult json = Json(tstData, JsonRequestBehavior.AllowGet);
json.MaxJsonLength = int.MaxValue;
return(json);
}
private ReportParameter[] GetParametersServer()
{
string query = "SELECT CONVERT(VARCHAR(10),MAX(DATE_PAID),101) as 'LAST_COMM_PAID_DATE' FROM [dbo].[TST_COMMISSION_PAYMENTS]";
DataAccess.TSTDataAccess dataAccess = new DataAccess.TSTDataAccess();
DataSet ds = dataAccess.ReadDataSet(query);
string lastCommPaidDate = ds.Tables[0].Rows[0]["LAST_COMM_PAID_DATE"].ToString();
ReportParameter p1 = new ReportParameter("pCommissionPaymentDate", lastCommPaidDate);
return(new ReportParameter[] { p1 });
}
public JsonResult GetCompanyDetails(int COMP_ID)
{
string query = "SELECT [COMP_ID],[COMPANY_ID],[COMPANY_NAME],[ADDRESS],[ADDRESS_2],[CITY],[STATE],[ZIP]" +
",[BILL_ADDRESS],[BILL_ADDRESS_2],[BILL_CITY],[BILL_STATE],[BILL_ZIP],[ACCOUNT_TYPE] " +
", ISNULL([ACCOUNT_REP],'Unknown') AS ACCOUNT_REP ,ISNULL([ACCOUNT_REP_ID],280) AS ACCOUNT_REP_ID" +
",[LEAK_COUNT],[CONTRACT_COUNT],[BILLABLE_COUNT] ,[NONBILLABLE_COUNT],[CANCELLED_COUNT],[TOTAL_INVOICE_COUNT],[DATE_CREATED],[DATE_MODIFIED]" +
" FROM [TST_DATA].[dbo].[TST_Companies_With_Counts] " +
" WHERE [COMP_ID]=" + COMP_ID;
DataAccess.TSTDataAccess da = new DataAccess.TSTDataAccess();
DataTable dt = da.ReadDataTable(query);
var tstData = (from row in dt.AsEnumerable()
select new
{
COMP_ID = row.Field <int>("COMP_ID").ToString(),
COMPANY_ID = row.Field <string>("COMPANY_ID").ToString(),
COMPANY_NAME = row.Field <string>("COMPANY_NAME").ToString(),
ADDRESS = row.Field <string>("ADDRESS").ToString(),
ADDRESS_2 = row.Field <string>("ADDRESS_2").ToString(),
CITY = row.Field <string>("CITY").ToString(),
STATE = row.Field <string>("STATE").ToString(),
ZIP = row.Field <string>("ZIP").ToString(),
BILL_ADDRESS = row.Field <string>("BILL_ADDRESS").ToString(),
BILL_ADDRESS_2 = row.Field <string>("BILL_ADDRESS_2").ToString(),
BILL_CITY = row.Field <string>("BILL_CITY").ToString(),
BILL_STATE = row.Field <string>("BILL_STATE").ToString(),
BILL_ZIP = row.Field <string>("BILL_ZIP").ToString(),
ACCOUNT_TYPE = row.Field <string>("ACCOUNT_TYPE").ToString(),
ACCOUNT_REP = row.Field <string>("ACCOUNT_REP").ToString(),
ACCOUNT_REP_ID = row.Field <int>("ACCOUNT_REP_ID").ToString(),
LEAK_COUNT = row.Field <int>("LEAK_COUNT").ToString(),
CONTRACT_COUNT = row.Field <int>("CONTRACT_COUNT").ToString(),
BILLABLE_COUNT = row.Field <int>("BILLABLE_COUNT").ToString(),
NONBILLABLE_COUNT = row.Field <int>("NONBILLABLE_COUNT").ToString(),
CANCELLED_COUNT = row.Field <int>("CANCELLED_COUNT").ToString(),
TOTAL_INVOICE_COUNT = row.Field <int>("TOTAL_INVOICE_COUNT").ToString(),
DATE_CREATED = row.Field <DateTime?>("DATE_CREATED"),
DATE_MODIFIED = row.Field <DateTime?>("DATE_MODIFIED")
});
return(Json(tstData, JsonRequestBehavior.AllowGet));
}
public JsonResult GetAccountReps()
{
string query = "SELECT [EMPLOYEE_ID],[EMPLOYEE] " +
"FROM [TST_DATA].[dbo].[TST_Employees] " +
"WHERE [EMPLOYEE_TYPE] = 'SALES' AND [EMPLOYEE_STATUS]='Active' " +
"ORDER BY [EMPLOYEE]";
DataAccess.TSTDataAccess da = new DataAccess.TSTDataAccess();
DataTable dt = da.ReadDataTable(query);
var tstData = (from row in dt.AsEnumerable()
select new
{
EMPLOYEE_ID = row.Field <int>("EMPLOYEE_ID").ToString(),
EMPLOYEE = row.Field <string>("EMPLOYEE").ToString()
});
return(Json(tstData, JsonRequestBehavior.AllowGet));
}
public JsonResult GetMASCompany(string COMPANY_ID)
{
string query = "SELECT [MAS_CUSTOMER_ID],[COMPANY_NAME],[ADDRESS],[ADDRESS_2] ,[CITY] ,[STATE] ,[ZIP] " +
"FROM [TST_DATA].[dbo].[MAS_Companies] " +
"WHERE [MAS_CUSTOMER_ID] = '" + COMPANY_ID + "' " +
"ORDER BY [COMPANY_NAME]";
DataAccess.TSTDataAccess da = new DataAccess.TSTDataAccess();
DataTable dt = da.ReadDataTable(query);
var tstData = (from row in dt.AsEnumerable()
select new
{
MAS_CUSTOMER_ID = row.Field <string>("MAS_CUSTOMER_ID").ToString(),
COMPANY_NAME = row.Field <string>("COMPANY_NAME").ToString(),
ADDRESS = row.Field <string>("ADDRESS").ToString(),
ADDRESS_2 = row.Field <string>("ADDRESS_2").ToString(),
CITY = row.Field <string>("CITY").ToString(),
STATE = row.Field <string>("STATE").ToString(),
ZIP = row.Field <string>("ZIP").ToString()
});
return(Json(tstData, JsonRequestBehavior.AllowGet));
}
public JsonResult AddEditCompany(int COMP_ID, string COMPANY_NAME, string COMPANY_ID, string ACCOUNT_TYPE
, string ACCOUNT_REP, int ACCOUNT_REP_ID, string ADDRESS, string ADDRESS_2, string CITY, string STATE, string ZIP
, string BILL_ADDRESS, string BILL_ADDRESS_2, string BILL_CITY, string BILL_STATE, string BILL_ZIP, string frmAction)
{
if (frmAction == "Add")
{
string queryAdd = "INSERT INTO [TST_DATA].[dbo].[TST_COMPANIES] " +
"([COMPANY_NAME],[COMPANY_ID],[ACCOUNT_TYPE] ,[ACCOUNT_REP] ,[ACCOUNT_REP_ID]" +
",[ADDRESS],[ADDRESS_2],[CITY] ,[STATE] ,[ZIP]" +
",[BILL_ADDRESS],[BILL_ADDRESS_2],[BILL_CITY],[BILL_STATE],[BILL_ZIP],[DATE_CREATED]) " +
"VALUES ('" + COMPANY_NAME + "','" + COMPANY_ID + "','" + ACCOUNT_TYPE + "','" + ACCOUNT_REP + "'" + "','" + ACCOUNT_REP_ID + "'" +
"','" + ADDRESS + "','" + ADDRESS_2 + "','" + CITY + "'" + "','" + STATE + "'" + "','" + ZIP + "'" +
"','" + BILL_ADDRESS + "','" + BILL_ADDRESS_2 + "','" + BILL_CITY + "'" + "','" + BILL_STATE + "'" + "','" + BILL_ZIP + "',GETDATE())";
DataAccess.TSTDataAccess daAdd = new DataAccess.TSTDataAccess();
bool actionResults = daAdd.QryCommand(queryAdd);
string queryGetNewRecord = "SELECT MAX(COMP_ID) AS COMP_ID,ACTION_RESULTS = '" + actionResults.ToString() + "' " +
"FROM [TST_DATA].[dbo].[TST_COMPANIES] " +
"WHERE [COMPANY_ID]='" + COMPANY_ID + "' AND " +
"[COMPANY_NAME]='" + COMPANY_NAME + "'";
DataAccess.TSTDataAccess daNewRec = new DataAccess.TSTDataAccess();
DataTable dt = daNewRec.ReadDataTable(queryGetNewRecord);
var tstData = (from row in dt.AsEnumerable()
select new
{
COMP_ID = row.Field <int>("COMP_ID"),
ACTION_RESULTS = row.Field <string>("ACTION_RESULTS").ToString()
});
return(Json(tstData, JsonRequestBehavior.AllowGet));
}
else
{
string queryOrigRec = "SELECT [COMPANY_ID],[COMPANY_NAME] " +
", ISNULL([ACCOUNT_REP],'Unknown') AS ACCOUNT_REP ,ISNULL([ACCOUNT_REP_ID],280) AS ACCOUNT_REP_ID" +
" FROM [TST_DATA].[dbo].[TST_COMPANIES] " +
" WHERE [COMP_ID]=" + COMP_ID;
DataAccess.TSTDataAccess daOrigRec = new DataAccess.TSTDataAccess();
DataTable dtOrigRec = daOrigRec.ReadDataTable(queryOrigRec);
string origCompanyID = dtOrigRec.Rows[0].Field <string>("COMPANY_ID");
string origCompanyName = dtOrigRec.Rows[0].Field <string>("COMPANY_NAME");
string origAcctRep = dtOrigRec.Rows[0].Field <string>("ACCOUNT_REP");
int origAcctRepID = dtOrigRec.Rows[0].Field <int>("ACCOUNT_REP_ID");
string queryEdit = "UPDATE [TST_DATA].[dbo].[TST_COMPANIES] SET " +
"[COMPANY_ID] = '" + COMPANY_ID + "'" +
",[ACCOUNT_TYPE]='" + ACCOUNT_TYPE.ToUpper() + "'" +
",[ACCOUNT_REP]='" + ACCOUNT_REP + "'" +
",[ACCOUNT_REP_ID]='" + ACCOUNT_REP_ID + "'" +
",[ADDRESS]='" + ADDRESS + "'" +
",[ADDRESS_2] = '" + ADDRESS_2 + "'" +
",[CITY]='" + CITY + "'" +
",[STATE]='" + STATE + "'" +
",[ZIP]='" + ZIP + "'" +
",[BILL_ADDRESS]='" + BILL_ADDRESS + "'" +
",[BILL_ADDRESS_2] = '" + BILL_ADDRESS_2 + "'" +
",[BILL_CITY]='" + BILL_CITY + "'" +
",[BILL_STATE]='" + BILL_STATE + "'" +
",[BILL_ZIP]='" + BILL_ZIP + "'" +
",[DATE_MODIFIED]=GETDATE() " +
" WHERE [COMP_ID] = " + COMP_ID;
DataAccess.TSTDataAccess daAdd = new DataAccess.TSTDataAccess();
bool actionResults = daAdd.QryCommand(queryEdit);
string queryGetUpdatedRecord = "SELECT COMP_ID,ACTION_RESULTS = '" + actionResults.ToString() + "' " +
"FROM [TST_DATA].[dbo].[TST_COMPANIES] " +
"WHERE [COMP_ID]=" + COMP_ID;
DataAccess.TSTDataAccess daNewRec = new DataAccess.TSTDataAccess();
DataTable dt = daNewRec.ReadDataTable(queryGetUpdatedRecord);
var tstData = (from row in dt.AsEnumerable()
select new
{
COMP_ID = row.Field <int>("COMP_ID"),
ACTION_RESULTS = row.Field <string>("ACTION_RESULTS").ToString()
});
//update invoices with the new company id only if it has changed
if (origCompanyID != COMPANY_ID || origCompanyName != COMPANY_NAME)
{
string qryUpdateInvCompanyID = "UPDATE [TST_DATA].[dbo].[JOB_DETAILS] SET COMPANY_ID = '" + COMPANY_ID + "', COMPANY_NAME='" + COMPANY_NAME + "' " +
"WHERE COMPANY_ID='" + origCompanyID + "' AND COMPANY_NAME='" + origCompanyName + "'";
DataAccess.TSTDataAccess daUpdateInvCompanyID = new DataAccess.TSTDataAccess();
bool updateInvCompanyIDResults = daUpdateInvCompanyID.QryCommand(qryUpdateInvCompanyID);
//Update the pending commission record for the company if it had been changed
string qryUpdateCommRecCompanyID = "UPDATE [TST_DATA].[dbo].[TST_COMMISSION_RECORDS] SET COMPANY_ID = '" + COMPANY_ID + "' " +
"WHERE COMPANY_ID='" + origCompanyID + "' AND SVC_STATUS in ('Pending') ";
DataAccess.TSTDataAccess daUpdateCommRecCompanyID = new DataAccess.TSTDataAccess();
bool updateCommRecCompanyIDResults = daUpdateCommRecCompanyID.QryCommand(qryUpdateCommRecCompanyID);
}
if (origAcctRepID != ACCOUNT_REP_ID)
{
//if the original acct rep was unknown so update all invoices with the new acct rep
if (origAcctRepID == 123)
{
string qryUpdateInvAcctRep = "UPDATE [TST_DATA].[dbo].[JOB_DETAILS] SET BILL_TO_ACCOUNT_MANAGER = '" + ACCOUNT_REP + "' " +
"WHERE COMPANY_ID='" + COMPANY_ID + "' AND BILL_TO_ACCOUNT_MANAGER='" + origAcctRep + "'";
DataAccess.TSTDataAccess daUpdateInvAcctRep = new DataAccess.TSTDataAccess();
bool updateInvAcctRepResults = daUpdateInvAcctRep.QryCommand(qryUpdateInvAcctRep);
//Update the commission record for the account manager if it had been originally set to unknown
string qryUpdateCommRecAcctRep = "UPDATE [TST_DATA].[dbo].[TST_COMMISSION_RECORDS] SET BILL_TO_ACCOUNT_MANAGER = '" + ACCOUNT_REP + "' " +
"WHERE COMPANY_ID='" + COMPANY_ID + "' AND ISNULL(cr.SVC_STATUS,'Pending') IN ('Pending') AND BILL_TO_ACCOUNT_MANAGER='" + origAcctRep + "'";
DataAccess.TSTDataAccess daUpdateCommRecAcctRep = new DataAccess.TSTDataAccess();
bool updateCommRecAcctRepResults = daUpdateCommRecAcctRep.QryCommand(qryUpdateCommRecAcctRep);
//Update the commission record for the employee owed (AcctMgr only) if it had been originally set to unknown
string qryUpdateCommRecEmpOwed = "UPDATE [TST_DATA].[dbo].[TST_COMMISSION_RECORDS] SET EMPLOYEE = '" + ACCOUNT_REP + "', EMPLOYEE_ID= " + ACCOUNT_REP_ID +
"WHERE COMPANY_ID='" + COMPANY_ID + "' AND EMPLOYEE_TYPE='ACCT_MGR' ANDISNULL(cr.SVC_STATUS,'Pending') IN ('Pending') AND EMPLOYEE_ID='" + origAcctRepID + "'";
DataAccess.TSTDataAccess daUpdateCommRecEmpOwed = new DataAccess.TSTDataAccess();
bool updateCommRecEmpOwedpResults = daUpdateCommRecEmpOwed.QryCommand(qryUpdateCommRecAcctRep);
}
}
return(Json(tstData, JsonRequestBehavior.AllowGet));
}
}
