public static string getExistIndividualsAndScores(int obj, string rmNum)
{
string j1 = Mydb.ExecuteReadertoDataTableAsJson(@"select NUMBER from PER_SCORE where IS_DELETED=0 and ROOM_ID in
( select ROOM_ID from ROOM where OBJECT_ID =@obj and ROOM_NUMBER=@rmNum and IS_DELETED=0)", new SqlParameter[] { new SqlParameter("@obj", obj), new SqlParameter("@rmNum", rmNum) }, CommandType.Text);
// j1.Length
dynamic j1_ = JsonConvert.DeserializeObject(j1);
List <Dictionary <string, object> > rows = new List <Dictionary <string, object> >();
// rows.Count
if (j1_.Count != 0)
{
j1_ = j1_[0].NUMBER;
string j2 = Mydb.ExecuteReadertoDataTableAsJson("select * from IND_NAME where IS_DELETED=0 and INDIVIDUAL_ID in (select INDIVIDUAL_ID from INDIVIDUAL_PERSCORE where SCORE_ID=@Score and IS_DELETED=0)", new SqlParameter[] { new SqlParameter("@Score", Convert.ToString(j1_)) }, CommandType.Text);
var row = new Dictionary <string, object>();
row.Add("NummberJson", j1);
row.Add("IndividualJson", j2);
rows.Add(row);
}
//JsonConvert.SerializeObject(j1 + j2);//j1 +","+ j2;
JavaScriptSerializer js = new JavaScriptSerializer();
return(js.Serialize(rows));
}
public static string GetServicesForSupplierSelected(int obj, string supp_guid, string service_guid)
{
string project_guid = Mydb.ExecuteScalar("[GetProjectGuidBySuppGud_LK]", new SqlParameter[] { new SqlParameter("@guid", supp_guid) }, CommandType.StoredProcedure).ToString();
return(Mydb.ExecuteReadertoDataTableAsJson("TestDB.[dbo].[sp_QUICK_API_get_mp_services_web]", new SqlParameter[] { new SqlParameter("@project", project_guid), new SqlParameter("@supplier", supp_guid), new SqlParameter("@service_guid", service_guid) }, CommandType.StoredProcedure));
}
public static string GetExistServices(int prj, int dId)
{/*select ps.SERVICE_ID,ps.SERVICE_NAME,ps.QUANTITY_IS,ps.UNIT_OF_MEASURE_ID from PRODUCT_SERVICE ps inner join PROJECT_PRODUCT_SERVICE pps on pps.PRODUCT_SERVICE_ID=ps.SERVICE_ID and pps.PROJECT_ID=@prj and ps.DIRECTION_ID=@d*/
return(Mydb.ExecuteReadertoDataTableAsJson(@"select
ps.SERVICE_ID,
ps.SERVICE_NAME,
ps.QUANTITY_IS,
ps.UNIT_OF_MEASURE_ID,
pps.COST,
pps.DATE_START
from PRODUCT_SERVICE ps
inner join
(
SELECT *
FROM
PROJECT_PRODUCT_SERVICE AS [data]
WHERE
DATE_START = (SELECT max(DATE_START)
FROM
PROJECT_PRODUCT_SERVICE
WHERE
PRODUCT_SERVICE_ID = [data].PRODUCT_SERVICE_ID)
OR DATE_START is NULL
)
pps on
pps.PRODUCT_SERVICE_ID=ps.SERVICE_ID and pps.PROJECT_ID=@prj and ps.DIRECTION_ID=@d", new SqlParameter[] { new SqlParameter("@prj", prj), new SqlParameter("@d", dId) }, CommandType.Text));
}
public static string getServices(int dId, int lg)
{
return(Mydb.ExecuteReadertoDataTableAsJson(@"select distinct s.SERVICE_ID,s.SERVICE_NAME,s.SUPP_ID,s.COMMENT, s.UNIT_MEASURE as SERVICE_TYPE_NAME,sc.COST,sc.QUANTITY_IS from SERVICES_FOR_SUPPLIERS s
inner join
(select * from SUPPLIER_SERVICE_COSTS as [data] where DATE_START=(select max (DATE_START)
from SUPPLIER_SERVICE_COSTS where SERVICE_ID=[data].SERVICE_ID)
) sc on sc.SERVICE_ID=s.SERVICE_ID
where s.SERVICE_DIRECT_ID=@dId and s.SUPP_ID=(select MAN_COMP_ID from DISP where
DISP_ID = (select DISP_ID from DISP_ACC where LOG_IN_ID = @lg))", new SqlParameter[] { new SqlParameter("@dId", dId), new SqlParameter("@lg", lg) }, CommandType.Text));
}
public ContentResult LoginSecond(string sc, string pass)
{
string result = "";
int ObjCount = (int)Mydb.ExecuteScalar("select COUNT(OBJECT_ID) from PER_SCORE where SCORE_ID=@sc", new SqlParameter[] { new SqlParameter("@sc", sc) }, CommandType.Text);
if (ObjCount == 1)
{
int objId = (int)Mydb.ExecuteScalar("select OBJECT_ID from PER_SCORE where SCORE_ID=@sc", new SqlParameter[] { new SqlParameter("@sc", sc) }, CommandType.Text);
int ExistOrNot = (int)Mydb.ExecuteScalar("select COUNT(*) from PER_SCORE where SCORE_ID=@sc and PASS=@pass and OBJECT_ID=@objId", new SqlParameter[] { new SqlParameter("@sc", sc), new SqlParameter("@pass", pass), new SqlParameter("@objId", objId) }, CommandType.Text);
if (ExistOrNot == 1)
{
int ProjectId = (int)Mydb.ExecuteScalar("select PROJECT_ID from OBJECT where OBJECT_ID=@ObjId", new SqlParameter[] { new SqlParameter("@ObjId", objId) }, CommandType.Text);
result = "{\"result\" : \"1\",\"objId\":\"" + objId + "\",\"C_Score\":\"" + sc + "\",\"ProjectId\":\"" + ProjectId + "\"}";
}
else
{
int ProjectId = (int)Mydb.ExecuteScalar("select PROJECT_ID from OBJECT where OBJECT_ID=@ObjId", new SqlParameter[] { new SqlParameter("@ObjId", objId) }, CommandType.Text);
string LoginBitrix = Mydb.ExecuteReadertoDataTableAsJson("usp_CHECK_BITRIX_USER", new SqlParameter[] { new SqlParameter("@login", sc), new SqlParameter("@pwd", pass) }, CommandType.StoredProcedure);
dynamic jsonLoginBitrix = JsonConvert.DeserializeObject(LoginBitrix);
if (jsonLoginBitrix[0].LOTUS_GUID != "Login failed")
{
result = "{\"result\" : \"1\",\"objId\":\"" + objId + "\",\"C_Score\":\"" + sc + "\",\"ProjectId\":\"" + ProjectId + "\"}";
}
else
{
result = "{\"result\" : \"0\"}";
}
}
}
if (ObjCount > 1)
{
int objId = (int)Mydb.ExecuteScalar("select OBJECT_ID from PER_SCORE where SCORE_ID=@sc", new SqlParameter[] { new SqlParameter("@sc", sc) }, CommandType.Text);
int ProjectId = (int)Mydb.ExecuteScalar("select PROJECT_ID from OBJECT where OBJECT_ID=@ObjId", new SqlParameter[] { new SqlParameter("@ObjId", objId) }, CommandType.Text);
string LoginBitrix = Mydb.ExecuteReadertoDataTableAsJson("usp_CHECK_BITRIX_USER", new SqlParameter[] { new SqlParameter("@login", sc), new SqlParameter("@pwd", pass) }, CommandType.StoredProcedure);
dynamic jsonLoginBitrix = JsonConvert.DeserializeObject(LoginBitrix);
if (jsonLoginBitrix[0].LOTUS_GUID != "Login failed")
{
result = "{\"result\" : \"1\",\"objId\":\"" + objId + "\",\"C_Score\":\"" + sc + "\",\"ProjectId\":\"" + ProjectId + "\"}";
}
else
{
result = "{\"result\" : \"0\"}";
}
}
if (ObjCount == 0)
{
result = "{\"result\" : \"0\"}";
}
//res.Content = new StringContent(result.ToString(), Encoding.UTF8, "text/csv");
return(Content(result, "application/json"));//result
}
public static string GetServicesForSupplier(int obj, string supp_guid, string project_guid, string service_guid)
{
//string SUPPS = Mydb.ExecuteReadertoDataTableAsJson("GetSuppGuid_andProjectGuid", new SqlParameter[] { new SqlParameter("@obj", obj), new SqlParameter("@lg", lg) }, CommandType.StoredProcedure);
//dynamic SUPPS_ = JsonConvert.DeserializeObject(SUPPS);
////List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
//string supp_guid = Convert.ToString(SUPPS_[0].SUPP_GUID);
//string project_guid = Convert.ToString(SUPPS_[0].PROJECT_GUID);
return(Mydb.ExecuteReadertoDataTableAsJson("TestDB.[dbo].[sp_QUICK_API_get_mp_services_web]", new SqlParameter[] { new SqlParameter("@project", project_guid), new SqlParameter("@supplier", supp_guid), new SqlParameter("@service_guid", service_guid) }, CommandType.StoredProcedure));
}
public static string GetRelatedDirects(int lg, int sid)
{
return(Mydb.ExecuteReadertoDataTableAsJson(@"select DIRECTION_ID,DIRECTION_NAME, ICON as ICON_ADRESS,SERVICE_SET_ID from SERVICE_DIRECT_FOR_SUPPLIERS where SERVICE_SET_ID=@sid and SUPP_ID = (select MAN_COMP_ID from DISP where
DISP_ID = (select DISP_ID from DISP_ACC where LOG_IN_ID = @lg))", new SqlParameter[] { new SqlParameter("@sid", sid), new SqlParameter("@lg", lg) }, CommandType.Text));
}
public static string getServiceSet(int lg)
{
return(Mydb.ExecuteReadertoDataTableAsJson(@"select * from SERVICE_SET_FOR_SUPPLIERS where
SUPP_ID = (select MAN_COMP_ID from DISP where
DISP_ID = (select DISP_ID from DISP_ACC where LOG_IN_ID = @lg))", new SqlParameter[] { new SqlParameter("@lg", lg) }, CommandType.Text));
}
// GET: Login_Fili
public string GetProjectDatas(int prj)
{
return(Mydb.ExecuteReadertoDataTableAsJson("select * from PROJECTS where PROJECT_ID=@prj", new SqlParameter[] { new SqlParameter("@prj", prj) }, CommandType.Text));
}
public static string getObjectDisp(int lg)
{
return(Mydb.ExecuteReadertoDataTableAsJson("select * from OBJECT where OBJECT_ID in (select OBJECT_ID from DISP_OBJECT where DISP_ID =(select DISP_ID from DISP_ACC where LOG_IN_ID=@lg))", new SqlParameter[] { new SqlParameter("@lg", lg) }, CommandType.Text));
}
public static string getSuppsDatas(int obj, int lg)
{
return(Mydb.ExecuteReadertoDataTableAsJson("GetSuppGuid_andProjectGuid", new SqlParameter[] { new SqlParameter("@obj", obj), new SqlParameter("@lg", lg) }, CommandType.StoredProcedure));
}
public static string GetRequestbyId(int Rid)
{
DataTable dt = Mydb.ExecuteReadertoDataTable("getRequest_SUPPLIER_byId", new SqlParameter[] { new SqlParameter("@R", Rid) }, CommandType.StoredProcedure);
List <Request> rs = new List <Request>();
if (dt.Rows.Count != 0)
{
foreach (DataRow item in dt.Rows)
{
Request r = new Request(); //EMERGENCY_TREATMENT,PAYMENT,PLAN_END_DATE,PLAN_END_TIME
r.EMERGENCY_TREATMENT = Convert.ToBoolean(item["EMERGENCY_TREATMENT"]); //
r.PAYMENT = Convert.ToBoolean(item["PAYMENT"]); // r.ADRESS = item["ADRESS"].ToString();
// r.i_IND_PHONE_NUMBER = item["IND_PHONE_NUMBER"].ToString();
// r.im_FIRST_NAME = item["FIRST_NAME"].ToString();
// r.ROOM_T = item["ROOM_T"].ToString();
r.TOTAL_COST = item["TOTAL_COST"].ToString(); //
r.PLAN_END_DATE = item["PLAN_END_DATE"].ToString(); //
r.PLAN_END_TIME = item["PLAN_END_TIME"].ToString(); //
// r.DELIVERY_TYPE_ID = Convert.ToInt32(item["DELIVERY_TYPE_ID"]);//
r.SPECIALIS_ID = Convert.ToInt32(item["SPECIALIS_ID"]); //
r.REQUEST_TEXT = item["REQUEST_TEXT"].ToString(); //
// r.REQUEST_COMMENT = item["REQUEST_COMMENT"].ToString();//
// r.COMMENT_FILE = item["COMMENT_FILE"].ToString();//
r.LOG_IN_ID = Convert.ToInt32(item["LOG_IN_ID"]); //
r.ACCOUNT_NAME = item["ACCOUNT_NAME"].ToString(); //
r.STATUS = item["STATUS_ID"].ToString(); //
r.REQUEST_ID = Convert.ToInt32(item["REQUEST_ID"]); //
r.ATRIBUTE = item["SPECIALIST_NAME"].ToString();
string ind = (item["INDIVIDUAL_ID"].ToString().Length == 0) ? "0" : item["INDIVIDUAL_ID"].ToString();
if (ind == "")
{
r.INDIVIDUAL_ID = 0;
}
else
{
r.INDIVIDUAL_ID = (item["INDIVIDUAL_ID"].ToString().Length == 0) ? 0 : Convert.ToInt32(item["INDIVIDUAL_ID"].ToString());
}
r.SERVICE_NAME = item["NUMBER"].ToString();
r.TOTAL_COST = item["PHONE_NUMBER"].ToString();
r.SERVICE_GROUP_ID = (int)item["SERVICE_GROUP_ID"];// service Set
r.ADRESS = item["OBJECT_ID"].ToString();
// r.ROOM_COUNT = Convert.ToInt32(item["ROOM_COUNT"]);
r.ROOM_COUNT = Mydb.ExecuteReadertoDataTableAsJson("GetServices_SUPPLIERS_ByLevel", new SqlParameter[] { new SqlParameter("@R", r.REQUEST_ID) }, CommandType.StoredProcedure);
rs.Add(r);
}
JavaScriptSerializer js = new JavaScriptSerializer();
return(js.Serialize(rs));
}
else
{
DataTable dt2 = Mydb.ExecuteReadertoDataTable("GetTenantRequest", new SqlParameter[] { new SqlParameter("@R", Rid) }, CommandType.StoredProcedure);
List <RequestTenant> rsts = new List <RequestTenant>();
foreach (DataRow item in dt2.Rows)
{
RequestTenant rst = new RequestTenant();
rst.ROOM_T = item["ROOM_T"].ToString();
rst.ACCOUNT_NAME = "~";
rst.FIRST_NAME = item["FIRST_NAME"].ToString();
rst.NUMBER = item["NUMBER"].ToString();
rst.OBJECT_ID = item["OBJECT_ID"].ToString();
rst.PHONE = item["PHONE_NUMBER"].ToString();
rst.ROOM_NUMBER = item["ROOM_NUMBER"].ToString();
rst.INDIVIDUAL_ID = (item["INDIVIDUAL_ID"].ToString().Length == 0) ? "0" : item["INDIVIDUAL_ID"].ToString();
rst.SERVICE_GROUP_ID = (int)item["SERVICE_GROUP_ID"];
string comf = (item["COMFORDATE"].ToString().Length != 0) ? "|" + "Желаемая дата: (" + item["COMFORDATE"].ToString().Substring(0, item["COMFORDATE"].ToString().LastIndexOf(' ')) + ")\n Желаемое время : c (" + item["COM_TIME_FROM"].ToString().Substring(0, item["COM_TIME_FROM"].ToString().LastIndexOf(':')).Replace(':', '-') + ") по (" + item["COM_TIME_TO"].ToString().Substring(0, item["COM_TIME_TO"].ToString().LastIndexOf(':')).Replace(':', '-') + ")" : "";
rst.REQUEST_TEXT = (item["COMFORDATE"].ToString().Length == 0) ? item["REQUEST_TEXT"].ToString() : item["REQUEST_TEXT"].ToString() + comf;
rst.EMERGENCY_TREATMENT = Convert.ToBoolean(item["EMERGENCY_TREATMENT"]);
rst.PAYMENT = (item["PAYMENT"].ToString().Length == 0) ? false : Convert.ToBoolean(item["PAYMENT"]);
rst.PLAN_END_DATE = item["PLAN_END_DATE"].ToString();;
rst.PLAN_END_TIME = item["PLAN_END_TIME"].ToString();
rst.SPECIALIST_ID = (item["SPECIALIS_ID"].ToString().Length != 0) ? (int)item["SPECIALIS_ID"] : 0;
rsts.Add(rst);
}
JavaScriptSerializer js2 = new JavaScriptSerializer();
return(js2.Serialize(rsts));
}
}
public static string GetSuppliers(int Cid)
{
return(Mydb.ExecuteReadertoDataTableAsJson("GetSuppliers", new SqlParameter[] { new SqlParameter("@Cid", Cid) }, CommandType.StoredProcedure));
}
public string GetNewsDetail(string guid, string lg)
{
string result = Mydb.ExecuteReadertoDataTableAsJson("GetNews", new SqlParameter[] { new SqlParameter("@guid", guid), new SqlParameter("@lg", lg) }, CommandType.StoredProcedure);
return(result);
}
public static string getProjectNamebyLogin(int lg)
{
return(Mydb.ExecuteReadertoDataTableAsJson("getProjectNamebyLogin", new SqlParameter[] { new SqlParameter("@lg", lg) }, CommandType.StoredProcedure));
}
public static string GetResponsibels()
{
return(Mydb.ExecuteReadertoDataTableAsJson("GetResponsibels", new SqlParameter[] { }, CommandType.StoredProcedure));
}
public static string GetServicesForAll(int dId)
{
return(Mydb.ExecuteReadertoDataTableAsJson("select * from PRODUCT_SERVICE where DIRECTION_ID=@dId", new SqlParameter[] { new SqlParameter("@dId", dId) }, CommandType.Text));
}
public static string GetAllDirectsforAll(int sid)
{
return(Mydb.ExecuteReadertoDataTableAsJson("select * from SERVICE_DIRECT where SS_ID=@sid", new SqlParameter[] { new SqlParameter("@sid", sid) }, CommandType.Text));
}
// GET: PassRequest
public string Tenant_Datas_For_Pass(string sc)
{
return(Mydb.ExecuteReadertoDataTableAsJson("Tenant_Datas_For_Pass", new SqlParameter[] { new SqlParameter("@sc", sc) }, CommandType.StoredProcedure));
}
public static string getServiceSetIcon()
{
return(Mydb.ExecuteReadertoDataTableAsJson("select * from SERVICE_SET_ICONS", new SqlParameter[] { }, CommandType.Text));
}
public static string GetProjects(int UprId)
{
return(Mydb.ExecuteReadertoDataTableAsJson("select * from PROJECTS where PROJECT_ID in (select PROJECT_ID from OBJECT where LOG_IN_ID=@UprId)", new SqlParameter[] { new SqlParameter("@UprId", UprId) }, CommandType.Text));
}
public static string GetDatasForRaport(int type, string Stext, string MultypleData)
{
return(Mydb.ExecuteReadertoDataTableAsJson("GetDatasForRaport", new SqlParameter[] { new SqlParameter("@type", type)
, new SqlParameter("@Stext", Stext), new SqlParameter("@MultypleData", MultypleData) }, CommandType.StoredProcedure));
}
public static string GetSupplierDetail(string guid)
{
return(Mydb.ExecuteReadertoDataTableAsJson("GetSupplierDetail", new SqlParameter[] { new SqlParameter("@guid", guid) }, CommandType.StoredProcedure));
}
public string GetNewsLK(int proj)
{
return(Mydb.ExecuteReadertoDataTableAsJson("GetNewsLK", new SqlParameter[] { new SqlParameter("@proj", proj) }, CommandType.StoredProcedure));
}
