public async Task <IHttpActionResult> GetWeb_AccessUserReport(string userName, string password, string ace, string group, string user)
{
if (UnitDatabase.CreateConection(userName, password, "Config", "", ""))
{
if (!string.IsNullOrEmpty(ace) || !string.IsNullOrEmpty(group) || !string.IsNullOrEmpty(user))
{
string sql = string.Format(@"declare @ace nvarchar(5), @group int , @username nvarchar(20)
set @ace = '{0}'
set @group = {1}
set @username = '******'
select 'TrzIKala'as Code , [dbo].[Web_RprtTrs](@group,@ace,@username,'TrzIKala') as Trs
union all
select 'TrzIKalaExf'as Code , [dbo].[Web_RprtTrs](@group,@ace,@username,'TrzIKalaExf') as Trs
union all
select 'IDocR'as Code , [dbo].[Web_RprtTrs](@group,@ace,@username,'IDocR') as Trs
union all
select 'FDocR'as Code , [dbo].[Web_RprtTrs](@group,@ace,@username,'FDocR') as Trs
union all
select 'TrzAcc' as Code, [dbo].[Web_RprtTrs](@group, @ace, @username, 'TrzAcc') as Trs"
, ace, group, user);
var listDB = UnitDatabase.db.Database.SqlQuery <AccessUserReport>(sql).ToList();
return(Ok(listDB));
}
}
return(null);
}
public async Task <IHttpActionResult> PostWeb_UpdatePriceAnbar(string ace, string sal, string group, long serialnumber)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
if (UnitDatabase.CreateConection(ace, sal, group))
{
try
{
string sql = string.Format(CultureInfo.InvariantCulture,
@"DECLARE @return_value int
EXEC @return_value = [dbo].[Web_IDocB_SetKalaPrice]
@SerialNumber = {0}
SELECT 'Return Value' = @return_value",
serialnumber);
int value = UnitDatabase.db.Database.SqlQuery <int>(sql).Single();
if (value == 0)
{
await UnitDatabase.db.SaveChangesAsync();
}
}
catch (Exception)
{
throw;
}
}
string sql1 = string.Format(@"SELECT SerialNumber,BandNo,KalaCode,KalaName,MainUnit,MainUnitName,Amount1,Amount2,Amount3,UnitPrice,TotalPrice,Comm,Up_Flag,DeghatR1,DeghatR2,DeghatR3,DeghatM1,DeghatM2,DeghatM3,DeghatR
FROM Web_IDocB WHERE SerialNumber = {0}", serialnumber);
var listIDocB = UnitDatabase.db.Database.SqlQuery <Web_IDocB>(sql1);
return(Ok(listIDocB));
}
public async Task <IHttpActionResult> PostWeb_TrzIKala(string ace, string sal, string group, TrzIObject TrzIObject)
{
if (UnitDatabase.CreateConection(ace, sal, group))
{
string sql = string.Format(CultureInfo.InvariantCulture,
@"select * FROM dbo.Web_TrzIKala('{0}', '{1}') AS TrzI where 1 = 1 ",
TrzIObject.azTarikh, TrzIObject.taTarikh);
if (TrzIObject.InvCode != "0")
{
sql += string.Format(" and InvCode = '{0}' ", TrzIObject.InvCode);
}
if (TrzIObject.KGruCode != "0")
{
sql += string.Format(" and KGruCode = '{0}' ", TrzIObject.KGruCode);
}
if (TrzIObject.KalaCode != "0")
{
sql += string.Format("and KalaCode = '{0}' ", TrzIObject.KalaCode);
}
var listTrzI = UnitDatabase.db.Database.SqlQuery <Web_TrzIKala>(sql);
return(Ok(listTrzI));
}
return(null);
}
public async Task <IHttpActionResult> PostWeb_IDocR(string ace, string sal, string group, IDocRObject IDocRObject)
{
var dataAccount = UnitDatabase.ReadUserPassHeader(this.Request.Headers);
string con = UnitDatabase.CreateConection(dataAccount[0], dataAccount[1], dataAccount[2], ace, sal, group, 0, "24", 9, 0);
if (con == "ok")
{
string sql = string.Format(CultureInfo.InvariantCulture,
@"select top (10000) * FROM dbo.Web_IDocR('{0}', '{1}','{2}') AS IDocR where 1 = 1 ",
IDocRObject.azTarikh, IDocRObject.taTarikh, dataAccount[2]);
sql += UnitPublic.SpiltCodeAnd("InvCode", IDocRObject.InvCode);
sql += UnitPublic.SpiltCodeAnd("KGruCode", IDocRObject.KGruCode);
sql += UnitPublic.SpiltCodeAnd("KalaCode", IDocRObject.KalaCode);
sql += UnitPublic.SpiltCodeAnd("ThvlCode", IDocRObject.ThvlCode);
sql += UnitPublic.SpiltCodeAnd("OprCode", IDocRObject.OprCode);
sql += UnitPublic.SpiltCodeAnd("MkzCode", IDocRObject.MkzCode);
sql += UnitPublic.SpiltCodeAnd("Status", IDocRObject.StatusCode);
sql += UnitPublic.SpiltCodeAnd("ModeCode", IDocRObject.ModeCode);
if (IDocRObject.DocNo > 0)
{
sql += " and DocNo = " + IDocRObject.DocNo;
}
sql += " order by DocNo ";
var listIDocR = UnitDatabase.db.Database.SqlQuery <Web_IDocR>(sql);
return(Ok(listIDocR));
}
return(Ok(con));
}
public async Task <IHttpActionResult> PostWeb_Krdx(string ace, string sal, string group, KrdxObject KrdxObject)
{
var dataAccount = UnitDatabase.ReadUserPassHeader(this.Request.Headers);
string con = UnitDatabase.CreateConection(dataAccount[0], dataAccount[1], dataAccount[2], ace, sal, group, 0, "21", 9, 0);
if (con == "ok")
{
string invCode = UnitPublic.SpiltCodeCama(KrdxObject.InvCode);
string sql = string.Format(CultureInfo.InvariantCulture,
@"select * from (select top (10000) * FROM dbo.Web_Krdx('{0}', '{1}',{2},'{3}','{4}','{5}') AS Krdx where 1 = 1 ",
KrdxObject.azTarikh,
KrdxObject.taTarikh,
KrdxObject.Naghl,
KrdxObject.KalaCode,
invCode,
dataAccount[2]);
sql += UnitPublic.SpiltCodeAnd("KGruCode", KrdxObject.KGruCode);
sql += UnitPublic.SpiltCodeAnd("ThvlCode", KrdxObject.ThvlCode);
sql += UnitPublic.SpiltCodeAnd("OprCode", KrdxObject.OprCode);
sql += UnitPublic.SpiltCodeAnd("MkzCode", KrdxObject.MkzCode);
sql += UnitPublic.SpiltCodeAnd("Status", KrdxObject.StatusCode);
sql += UnitPublic.SpiltCodeAnd("ModeCode", KrdxObject.ModeCode);
sql += "or BodyTag = 0 order by BodyTag,DocNo ";
sql += " ) as a where VAmount1 is not null";
var listKrdx = UnitDatabase.db.Database.SqlQuery <Web_Krdx>(sql);
return(Ok(listKrdx));
}
return(Ok(con));
}
public async Task <IHttpActionResult> PostWeb_FDocR(string ace, string sal, string group, FDocRObject FDocRObject)
{
var dataAccount = UnitDatabase.ReadUserPassHeader(this.Request.Headers);
string con = UnitDatabase.CreateConection(dataAccount[0], dataAccount[1], dataAccount[2], ace, sal, group, 0, "18", 9, 0);
if (con == "ok")
{
string sql = string.Format(CultureInfo.InvariantCulture,
@"select top(10000) * FROM dbo.Web_FDocR('{0}', '{1}','{2}') AS FDocR where 1 = 1 ",
FDocRObject.ModeCode1, FDocRObject.ModeCode2, dataAccount[2]);
sql += UnitPublic.SpiltCodeAnd("InvCode", FDocRObject.InvCode);
sql += UnitPublic.SpiltCodeAnd("KGruCode", FDocRObject.KGruCode);
sql += UnitPublic.SpiltCodeAnd("KalaCode", FDocRObject.KalaCode);
sql += UnitPublic.SpiltCodeAnd("CustCode", FDocRObject.CustCode);
sql += UnitPublic.SpiltCodeAnd("OprCode", FDocRObject.OprCode);
sql += UnitPublic.SpiltCodeAnd("MkzCode", FDocRObject.MkzCode);
if (FDocRObject.azTarikh != "")
{
sql += string.Format(" and DocDate >= '{0}' ", FDocRObject.azTarikh);
}
if (FDocRObject.taTarikh != "")
{
sql += string.Format(" and DocDate <= '{0}' ", FDocRObject.taTarikh);
}
var listFDocR = UnitDatabase.db.Database.SqlQuery <Web_FDocR>(sql);
return(Ok(listFDocR));
}
return(Ok(con));
}
public async Task <IHttpActionResult> GetWeb_Login(string userName, string password, string user, string pass, string param1, string param2)
{
if (UnitDatabase.CreateConection(userName, password, "Config", "", ""))
{
if (pass == "null")
{
pass = "";
}
string sql = string.Format(@" DECLARE @return_value int
EXEC @return_value = [dbo].[Web_Login]
@Code1 = '{0}',
@UserCode = N'{1}',
@Code2 = '{2}',
@Psw = N'{3}'
SELECT 'Return Value' = @return_value",
param1, user, param2, pass);
int value = UnitDatabase.db.Database.SqlQuery <int>(sql).Single();
if (value == 1)
{
return(Ok(1));
}
else
{
return(Ok(0));
}
}
return(null);
}
public async Task <IHttpActionResult> DeleteAFI_IDocHi(string ace, string sal, string group, long SerialNumber, string ModeCode)
{
var dataAccount = UnitDatabase.ReadUserPassHeader(this.Request.Headers);
string con = UnitDatabase.CreateConection(dataAccount[0], dataAccount[1], dataAccount[2], ace, sal, group, SerialNumber, ModeCode == "1" ? "IIDoc" : "IODoc", 3, 0);
if (con == "ok")
{
try
{
string sql = string.Format(@"DECLARE @return_value int
EXEC @return_value = [dbo].[Web_SaveIDoc_Del]
@SerialNumber = {0}
SELECT 'Return Value' = @return_value"
, SerialNumber);
int value = UnitDatabase.db.Database.SqlQuery <int>(sql).Single();
if (value > 0)
{
await UnitDatabase.db.SaveChangesAsync();
}
}
catch (Exception e)
{
throw;
}
UnitDatabase.SaveLog(dataAccount[0], dataAccount[1], dataAccount[2], ace, sal, group, SerialNumber, ModeCode == "1" ? "IIDoc" : "IODoc", 3, "Y", 0);
return(Ok(1));
}
return(Ok(con));
}
public async Task <IHttpActionResult> PostWeb_TestMoveFactor(string ace, string sal, string group, long serialNumber, string ModeCode)
{
var dataAccount = UnitDatabase.ReadUserPassHeader(this.Request.Headers);
string con = UnitDatabase.CreateConection(dataAccount[0], dataAccount[1], dataAccount[2], ace, sal, group, serialNumber, "", 0, 0);
if (con == "ok")
{
string sql = string.Format(CultureInfo.InvariantCulture,
@"DECLARE @retval nvarchar(250)
EXEC [dbo].[Web_TestFDoc_Move]
@serialNumber = {0},
@MoveToModeCode = '{1}',
@retval = @retval OUTPUT
SELECT @retval as N'@retval'" ,
serialNumber,
ModeCode);
try
{
var result = UnitDatabase.db.Database.SqlQuery <string>(sql).ToList();
return(Ok(result));
// return Ok("");
}
catch (Exception e)
{
throw;
}
}
return(Ok(con));
}
public async Task <IHttpActionResult> PostWeb_TrzIKalaExf(string userName, string password, string ace, string sal, string group, TrzIExfObject TrzIExfObject)
{
if (UnitDatabase.CreateConection(userName, password, ace, sal, group))
{
string invCode = UnitPublic.SpiltCodeCama(TrzIExfObject.InvCode);
string sql = string.Format(CultureInfo.InvariantCulture,
@"select * FROM dbo.Web_TrzIKalaExf('{0}', '{1}','{2}') AS TrzIExf where 1 = 1 ",
TrzIExfObject.azTarikh, TrzIExfObject.taTarikh, invCode);
if (TrzIExfObject.KGruCode != "0")
{
sql += string.Format(" and KGruCode = '{0}' ", TrzIExfObject.KGruCode);
}
// sql += UnitPublic.SpiltCodeAnd("InvCode", TrzIExfObject.InvCode);
sql += UnitPublic.SpiltCodeAnd("KalaCode", TrzIExfObject.KalaCode);
sql += " order by KalaCode,KalaFileNo,KalaState,KalaExf1,KalaExf2,KalaExf3,KalaExf4,KalaExf5,KalaExf6,KalaExf7,KalaExf8,KalaExf9,KalaExf10,KalaExf11,KalaExf12,KalaExf13,KalaExf14,KalaExf15,InvCode,Tag ";
var listTrzIExf = UnitDatabase.db.Database.SqlQuery <Web_TrzIKalaExf>(sql);
return(Ok(listTrzIExf));
}
return(null);
}
public async Task <IHttpActionResult> DeleteAFI_FDocHi(string ace, string sal, string group, long SerialNumber, string ModeCode)
{
if (UnitDatabase.CreateConection(ace, sal, group))
{
try
{
string sql = string.Format(@"DECLARE @return_value int
EXEC @return_value = [dbo].[Web_SaveFDoc_Del]
@SerialNumber = {0}
SELECT 'Return Value' = @return_value"
, SerialNumber);
int value = UnitDatabase.db.Database.SqlQuery <int>(sql).Single();
if (value > 0)
{
await UnitDatabase.db.SaveChangesAsync();
}
}
catch (Exception e)
{
throw;
}
}
/*
* string sql1 = string.Format(@" select top(100)
* SerialNumber,
* DocNo,
* DocDate,
* CustCode,
* CustName,
* Spec,
* KalaPriceCode,
* InvCode,
* AddMinPrice1,
* AddMinPrice2,
* AddMinPrice3,
* AddMinPrice4,
* AddMinPrice5,
* AddMinPrice6,
* AddMinPrice7,
* AddMinPrice8,
* AddMinPrice9,
* AddMinPrice10,
* ModeCode,
* Status,
* PaymentType,
* Footer,
* Tanzim,
* Taeed,
* FinalPrice,
* Eghdam
* from Web_FDocH where ModeCode = {0} ",
* ModeCode.ToString());
* sql1 += " order by DocNo desc ";
*
* var listFDocH = UnitDatabase.db.Database.SqlQuery<Web_FDocHMini>(sql1); */
return(Ok(1));
}
public IQueryable <Web_Payment> GetWeb_Payment(string ace, string sal, string group)
{
if (UnitDatabase.CreateConection(ace, sal, group))
{
return(UnitDatabase.db.Web_Payment.OrderBy(c => c.OrderFld));
}
return(null);
}
public IQueryable <Web_Inv> GetWeb_Inv(string ace, string sal, string group)
{
if (UnitDatabase.CreateConection(ace, sal, group))
{
return(UnitDatabase.db.Web_Inv);
}
return(null);
}
public IQueryable <Web_Kala> GetWeb_Kala(string userName, string password, string ace, string sal, string group)
{
if (UnitDatabase.CreateConection(userName, password, ace, sal, group))
{
return(UnitDatabase.db.Web_Kala);
}
return(null);
}
public IQueryable <Web_Status> GetWeb_Status(string userName, string password, string ace, string sal, string group)
{
if (UnitDatabase.CreateConection(userName, password, ace, sal, group))
{
return(UnitDatabase.db.Web_Status.OrderBy(c => c.OrderFld));
}
return(null);
}
public async Task <IHttpActionResult> GetAllWeb_FDocHMin(string ace, string sal, string group, int ModeCode, int select, string userName, bool accessSanad)
{
if (UnitDatabase.CreateConection(ace, sal, group))
{
string sql = "select ";
if (select == 0)
{
sql += " top(100) ";
}
sql += string.Format(@" SerialNumber,
DocNo,
SortDocNo,
DocDate,
CustCode,
CustName,
Spec,
KalaPriceCode,
InvCode,
AddMinSpec1,
AddMinSpec2,
AddMinSpec3,
AddMinSpec4,
AddMinSpec5,
AddMinSpec6,
AddMinSpec7,
AddMinSpec8,
AddMinSpec9,
AddMinSpec10,
AddMinPrice1,
AddMinPrice2,
AddMinPrice3,
AddMinPrice4,
AddMinPrice5,
AddMinPrice6,
AddMinPrice7,
AddMinPrice8,
AddMinPrice9,
AddMinPrice10,
ModeCode,
Status,
PaymentType,
Footer,
Tanzim,
Taeed,
FinalPrice,
Eghdam
from Web_FDocH where ModeCode = {0} ",
ModeCode.ToString());
if (accessSanad == false)
{
sql += " and Eghdam = '" + userName + "' ";
}
sql += " order by SortDocNo desc ";
var listFDocH = UnitDatabase.db.Database.SqlQuery <Web_FDocHMini>(sql);
return(Ok(listFDocH));
}
return(null);
}
public async Task <IQueryable <Web_FDocH> > GetWeb_FDocH(string ace, string sal, string group, long serialNumber, int ModeCode)
{
if (UnitDatabase.CreateConection(ace, sal, group))
{
var a = UnitDatabase.db.Web_FDocH.Where(c => c.SerialNumber == serialNumber && c.ModeCode == ModeCode);
return(a);
}
return(null);
}
public IQueryable <Web_Unit> GetWeb_Unit(string userName, string password, string ace, string sal, string group, string codeKala)
{
if (UnitDatabase.CreateConection(userName, password, ace, sal, group))
{
var a = from p in UnitDatabase.db.Web_Unit where p.KalaCode == codeKala && p.Name != "" select p;
return(a);
}
return(null);
}
public IQueryable <Web_KalaPriceB> GetWeb_KalaPriceB(string userName, string password, string ace, string sal, string group, int code, string kalacode)
{
if (UnitDatabase.CreateConection(userName, password, ace, sal, group))
{
var list = UnitDatabase.db.Web_KalaPriceB.Where(c => c.Code == code && c.KalaCode == kalacode);
return(list);
}
return(null);
}
public async Task <IHttpActionResult> DeleteAFI_FDocBi(string ace, string sal, string group, long SerialNumber, int BandNo, string ModeCode, string FlagLog)
{
var dataAccount = UnitDatabase.ReadUserPassHeader(this.Request.Headers);
string con = UnitDatabase.CreateConection(dataAccount[0], dataAccount[1], dataAccount[2], ace, sal, group, SerialNumber, ModeCode, 6, BandNo);
if (con == "ok")
{
try
{
// Fct5: 'Fct5DocB' 'Radif'
// Inv5: 'Inv5DocB' 'Radif'
// Afi1: 'Afi1FDocB' 'BandNo'
//var list = UnitDatabase.db.AFI_FDocBi.First(c=>c.SerialNumber == SerialNumber && c.BandNo==BandNo);
//if (list == null)
//{
// return NotFound();
//}
string sql = string.Format(@"DECLARE @return_value int
EXEC @return_value = [dbo].[Web_SaveFDoc_BD]
@SerialNumber = {0},
@BandNo = {1}
SELECT 'Return Value' = @return_value",
SerialNumber,
BandNo);
int value = UnitDatabase.db.Database.SqlQuery <int>(sql).Single();
if (value == 0)
{
await UnitDatabase.db.SaveChangesAsync();
}
string sqlUpdateBand = string.Format(@"DECLARE @return_value int
EXEC @return_value = [dbo].[Web_Doc_BOrder]
@TableName = '{0}',
@SerialNumber = {1},
@BandNoFld = '{2}'
SELECT 'Return Value' = @return_value" ,
ace == "Web1" ? "Afi1FDocB" : "Fct5DocB",
SerialNumber,
ace == "Web1" ? "BandNo" : "Radif");
int valueUpdateBand = UnitDatabase.db.Database.SqlQuery <int>(sqlUpdateBand).Single();
//await UnitDatabase.db.SaveChangesAsync();
}
catch (Exception e)
{
throw;
}
string sql1 = string.Format(@"SELECT SerialNumber,BandNo,KalaCode,KalaName,MainUnit,MainUnitName,Amount1,Amount2,Amount3,UnitPrice,TotalPrice,Discount,Comm,Up_Flag,KalaDeghatR1,KalaDeghatR2,KalaDeghatR3,KalaDeghatM1,KalaDeghatM2,KalaDeghatM3,DeghatR
FROM Web_FDocB WHERE SerialNumber = {0}", SerialNumber.ToString());
var listFactor = UnitDatabase.db.Database.SqlQuery <Web_FDocB>(sql1);
UnitDatabase.SaveLog(dataAccount[0], dataAccount[1], dataAccount[2], ace, sal, group, SerialNumber, ModeCode, 1, FlagLog, 0);
return(Ok(listFactor));
}
return(Ok(con));
}
public IQueryable <Web_Thvl> GetWeb_Thvl(string userName, string password, string ace, string sal, string group)
{
if (UnitDatabase.CreateConection(userName, password, ace, sal, group))
{
var aa = UnitDatabase.db.Web_Thvl;
return(aa);
}
return(null);
}
public IQueryable <Web_IDocH> GetWeb_IDocH(string userName, string password, string ace, string sal, string group, long serialNumber)
{
if (UnitDatabase.CreateConection(userName, password, ace, sal, group))
{
var a = UnitDatabase.db.Web_IDocH.Where(c => c.SerialNumber == serialNumber);
return(a);//UnitDatabase.db.Web_IDocH.Where(c => c.SerialNumber == serialNumber);
}
return(null);
}
public async Task <IHttpActionResult> PutAFI_FDocBi(string userName, string password, string ace, string sal, string group, long BandNo, AFI_FDocBi aFI_FDocBi)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
if (UnitDatabase.CreateConection(userName, password, ace, sal, group))
{
try
{
string sql = string.Format(CultureInfo.InvariantCulture,
@"DECLARE @return_value int
EXEC @return_value = [dbo].[Web_SaveFDoc_BU]
@SerialNumber = {0},
@BandNo = {1},
@KalaCode = N'{2}',
@Amount1 = {3},
@Amount2 = {4},
@Amount3 = {5},
@UnitPrice = {6},
@TotalPrice = {7},
@Discount = {8},
@MainUnit = {9},
@Comm = N'{10}',
@Up_Flag = {11}
SELECT 'Return Value' = @return_value
",
aFI_FDocBi.SerialNumber,
aFI_FDocBi.BandNo,
aFI_FDocBi.KalaCode,
aFI_FDocBi.Amount1 ?? 0,
aFI_FDocBi.Amount2 ?? 0,
aFI_FDocBi.Amount3 ?? 0,
aFI_FDocBi.UnitPrice ?? 0,
aFI_FDocBi.TotalPrice ?? 0,
aFI_FDocBi.Discount ?? 0,
aFI_FDocBi.MainUnit,
aFI_FDocBi.Comm,
aFI_FDocBi.Up_Flag);
int value = UnitDatabase.db.Database.SqlQuery <int>(sql).Single();
if (value == 0)
{
await UnitDatabase.db.SaveChangesAsync();
}
}
catch (Exception)
{
throw;
}
}
string sql1 = string.Format(@"SELECT SerialNumber,BandNo,KalaCode,KalaName,MainUnit,MainUnitName,Amount1,Amount2,Amount3,UnitPrice,TotalPrice,Discount,Comm,Up_Flag,DeghatR1,DeghatR2,DeghatR3,DeghatM1,DeghatM2,DeghatM3,DeghatR
FROM Web_FDocB WHERE SerialNumber = {0}", aFI_FDocBi.SerialNumber);
var listFactor = UnitDatabase.db.Database.SqlQuery <Web_FDocB>(sql1);
return(Ok(listFactor));
}
public async Task <IHttpActionResult> PostWeb_ErjDocB_Last(string userName, string password, string ace, string sal, string group, ErjDocB_Last ErjDocB_Last)
{
if (UnitDatabase.CreateConection(userName, password, ace, sal, group))
{
string sql = string.Format(CultureInfo.InvariantCulture,
@"select * FROM Web_ErjDocB_Last({0}, {1},'{2}','{3}','{4}') AS ErjDocK where 1 = 1 "
, ErjDocB_Last.erjaMode
, ErjDocB_Last.docBMode
, ErjDocB_Last.fromUserCode
, ErjDocB_Last.toUserCode
, ErjDocB_Last.srchSt);
if (ErjDocB_Last.azDocDate != "")
{
sql += string.Format(" and DocDate >= '{0}' ", ErjDocB_Last.azDocDate);
}
if (ErjDocB_Last.taDocDate != "")
{
sql += string.Format(" and DocDate <= '{0}' ", ErjDocB_Last.taDocDate);
}
if (ErjDocB_Last.azRjDate != "")
{
sql += string.Format(" and RjDate >= '{0}' ", ErjDocB_Last.azRjDate);
}
if (ErjDocB_Last.taRjDate != "")
{
sql += string.Format(" and RjDate <= '{0}' ", ErjDocB_Last.taRjDate);
}
if (ErjDocB_Last.azMhltDate != "")
{
sql += string.Format(" and MhltDate >= '{0}' ", ErjDocB_Last.azMhltDate);
}
if (ErjDocB_Last.taMhltDate != "")
{
sql += string.Format(" and MhltDate <= '{0}' ", ErjDocB_Last.taMhltDate);
}
if (ErjDocB_Last.status != "")
{
sql += string.Format(" and Status = '{0}' ", ErjDocB_Last.status);
}
sql += UnitPublic.SpiltCodeAnd("KhdtCode", ErjDocB_Last.khdtCode);
sql += UnitPublic.SpiltCodeAnd("CustCode", ErjDocB_Last.custCode);
var listErjDocB_Last = UnitDatabase.db.Database.SqlQuery <Web_ErjDocB_Last>(sql);
return(Ok(listErjDocB_Last));
}
return(null);
}
public async Task <IHttpActionResult> GetAllWeb_IDocHMin(string userName, string password, string ace, string sal, string group, byte InOut, int select, long invSelect, string user, bool accessSanad)
{
if (UnitDatabase.CreateConection(userName, password, ace, sal, group))
{
string sql = "declare @enddate nvarchar(20) ";
//if (select == 1) // اگر انتخاب برای اخرین روز بود
// sql += string.Format(@" select @enddate = max(DocDate) from Web_IDocH where InOut = {0}", InOut);
// else if (select == 2) // اگر انتخاب برای اخرین ماه بود
// sql += string.Format(@" select @enddate = substring(max(DocDate), 1, 7) from Web_IDocH where InOut = {0}", InOut);
//if (ModeCode == "in" && (select == 1 || select == 2))
// sql += " (101,102,103,106,108,110) ";
//else if (ModeCode == "out" && (select == 1 || select == 2))
// sql += " (104,105,107,109,111)";
sql += "select ";
if (select == 0)
{
sql += " top(100) ";
}
sql += string.Format(@"SerialNumber,InOut,DocNo,SortDocNo,DocDate,ThvlCode,thvlname,Spec,KalaPriceCode,InvCode,ModeCode," +
"Status,PaymentType,Footer,Tanzim,Taeed,FinalPrice,Eghdam,ModeName,InvName " +
"from Web_IDocH where InOut = {0} ", InOut);
//if (ModeCode == "in")
// sql += " (101,102,103,106,108,110) ";
//else if (ModeCode == "out")
// sql += " (104,105,107,109,111)";
if (invSelect > 0)
{
sql += " and InvCode = '" + invSelect.ToString() + "' ";
}
if (select == 1)
{
sql += " and DocDate = @enddate ";
}
else if (select == 2)
{
sql += " and DocDate like @enddate + '%' ";
}
if (accessSanad == false)
{
sql += " and Eghdam = '" + user + "' ";
}
sql += " order by SortDocNo desc";
var listIDocH = UnitDatabase.db.Database.SqlQuery <Web_IDocHMini>(sql);
return(Ok(listIDocH));
}
return(null);
}
public async Task <IHttpActionResult> GetWeb_AMode(string userName, string password, string ace, string sal, string group)
{
if (UnitDatabase.CreateConection(userName, password, ace, sal, group))
{
string sql = "SELECT * FROM Web_AMode";
var listAMode = UnitDatabase.db.Database.SqlQuery <Web_AMode>(sql);
return(Ok(listAMode));
}
return(null);
}
public async Task <IHttpActionResult> GetWeb_FDocHCount(string ace, string sal, string group, int ModeCode)
{
if (UnitDatabase.CreateConection(ace, sal, group))
{
string sql = string.Format(@"SELECT count(SerialNumber) FROM Web_FDocH WHERE ModeCode = {0}", ModeCode);
int count = UnitDatabase.db.Database.SqlQuery <int>(sql).Single();
return(Ok(count));
}
return(null);
}
public async Task <IHttpActionResult> GetWeb_FldNames(string userName, string password, string ace, string sal, string group)
{
if (UnitDatabase.CreateConection(userName, password, ace, sal, group))
{
string sql = "EXEC WEB_FldNames";
var list = UnitDatabase.db.Database.SqlQuery <Web_FldNames>(sql);
return(Ok(list));
}
return(null);
}
public async Task <IHttpActionResult> GetWeb_IDocHCount(string userName, string password, string ace, string sal, string group, byte InOut)
{
if (UnitDatabase.CreateConection(userName, password, ace, sal, group))
{
string sql = string.Format(@"SELECT count(SerialNumber) FROM Web_IDocH WHERE InOut = {0} ", InOut);
int count = UnitDatabase.db.Database.SqlQuery <int>(sql).Single();
return(Ok(count));
}
return(null);
}
public async Task <IHttpActionResult> GetWeb_ErjUsers(string userName, string password, string ace, string sal, string group)
{
if (UnitDatabase.CreateConection(userName, password, ace, sal, group))
{
string sql = string.Format(@"Select * from Web_ErjUsers");
var listDB = UnitDatabase.db.Database.SqlQuery <Web_ErjUsers>(sql).ToList();
return(Ok(listDB));
}
return(null);
}