public string CreateUserReport(string username, int userid, DateTime dt_start, DateTime dt_end)
{
// string clienttableheader = global::Schiduch.Properties.Resources.ClientTableHeader;
string html = "";
SqlParameter[] prms = new SqlParameter[2];
prms[0] = new SqlParameter("dt_start", dt_start);
prms[1] = new SqlParameter("dt_end", dt_end);
string moreinfo = "<b>שדכן:</b> " + username + ", <b>מזהה שדכן:</b> " + userid.ToString() + "</br>";
string sqlLog = "select firstname + ' ' + lastname as allname, peoples.ID ,action,userid,date " +
" from log JOIN peoples ON log.info not like '' and SUBSTRING(log.info, CHARINDEX('^',log.Info)+1,DATALENGTH(log.info) - 1)= Peoples.ID" +
" where UserId = " + userid + " and action = 2 ";
if (dt_start != null && dt_end != null)
{
sqlLog += " and date between @dt_start and @dt_end";
moreinfo += " מתאריך " + dt_start.ToShortDateString() + " עד לתאריך " + dt_end.ToShortDateString();
}
html += CreateHtmlReport("שדכן", moreinfo);
html += RegisterDateToReport(userid);
SqlDataReader reader = DBFunction.ExecuteReader(sqlLog, prms);
html += "<u><h2>פירוט</h2></u><div class='row' style='width:80%'>";
while (reader.Read())
{
Log.ActionType a_type = (Log.ActionType) int.Parse(reader["ACTION"].ToString());
switch (a_type)
{
case Log.ActionType.ClientOpen:
clientlog_open += START_TABLE_ROW + CreateCol("", reader["allname"]) + CreateCol(null, reader["date"]) + END_TABLE_ROW;
InsertUserAction(reader["allname"].ToString(), (int)reader["ID"], ShiduchActivity.ActionType.openForms, ShiduchActivity.ActionStatus.completed);
count_openclient++;
break;
}
}
reader.Close();
reader = ShiduchActivity.GetActivities(false, null, false, false, true, dt_start, dt_end, 0, userid);
while (reader.Read())
{
ShiduchActivity.ActionType action = (ShiduchActivity.ActionType) int.Parse(reader["Action"].ToString());
switch (action)
{
case ShiduchActivity.ActionType.proposal:
userAction_proposal += START_TABLE_ROW + CreateColAction(ref reader) + END_TABLE_ROW;
InsertUserAction(reader["FullNameA"].ToString(), (int)reader["PeopleId"], ShiduchActivity.ActionType.proposal, (ShiduchActivity.ActionStatus)(int) reader["Status"]);
count_clientProposal++;
break;
case ShiduchActivity.ActionType.date:
userAction_date += START_TABLE_ROW + CreateColAction(ref reader) + END_TABLE_ROW;
InsertUserAction(reader["FullNameA"].ToString(), (int)reader["PeopleId"], ShiduchActivity.ActionType.date, (ShiduchActivity.ActionStatus)(int) reader["Status"]);
count_clientDate++;
break;
case ShiduchActivity.ActionType.details:
userAction_details += START_TABLE_ROW + CreateColAction(ref reader) + END_TABLE_ROW;
InsertUserAction(reader["FullNameA"].ToString(), (int)reader["PeopleId"], ShiduchActivity.ActionType.details, (ShiduchActivity.ActionStatus)(int) reader["Status"]);
count_clientDetails++;
break;
case ShiduchActivity.ActionType.other:
userAction_other += START_TABLE_ROW + CreateColAction(ref reader) + END_TABLE_ROW;
InsertUserAction(reader["FullNameA"].ToString(), (int)reader["PeopleId"], ShiduchActivity.ActionType.other, (ShiduchActivity.ActionStatus)(int) reader["Status"]);
count_clientOther++;
break;
}
}
reader.Close();
clientlog_open += "</tbody></table></div>";
userAction_proposal += "</tbody></table></div>";
userAction_date += "</tbody></table></div>";
userAction_details += "</tbody></table></div>";
userAction_other += "</tbody></table></div>";
html += clientlog_open + userAction_proposal + userAction_date + userAction_details + userAction_other;
html += "</div><hr>"; // end div of all info tables and create hr
html += Schiduch.Properties.Resources.ClientSumAction.Replace("שדכן", "לקוח");
html += SumClientsTableList();
html += "</tbody></table><hr><u><h2>סך הכל</h2></u>";
html += SumClientsData();
html += global::Schiduch.Properties.Resources.ReportEnd;
using (TextWriter txtwrite = File.CreateText(path))
{
txtwrite.Write(html);
}
return(path);
}
public string CreateDatesReport(DateTime dt_start, DateTime dt_end)
{
string html = "";
int dates = 0;
int dates_unpaid = 0;
SqlParameter[] prms = new SqlParameter[2];
prms[0] = new SqlParameter("dt_start", dt_start);
prms[1] = new SqlParameter("dt_end", dt_end);
string moreinfo = "";
bool paid = false;
string nopay = "<span class='label label-danger'> לא שילם </span>";
string lbl = "";
string sql = "select date,info,action,userid,name,replace(left(info,CHARINDEX(N'^',info,0)),'^','') as clientname" +
" from Log left join users on userid = users.id left" +
" join RegisterInfo on relatedid = replace(substring(info, CHARINDEX('^', info), 6), '^', '')" +
" where (action = 13 or action = 10) and replace(substring(info, CHARINDEX('^', info), 6), '^', '') > 2750 ";
if (dt_start != null && dt_end != null)
{
sql += " and date between @dt_start and @dt_end";
moreinfo = " מתאריך " + dt_start.ToShortDateString() + " עד לתאריך " + dt_end.ToShortDateString();
}
sql += " order by action,date desc";
html += CreateHtmlReport("פגישות", moreinfo);
html += Properties.Resources.ShadchanStartDatesHeader;
SqlDataReader reader = DBFunction.ExecuteReader(sql, prms);
while (reader.Read())
{
Log.ActionType a_type = (Log.ActionType) int.Parse(reader["ACTION"].ToString());
paid = reader["paid"] as bool? ?? false;
if (!paid)
{
lbl = nopay; dates_unpaid++;
}
else
{
lbl = "";
}
//switch (a_type)
//{
// case Log.ActionType.GoodDateCall:
// html += START_TABLE_ROW + "<td class='success'>" + reader["clientname"] + " " + lbl + "</td>" +
// CreateCol("class='success'", reader["name"])
// + CreateCol("class='success'", reader["date"]) + END_TABLE_ROW;
// dates++;
// break;
// case Log.ActionType.StartDate:
// html += START_TABLE_ROW + "<td class='active'>" + reader["clientname"] + " " + lbl + "</td>" +
// CreateCol("class='active'", reader["name"])
// + CreateCol("class='active'", reader["date"]) + END_TABLE_ROW;
// dates++;
// break;
//}
}
reader.Close();
html += Schiduch.Properties.Resources.ShadchanStartDatesFooter;
html += "<h1>הפגישות שנעשו בזמן הזה : <label class='label label-default'>" + dates.ToString() + "</label></h1>";
html += "<h1>פגישות לכאלה שלא שילמו : <label class='label label-default'>" + dates_unpaid.ToString() + "</label></h1>";
html += "</div></div></body></html>";
using (TextWriter txtwrite = File.CreateText(path))
{
txtwrite.Write(html);
}
return(path);
}
public string CreateClientReport(string clientname, int userid, DateTime dt_start, DateTime dt_end)
{
string clienttableheader = global::Schiduch.Properties.Resources.ClientTableHeader;
string html = "";
SqlParameter[] prms = new SqlParameter[2];
prms[0] = new SqlParameter("dt_start", dt_start);
prms[1] = new SqlParameter("dt_end", dt_end);
string moreinfo = "<b>לקוח:</b> " + clientname + ", <b>מזהה לקוח:</b> " + userid.ToString() + "</br>";
string sqlLog = "select name,users.id as xid,info,action,userid,date,level from log LEFT JOIN USERS ON users.id=log.userid where log.info like '%" + userid.ToString() + "'";
if (dt_start != null && dt_end != null)
{
sqlLog += " and date between @dt_start and @dt_end";
moreinfo += " מתאריך " + dt_start.ToShortDateString() + " עד לתאריך " + dt_end.ToShortDateString();
}
sqlLog += " order BY log.ACTION ";
html += CreateHtmlReport("לקוח", moreinfo);
html += RegisterDateToReport(userid);
SqlDataReader reader = DBFunction.ExecuteReader(sqlLog, prms);
html += "<u><h2>פירוט</h2></u><div class='row' style='width:80%'>";
while (reader.Read())
{
Log.ActionType a_type = (Log.ActionType) int.Parse(reader["ACTION"].ToString());
switch (a_type)
{
case Log.ActionType.ClientOpen:
clientlog_open += START_TABLE_ROW + CreateCol("", reader["name"]) + CreateCol(null, reader["date"]) + END_TABLE_ROW;
InsertUserAction(reader["name"].ToString(), (int)reader["userid"], ShiduchActivity.ActionType.openForms, ShiduchActivity.ActionStatus.completed);
count_openclient++;
break;
}
}
reader.Close();
reader = ShiduchActivity.GetActivities(false, null, false, false, true, dt_start, dt_end, userid);
while (reader.Read())
{
ShiduchActivity.ActionType action = (ShiduchActivity.ActionType) int.Parse(reader["Action"].ToString());
switch (action)
{
case ShiduchActivity.ActionType.proposal:
clientAction_proposal += START_TABLE_ROW + CreateColAction(ref reader) + END_TABLE_ROW;
InsertUserAction(reader["Name"].ToString(), (int)reader["userID"], ShiduchActivity.ActionType.proposal, (ShiduchActivity.ActionStatus)(int) reader["Status"]);
count_clientProposal++;
break;
case ShiduchActivity.ActionType.date:
clientAction_date += START_TABLE_ROW + CreateColAction(ref reader) + END_TABLE_ROW;
InsertUserAction(reader["Name"].ToString(), (int)reader["userID"], ShiduchActivity.ActionType.date, (ShiduchActivity.ActionStatus)(int) reader["Status"]);
count_clientDate++;
break;
case ShiduchActivity.ActionType.details:
clientAction_details += START_TABLE_ROW + CreateColAction(ref reader) + END_TABLE_ROW;
InsertUserAction(reader["Name"].ToString(), (int)reader["userID"], ShiduchActivity.ActionType.details, (ShiduchActivity.ActionStatus)(int) reader["Status"]);
count_clientDetails++;
break;
case ShiduchActivity.ActionType.other:
clientAction_other += START_TABLE_ROW + CreateColAction(ref reader) + END_TABLE_ROW;
InsertUserAction(reader["Name"].ToString(), (int)reader["userID"], ShiduchActivity.ActionType.other, (ShiduchActivity.ActionStatus)(int) reader["Status"]);
count_clientOther++;
break;
}
}
reader.Close();
clientlog_open += "</tbody></table></div>";
clientAction_proposal += "</tbody></table></div>";
clientAction_date += "</tbody></table></div>";
clientAction_details += "</tbody></table></div>";
clientAction_other += "</tbody></table></div>";
html += clientlog_open + clientAction_proposal + clientAction_date + clientAction_details + clientAction_other;
html += "</div><hr>"; // end div of all info tables and create hr
html += Schiduch.Properties.Resources.ClientSumAction;
html += SumClientsTableList();
html += "</tbody></table><hr><u><h2>סך הכל</h2></u>";
html += SumClientsData();
html += global::Schiduch.Properties.Resources.ReportEnd;
using (TextWriter txtwrite = File.CreateText(path))
{
txtwrite.Write(html);
}
return(path);
}