public static ResObject <List <Contact> > GeContacts(string nvGuide)
{
ResObject <List <Contact> > res = new ResObject <List <Contact> >();
try
{
List <Contact> contacts = new List <Contact>();
DataSet ds = SqlDataAccess.ExecuteDatasetSP("TContact_SLCT",
new SqlParameter("nvGuide", nvGuide));
if (ds.Tables[0].Rows[0][0].ToString() != string.Empty && int.Parse(ds.Tables[0].Rows[0][0].ToString()) < 0)
{
res.Error = new ErrorObj(StatusCode.UserUnauthorized);
res.Result = null;
return(res);
}
if (ds != null && ds.Tables[1].Rows.Count > 0)
{
contacts = ObjectGenerator <Contact> .GeneratListFromDataRowCollection(ds.Tables[1].Rows);
}
res.Error = new ErrorObj(StatusCode.Success);
res.Result = contacts;
return(res);
}
catch (Exception x)
{
Log.LogError("GeContacts", x.Message);
res.Error = new ErrorObj(StatusCode.Error, x.Message);
res.Result = null;
return(res);
}
}
public static twoList RecommendationAll()
{
twoList res = new twoList()
{
recommendations1 = new List <Recommendation>(),
recommendations2 = new List <Recommendation>()
};
try
{
DataSet ds = SqlDataAccess.ExecuteDatasetSP("All_Recommendation_SLCT");
if (ds.Tables[0].Rows.Count > 0)
{
res.recommendations1 = ObjectGenerator <Recommendation> .GeneratListFromDataRowCollection(ds.Tables[0].Rows);
res.recommendations2 = ObjectGenerator <Recommendation> .GeneratListFromDataRowCollection(ds.Tables[1].Rows);
}
return(res);
}
catch (Exception x)
{
Log.LogError("RecommendationAll", x.Message);
res = null;
return(res);
}
}
public static Dictionary <string, List <CodeTable> > GetListCodeTables(string sProcName, List <SqlParameter> parameters = null)
{
try
{
DataSet ds;
if (parameters != null)
{
ds = SqlDataAccess.ExecuteDatasetSP(sProcName, parameters);
}
else
{
ds = SqlDataAccess.ExecuteDatasetSP(sProcName);
}
Dictionary <string, List <CodeTable> > codeTables = new Dictionary <string, List <CodeTable> >();
for (int i = 0; i < ds.Tables.Count; i++)
{
codeTables.Add(ds.Tables[i++].Rows[0][0].ToString(), ObjectGenerator <CodeTable> .GeneratListFromDataRowCollection(ds.Tables[i].Rows));
}
return(codeTables);
}
catch (Exception ex)
{
Log.ExceptionLog(ex.Message, "GetListCodeTables" + " , sProcName:" + sProcName);
return(null);
}
}
public static List <SysTables> GetAllNames()
{
try
{
DataTable dt = SqlDataAccess.ExecuteDatasetSP("TSysTables_SLCT").Tables[0];
DataRowCollection drc = dt.Rows;
return(ObjectGenerator <SysTables> .GeneratListFromDataRowCollection(drc));
}
catch (Exception ex)
{
Log.LogError("GetAllNames / TSysTables_SLCT", ", ex " + ex);
return(null);
}
}
public static List <TGlobalParameters> GetGlobalParameters()
{
try
{
DataTable dt = SqlDataAccess.ExecuteDatasetSP("TGlobalParameters_SLCT").Tables[0];
DataRowCollection drc = dt.Rows;
return(ObjectGenerator <TGlobalParameters> .GeneratListFromDataRowCollection(drc));
}
catch (Exception ex)
{
Log.LogError("SaveGlobalParameters / TGlobalParameters_SLCT", ", ex " + ex);
return(null);
}
}
public static List <User> GetUsers(int iPersonId)
{
try
{
DataRowCollection drc = SqlDataAccess.ExecuteDatasetSP("TUser_GetUsers_SLCT", new SqlParameter("iPersonId", iPersonId)).Tables[0].Rows;
List <User> users = ObjectGenerator <User> .GeneratListFromDataRowCollection(drc);
return(users);
}
catch (Exception ex)
{
Log.LogError("GetUsers / TUser_GetUsers_SLCT", "iPersonId:" + iPersonId + ",ex " + ex);
return(null);
}
}
public static List <Yeshivot> GetAllYeshivot()
{
try
{
DataRowCollection drc = SqlDataAccess.ExecuteDatasetSP("TYeshivot_SLCT").Tables[0].Rows;
List <Yeshivot> yeshivots = ObjectGenerator <Yeshivot> .GeneratListFromDataRowCollection(drc);
return(yeshivots);
}
catch (Exception ex)
{
Log.LogError("GetAllYeshivot / TYeshivot_SLCT", "ex: " + ex);
return(null);
}
}
public static List <SysTableRow> GetValues(int iSysTableId)
{
try
{
SqlParameter parameter = new SqlParameter("iSysTableId", iSysTableId);
DataTable dt = SqlDataAccess.ExecuteDatasetSP("TSysTableRow_ByTableId_SLCT", parameter).Tables[0];
DataRowCollection drc = dt.Rows;
return(ObjectGenerator <SysTableRow> .GeneratListFromDataRowCollection(drc));
}
catch (Exception ex)
{
Log.LogError("GetValues / TSysTableRow_ByTableId_SLCT", "iSysTableId: " + iSysTableId + ", ex " + ex);
return(null);
}
}
public static List <Avrech> GetAllAvrechim(int?iPersonId)
{
try
{
DataRowCollection drc = SqlDataAccess.ExecuteDatasetSP("TAvrech_SLCT", new SqlParameter("iPersonId", iPersonId)).Tables[0].Rows;
List <Avrech> avrech = ObjectGenerator <Avrech> .GeneratListFromDataRowCollection(drc);
return(avrech);
}
catch (Exception ex)
{
Log.LogError("GetAvrechim / TAvrech_SLCT", ", ex " + ex);
return(null);
}
}
public static List <Meeting> GetMeetingsByStudentId(int iPersonId)
{
try
{
DataRowCollection drc = SqlDataAccess.ExecuteDatasetSP("TMeeting_SLCT", new SqlParameter("iPersonId", iPersonId)).Tables[0].Rows;
List <Meeting> meetings = ObjectGenerator <Meeting> .GeneratListFromDataRowCollection(drc);
return(meetings);
}
catch (Exception ex)
{
Log.LogError("GetMeetings / TMeeting_SLCT", ", ex " + ex);
return(null);
}
}
public static List <Student> GetAvrechStudents(int iPersonId)
{
try
{
DataRowCollection drc = SqlDataAccess.ExecuteDatasetSP("TStudent_ByAvrechId_SLCT", new SqlParameter("iPersonId", iPersonId)).Tables[0].Rows;
List <Student> students = ObjectGenerator <Student> .GeneratListFromDataRowCollection(drc);
return(students);
}
catch (Exception ex)
{
Log.LogError("GetAvrechStudents / TAvrechStudents_ByAvrechId_SLCT", ", ex " + ex);
return(null);
}
}
//[DataMember]
//public DateTime? dtCreatedate { get; set; }
//[DataMember]
//public int iLastModifyUserId { get; set; }
//[DataMember]
//public DateTime? dtLastModifyDate { get; set; }
//[DataMember]
//public bool bSysRowStatus { get; set; }
#endregion
public static List <Event1> GetEventsList(int?iUserId)
{
try
{
DataRowCollection drc = SqlDataAccess.ExecuteDatasetSP("TEvent_SLCT", new SqlParameter("iUserId", iUserId)).Tables[0].Rows;
List <Event1> events = ObjectGenerator <Event1> .GeneratListFromDataRowCollection(drc);
return(events);
}
catch (Exception ex)
{
Log.LogError("GetEventsList / TEvent_SLCT", "ex" + ex);
return(null);
}
}
public static List <MessageCust> GetMessages(int iUserId)
{
try
{
DataTable dt = SqlDataAccess.ExecuteDatasetSP("TSysMessage_SLCT", new SqlParameter("iUserId", iUserId)).Tables[0];
List <MessageCust> lMessages = new List <MessageCust>();
lMessages = ObjectGenerator <MessageCust> .GeneratListFromDataRowCollection(dt.Rows);
return(lMessages);
}
catch (Exception ex)
{
Log.ExceptionLog(ex.Message, "GetMessages");
return(null);
}
}
public static List <Orders> GetOrders()
{
try
{
DataTable dt = SqlDataAccess.ExecuteDatasetSP("TSysOrders_SLCT").Tables[0];
List <Orders> lOrders = new List <Orders>();
lOrders = ObjectGenerator <Orders> .GeneratListFromDataRowCollection(dt.Rows);
return(lOrders);
}
catch (Exception ex)
{
Log.ExceptionLog(ex.Message, "GetOrders");
return(null);
}
}
public static List <CodeTable> GetCodeTable(string tableName, string iId, string nvName)
{
try
{
string query = "select " + (iId == null ? "iId" : iId) + " as iId, " + (nvName == null ? "nvName" : nvName) + " as nvName from tbl_code_" + tableName;
query += " where iSysRowStatus=1";
DataSet ds = SqlDataAccess.ExecuteDataset(query);
DataTable dt = ds.Tables[0];
return(ObjectGenerator <CodeTable> .GeneratListFromDataRowCollection(ds.Tables[0].Rows));
}
catch (Exception ex)
{
Log.ExceptionLog(ex.Message, "GetCodeTable");
return(null);
}
}
public static List <Remark> GetRemarks(int iUserId)
{
try
{
DataTable dt = SqlDataAccess.ExecuteDatasetSP("TRemark_SLCT", new SqlParameter("iUserId", iUserId)).Tables[0];
List <Remark> lRemarks = new List <Remark>();
lRemarks = ObjectGenerator <Remark> .GeneratListFromDataRowCollection(dt.Rows);
return(lRemarks);
}
catch (Exception ex)
{
Log.ExceptionLog(ex.Message, "GetRemarks");
return(null);
}
}
public static List <Month> GetMonthes()
{
try
{
DataTable dt = SqlDataAccess.ExecuteDatasetSP("TGlobalDate_SLCT").Tables[0];
List <Month> lMonths = new List <Month>();
lMonths = ObjectGenerator <Month> .GeneratListFromDataRowCollection(dt.Rows);
return(lMonths);
}
catch (Exception ex)
{
Log.ExceptionLog(ex.Message, "GetMonthes");
return(null);
}
}
public static List <Report> GetReportscryingDetector(int iMonthYearId)
{
try
{
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("iMonthYearId", iMonthYearId));
DataTable dt = SqlDataAccess.ExecuteDatasetSP("TReportcryingDetector_SLCT", parameters).Tables[0];
List <Report> lReports = new List <Report>();
lReports = ObjectGenerator <Report> .GeneratListFromDataRowCollection(dt.Rows);
return(lReports);
}
catch (Exception ex)
{
Log.ExceptionLog(ex.Message, "GetReportscryingDetector");
return(null);
}
}
public static List <Student> GetStudentList(int iUserId)
{
try
{
DataRowCollection drc = SqlDataAccess.ExecuteDatasetSP("TStudentGetStudentByUser_SLCT", new SqlParameter("iUserId", iUserId)).Tables[0].Rows;
List <Student> students = ObjectGenerator <Student> .GeneratListFromDataRowCollection(drc);
return(students);
}
catch (Exception ex)
{
Log.LogError("GetStudentList / TStudentGetStudentByUser_SLCT", "ex" + ex);
return(null);
}
}
public static List <Link> GetLinks()
{
try
{
List <Link> links = new List <Link>();
DataSet ds = SqlDataAccess.ExecuteDatasetSP("TLink_SLCT");
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
links = ObjectGenerator <Link> .GeneratListFromDataRowCollection(ds.Tables[0].Rows);
}
return(links);
}
catch (Exception x)
{
Log.LogError("GetLinks", x.Message);
return(null);
}
}
public static List <CodeTable> GetCities()
//public static Dictionary<string, List<CodeTable>> GetCities()
{
//return CodeTable.GetListCodeTables("TSysTableRow_SLCT", null);
try
{
DataSet ds = SqlDataAccess.ExecuteDatasetSP("TCities_SLCT");
List <CodeTable> citiesList = new List <CodeTable>();
citiesList = ObjectGenerator <CodeTable> .GeneratListFromDataRowCollection(ds.Tables[0].Rows);
return(citiesList);
}
catch (Exception ex)
{
Log.ExceptionLog(ex.Message, "GetCities");
return(null);
}
}
public static List <Conversations> GetConversations(int iUserId)
{
try
{
//data table שולף טבלה
DataTable dt = SqlDataAccess.ExecuteDatasetSP("TSysConversation_SLCT", new SqlParameter("iUserId", iUserId)).Tables[0];
List <Conversations> lConversations = new List <Conversations>();
lConversations = ObjectGenerator <Conversations> .GeneratListFromDataRowCollection(dt.Rows);
//פונקציה שהופכת את הטבלה לרשימה
//lPayment = ObjectGenerator<Payment>.GeneratListFromDataRowCollection(dt.Rows);
return(lConversations);
}
catch (Exception ex)
{
Log.ExceptionLog(ex.Message, "GetConversations");
return(null);
}
}
public static List <Orders> GetOrdersByUser(int iUserId, int iUserType)
{
try
{
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("iUserId", iUserId));
parameters.Add(new SqlParameter("iUserType", iUserType));
DataTable dt = SqlDataAccess.ExecuteDatasetSP("TSysOrdersByUser_SLCT", parameters).Tables[0];
List <Orders> lOrders = new List <Orders>();
lOrders = ObjectGenerator <Orders> .GeneratListFromDataRowCollection(dt.Rows);
return(lOrders);
}
catch (Exception ex)
{
Log.ExceptionLog(ex.Message, "GetOrdersByUser");
return(null);
}
}
public static CodeTableForABCBook GetCodeTableForActivityDay(int IAbcBookId)
{
try
{
DataSet ds = SqlDataAccess.ExecuteDatasetSP("TActivityDay_CodeTables_SLCT", new List <SqlParameter>()
{
new SqlParameter("IAbcBookId", IAbcBookId)
});
CodeTableForABCBook codeTableForStatisticsGoals = new CodeTableForABCBook();
codeTableForStatisticsGoals.dCodeTable = new Dictionary <string, List <CodeTable> >();
for (int i = 0; i < 8; i++)
{
codeTableForStatisticsGoals.dCodeTable.Add
(ds.Tables[i++].Rows[0][0].ToString(), ObjectGenerator <CodeTable> .GeneratListFromDataRowCollection(ds.Tables[i].Rows));
}
return(codeTableForStatisticsGoals);
}
catch (Exception ex)
{
Log.ExceptionLog(ex.Message, "GetCodeTableForStatisticsGoals , IAbcBookId:" + IAbcBookId.ToString());
return(null);
}
}
public static int createDocumentToProviders(int iUserId, DateTime?dtBeginDate, DateTime?dtEndDate)
{
try
{
FileManageCtrl.DeleteAllFile(AppDomain.CurrentDomain.BaseDirectory + "Files\\reports");
List <UserBasic> lUser = new List <UserBasic>();
lUser = UserBasic.GetUsersBasic(3);
foreach (var user in lUser)
{
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("iUserId", user.iUserId));
parameters.Add(new SqlParameter("dtBeginDate", dtBeginDate));
parameters.Add(new SqlParameter("dtEndDate", dtEndDate));
//data table שולף טבלה
DataTable dt = SqlDataAccess.ExecuteDatasetSP("TSumMessage_SLCT", parameters).Tables[0];
List <MessageToProvider> lToProvider = new List <MessageToProvider>();
lToProvider = ObjectGenerator <MessageToProvider> .GeneratListFromDataRowCollection(dt.Rows);
//data table שולף טבלה
DataTable dt2 = SqlDataAccess.ExecuteDatasetSP("TOrdersByStatus_SLCT", new SqlParameter("iUserId", user.iUserId)).Tables[0];
List <Orders> lOrders = new List <Orders>();
lOrders = ObjectGenerator <Orders> .GeneratListFromDataRowCollection(dt2.Rows);
string sFileName = "פירוט שעות";
string sFileName2 = "רשימת הזמנות";
string path = AppDomain.CurrentDomain.BaseDirectory + "Files\\" + "reports\\" + sFileName + "_" + DateTime.Now.ToFileTime().ToString() + ".xlsx";
string path2 = AppDomain.CurrentDomain.BaseDirectory + "Files\\" + "reports\\" + sFileName2 + "_" + DateTime.Now.ToFileTime().ToString() + ".xlsx";
string[] str = { "זמן תרגום", "סהכ לתשלום", "תשלום שעה ראשונה", "תשלום שעה שניה" };
string[] str2 = { "שם לקוח", "שם מתורגמן", "סוג הזמנה", "סוג תרגום", "תאריך תרגום", "זמן תרגום" };
Messages message = new Messages();
message.nvFrom = System.Configuration.ConfigurationManager.AppSettings["mailFrom"];
message.nvTo = user.nvEmail;
message.nvSubject = "דוח סיכום חודשי";
string dtBeginDateString = dtBeginDate != null?dtBeginDate.Value.ToString("dd-MM-yyyy") : "n/a";
string dtEndDateString = dtEndDate != null?dtEndDate.Value.ToString("dd-MM-yyyy") : "n/a";
if (lToProvider != null && lToProvider.Count != 0 && lOrders != null && lOrders.Count != 0)
{
message.nvMessage = " שלום. מצ\"ב פירוט השעות שבצעת מתאריך" + " " + dtBeginDateString + " " + "עד תאריך " + dtEndDateString + " " + "ובנוסף רשימת הזמנות שממתינות לאישור תשלום - לטיפולך";
}
else
if (lToProvider != null && lToProvider.Count != 0)
{
message.nvMessage = " שלום. מצ\"ב פירוט השעות שבצעת מתאריך" + " " + dtBeginDateString + " " + "עד תאריך " + dtEndDateString;
}
else
if (lOrders != null && lOrders.Count != 0)
{
message.nvMessage = "שלום. מצ\"ב רשימת הזמנות שממתינות לאישור תשלום - לטיפולך";
}
ExcelHendler.ExportToExcel(dt, "aaa", str, path);
ExcelHendler.ExportToExcel(dt2, "aaa", str2, path2);
List <Attachment> lAttach = new List <Attachment>();
if (lToProvider != null && lToProvider.Count != 0)
{
lAttach.Add(new Attachment(path));
}
if (lOrders != null && lOrders.Count != 0)
{
lAttach.Add(new Attachment(path2));
}
bool isSuccess = Messages.SendEmailToOne(message, lAttach, false);
}
return(1);
}
catch (Exception ex)
{
Log.ExceptionLog(ex.Message, "createDocumentToProviders");
return(2);
}
}